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] -----------------------------------------------------------------------------
