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
