Hi DRH, A mailing list post by you outlines a similar problem that I am seeing:
http://www.mail-archive.com/[email protected]/msg15913.html Have you given any thought to this index page locality matter? Perhaps something like: PRAGMA reserve_pages_for_indexes = 50000 Whereby a chunk of file space can be exclusively reserved for index use to prevent index fragmentation? I realize that this goes against the zero-admin principle of SQLite, but such a feature might lead to dramatic improvements in bulk insert and cold-cache queries. thanks. ----- Original Message ---- From: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> To: [email protected] Sent: Friday, October 27, 2006 9:31:51 AM Subject: Re: [sqlite] serious performance problems with indexes Peter De Rijk <[EMAIL PROTECTED]> wrote: > I have run into a serious performance problem with tables with many rows. > The problem only occurs on tables with an index > The time needed for an insert into a table with an index is dependend on the > number of rows. I have not formally checked, but from my tests it looks like > an exponential dependence. This of course means that while sqlite is very > fast on smaller datasets, it is slow on larger data sets and becomes unusable > on large datasets (million of rows). The insert behaviour is normal on non > indexed tables, but obviously queries are a problem then. > Is this index behaviour normal/expected for sqlite, or can this be solved? > When a table is indexed, INSERT performance is logorithmic in the number of rows in the table and linear in the number of indices. This is because entries have to be inserted into the index in sorted order (otherwise it wouldn't be an index). And each insert thus requires a binary search. If your index becomes very large so that it no longer fits in your disk cache, then the binary search can involve a lot of disk I/O which can make things really slow. The usual work-around here is to keep a much smaller staging table into which you do your inserts and then periodically merge the staging table into the main table. This makes your queries more complex (and slower) since you are now having to search multiple tables. But it does speed up inserts. -- D. Richard Hipp <[EMAIL PROTECTED]> ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] ----------------------------------------------------------------------------- ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
