Re: [sqlite] nfs 'sillynames'
On Sat, 4 Sep 2004, Ara.T.Howard wrote: On Fri, 3 Sep 2004, John LeSueur wrote: I think this file is created everytime you start a transaction. Maybe even if you only perform read only commands. The journal is what is used to keep track of the changes to the database. Anyone else have any thoughts? yes - it is. the thing is, unless a remote client unlinks it while another client has it open the sillynames would not appear. i can post a copy of one of the sillynamed files - but they are definitely the contents of db-journal. the question is why this is happening. running strace and looking at the fcntl lock calls lead me to think it __should__ be impossible for any client to unlink this file before someone else closed it - but that IS what i'm seeing happen... here's a snaphshot of my system in action: yacht:~/shared > new q total 4432 -rw-rw-rw-1 cfadmin cfadmin 1023 Aug 29 08:55 db.schema drwxrwxrwx 11 cfadmin 34096 Sep 4 15:43 .. -rw-rw-rw-1 cfadmin cfadmin 4441088 Sep 4 15:44 db -rw---1 cfadmin cfadmin 21692 Sep 4 15:44 .nfs0091c0d41089 -rw-rw-rw-1 cfadmin cfadmin 0 Sep 4 15:44 lock -rw---1 cfadmin cfadmin 15500 Sep 4 15:44 .nfs0091c0d711c2 -rw-r--r--1 cfadmin cfadmin 0 Sep 4 15:44 trout.ngdc.noaa.gov.22069.lock.w -rw-r--r--1 cfadmin cfadmin 0 Sep 4 15:44 mahi.ngdc.noaa.gov.31335.waiting.w -rw---1 cfadmin cfadmin 15500 Sep 4 15:44 db-journal -rw---1 cfadmin cfadmin 8276 Sep 4 15:44 .nfs0091c0d51270 drwxrwxrwx2 cfadmin cfadmin 4096 Sep 4 15:44 . you can see that the files .nfs0091c0d711c2 and db-journal are (amost positively) the same. so, somehow, the unlinking of the db-journal causes this behaviour. -a -- === | EMAIL :: Ara [dot] T [dot] Howard [at] noaa [dot] gov | PHONE :: 303.497.6469 | A flower falls, even though we love it; | and a weed grows, even though we do not love it. | --Dogen ===
Re: [sqlite] LIMIT does not speed up query execution
Adam, your query using LIMIT and a less-restricting WHERE is slower because you have an ORDER BY clause. ORDER BY is always one of the slowest things you can do in a query because every record returned by WHERE (or HAVING if you're using GROUP BY) has to be compared to every other record for sorting. Only after ORDER BY is run, then LIMIT is applied, because LIMIT doesn't know which records to return until after they are sorted. So to make this faster you either have to make WHERE return fewer rows (better), or let it return more but remove the ORDER BY. -- Darren Duncan At 10:49 PM +0200 9/4/04, hilaner wrote: Hi all! Since my database growed to more than 20 000 records, I have noticed that select limited to a few numer of records by LIMIT takes much more time than select limited to similar number of records by another WHERE condition. I use sqlite_get_table function. In my case I have the following queries: (1) SELECT *, col_1 - col_2 WHERE col_3 = 0 AND col_4 > 0 ORDER BY col_0 ASC col_5 DESC LIMIT 40 OFFSET 0; (2) SELECT *, col_1 - col_2 WHERE col_3 = 0 AND col_4 > 0 AND col_6 = 5 ORDER BY col_0 ASC col_5 DESC; And the (2) query executes much faster than (1), even it has another contition in WHERE section. (1) takes nearly the same time like (1) without LIMIT condition (full table select). Is it normal? What can I do to speed up (1) query? (SQLite 2.8.15, one table of 16 columns and more than 2 records, Windows 2000, VC++ 6.0) Regards Adam
[sqlite] HOWTO: Overcome the single threadness of the in-memory database...
There is a systemic problem with sqlite and multi-thread access to the in memory database. 1. Create a RAM Drive. [OS specific, see your development software.] 2. Point database to the RAM Drive. You should not suffer too much speed degredation from the RAM Drive. 3. This will give you all the locking capabilities of the file system and over come single threadness. Tezozomoc.
Re: [sqlite] nfs 'sillynames'
On Fri, 3 Sep 2004, John LeSueur wrote: I think this file is created everytime you start a transaction. Maybe even if you only perform read only commands. The journal is what is used to keep track of the changes to the database. Anyone else have any thoughts? yes - it is. the thing is, unless a remote client unlinks it while another client has it open the sillynames would not appear. i can post a copy of one of the sillynamed files - but they are definitely the contents of db-journal. the question is why this is happening. running strace and looking at the fcntl lock calls lead me to think it __should__ be impossible for any client to unlink this file before someone else closed it - but that IS what i'm seeing happen... -a -- === | EMAIL :: Ara [dot] T [dot] Howard [at] noaa [dot] gov | PHONE :: 303.497.6469 | A flower falls, even though we love it; | and a weed grows, even though we do not love it. | --Dogen ===
[sqlite] LIMIT does not speed up query execution
Hi all! Since my database growed to more than 20 000 records, I have noticed that select limited to a few numer of records by LIMIT takes much more time than select limited to similar number of records by another WHERE condition. I use sqlite_get_table function. In my case I have the following queries: (1) SELECT *, col_1 - col_2 WHERE col_3 = 0 AND col_4 > 0 ORDER BY col_0 ASC col_5 DESC LIMIT 40 OFFSET 0; (2) SELECT *, col_1 - col_2 WHERE col_3 = 0 AND col_4 > 0 AND col_6 = 5 ORDER BY col_0 ASC col_5 DESC; And the (2) query executes much faster than (1), even it has another contition in WHERE section. (1) takes nearly the same time like (1) without LIMIT condition (full table select). Is it normal? What can I do to speed up (1) query? (SQLite 2.8.15, one table of 16 columns and more than 2 records, Windows 2000, VC++ 6.0) Regards Adam
Re: [sqlite] Single INSERT is very slow
For a more useful test, please make a second table with 2 fields,
like Test but with a second VARCHAR(10) column. Then compare the
speed of inserting into that table vs inserting into your first one.
After all, if the problem is specific to single-column tables, then
we should get rid of all the other differences in what is being
compared. Please try that and resubmit the results here. Keep in
mind to try both without and with indexes on each field. -- Darren
Duncan
At 8:22 AM + 9/4/04, George Ionescu wrote:
Hello sqlite users,
Hello Dr. Hipp,
while using sqlite v3.0.6, I've discovered that doing a single
INSERT or UPDATE on a table which has only one field is very slow:
CREATE TABLE Test (Field1 VARCHAR(10));
INSERT INTO Test VALUES ('123');
the INSERT statement above takes approx. 150 milliseconds to
complete, which is very slow compared to Access/ADO, which does the
same in 3 milliseconds.
The timings were done on a 2.4 Celeron, 512 RAM, Windows XP.
Is there something I'm missing here or is this the expected
behavior? Timings on other operations (SELECT) yielded expected
results (e.g. faster than Access/ADO).
Best regards,
George Ionescu
[sqlite] Single INSERT is very slow
Hello sqlite users,
Hello Dr. Hipp,
while using sqlite v3.0.6, I've discovered that doing a single INSERT or
UPDATE on a table which has only one field is very slow:
CREATE TABLE Test (Field1 VARCHAR(10));
INSERT INTO Test VALUES ('123');
the INSERT statement above takes approx. 150 milliseconds to complete, which
is very slow compared to Access/ADO, which does the same in 3 milliseconds.
The timings were done on a 2.4 Celeron, 512 RAM, Windows XP.
Is there something I'm missing here or is this the expected behavior?
Timings on other operations (SELECT) yielded expected results (e.g. faster
than Access/ADO).
Best regards,
George Ionescu
_
Protect your PC - get McAfee.com VirusScan Online
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
