Richard,
this is also just a stab in the dark, and I subscribe to the digest version of the mailinglist, so I may not have the latest.
Perhaps you are doing something like
SELECT A.x, A.y FROM A WHERE A.rowid = xxx OR A.rowid = yyy OR A.rowid = zzz OR A.rowid = ...
etc.etc. with may OR-conditions.
I have noticed that SQLite (and PostgreSQL, for that matter) slows down quite a bit when the number of WHERE-conditions reaches beyond a somewhat low number, say around 10.
HTH
Ulrik Petersen
Subject: SQLite performance with mid-size databases From: "Richard Kuo" <[EMAIL PROTECTED]> Date: Tue, 15 Jun 2004 23:04:04 -0500 To: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Hi. We are using SQLite to store and retrieve data rows where each row is roughly 2K total in size and in a table of 15 columns. The total size of the database ranges from 100-300 MB.
The problem we are seeing is that query and insert performance is unusually bad and scales up linearly with database size. Compared to MS Access, the query times are several times slower. Frankly I was a bit shocked at this considering that most people seem to think the performance is good. However, I don't see anything that we are doing wrong...we query the rows we want only by rowid. I'm very puzzled that this hasn't come up a lot in my searches of the mailing list, but perhaps the slower query times aren't a concern for many of the applications using SQLite.
Empirically speaking, we display our data in a scrolling 2 dimensional grid format. With MS access, this grid responds instantaneously when moving through the grid. With SQLite, there is very noticable stalling and lag and the disk i/o is higher than MS Access by roughly a factor of 10.
I suppose I am looking to see if anyone is seeing the same results that I am seeing, and wondering if this is known and expected to be the case. The speed results on the website seem way off to me or must be so skewed towards a small dataset that they do not apply in a real world scenario. I would also like to state that I am very impressed with the simplicity of SQLite, which is rare to find these days. It was very easy to get up and running. I'm just having trouble getting past the performance issues. Any explanation would be helpful.
Richard Kuo
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
