Point certainly taken. I was trying to avoid getting into too much detail without first confirming if this was some well known issue.
I'll put together a standalone test app which will demonstrate the poor loading/insert performance. I'll get to the read performance after that. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, June 17, 2004 3:15 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [sqlite] RE: SQLite performance with mid-size databases Richard, I notice you don't seem to be getting useful replies on this list, and I think perhaps that it is in part a lack of useful input on your part that is holding up the works. Here is my suggestion: 1) Take a copy of your working database 2) Try the exact sql query you believe to be scaling linearly 3) Prove to your satisfaction that this really is happening 4) Share with us the shortest example you can come up with that shows the problem If you can't manage to isolate it in this way, I suggest that you put some debug code in to capture a log of all SQL statements you're issuing to sqlite while you're experiecing the problem. Try narrowing it down to a subset of those queries. On the other hand, perhaps is simply the number of times you're making each kind of query. Keep an eye on exactly what the most common operations are and find out if you're calling them in a way that scales linearly with your database size. On a "stab in the dark" note, the COUNT operator is something often used in scrolling tabular displays. It requires a linear scan of all table data, so if you're doing COUNTs don't. Try keeping your own records of how many rows are in your tables using triggers (I belive you will find sample code that does this in the list archives). LIMIT and OFFSET may also be used in such displays. Beware that OFFSET may be scanning through the results of your query and explicitly ignoring them in the same way as count would. Naturally, beware of any queries that don't fit naturally to your indicies and use .explain to ensure that the indices are being used. Good luck. Benjamin. "Richard Kuo" <[EMAIL PROTECTED]> 17/06/2004 03:26 PM To: "'Ionut Filip'" <[EMAIL PROTECTED]> cc: <[EMAIL PROTECTED]> Subject: [sqlite] RE: SQLite performance with mid-size databases Ionut, Thank you for your input. Do you mean precompilation and then subsequent calls to the sqlite_bind API? Am definitely using transactions, and the database is entirely read-only with only the client in question accessing the db. The poor and linearly scaling performance degradation with size is very odd. I can make no sense of why this would happen unless SQLite has terrible locality of reference issues. I will post a schema later today that may hopefully shed some light on the issue. Richard -----Original Message----- From: Ionut Filip [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 15, 2004 11:54 PM To: [EMAIL PROTECTED] Subject: SQLite performance with mid-size databases Hi Richard, Here is a tip to speed up the insert operations: use prepared statements instead of plain INSERTs. If you have to insert more than one record with the same format the performance increase is significant. Transactions also speeds up db operations, there are more detailed topics about this on the list. > The speed results on the website [...] Here I think you need to make sure the concurrency access to the DB is not a bottleneck. Ionut Filip PS: You can also try to post a sample (partial) schema of your database. -----Original Message----- From: Richard Kuo [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 16, 2004 7:04 AM To: [EMAIL PROTECTED] Subject: [sqlite] SQLite performance with mid-size databases 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] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
