Jerome Alet <[EMAIL PROTECTED]> wrote: > > SQLite is way faster than the two other databases at least when the > number of records is reasonable, but when the database reaches > around 300 Mb which is something like 4000000 records in Table3, > it slows down dramatically (in fact the slowdown is probably from > the beginning, but becomes noticeable around 250 / 300 Mb), > and disk I/O becomes the bottleneck since the top command > gives me "wa: 90%" (if I understand correctly). >
The problem (I suspect) is that you have an index on Table3. As you insert to records into Table3, those record go at the end, which is very efficient. But the index entries have to be inserted in index order, which means they get scattered out all through the index. This works fine as long as the index is small enough to fit in cache (either SQLite's private cache, or failing that your operating systems disk cache.) Once the index gets too big to fit in cache, you tend to start thrashing. The problem is a lack of locality of reference in the index. Each index insertion requires O(logN) disk reads and writes. This is no big deal as long as a "disk read and write" is serviced from cache but gets to be a very big deal when it actually involves real disk I/O. You start to get real disk I/O when the index loses locality of reference and exceeds the size of your cache. I do not know how to fix this and still make the indices useful. Experience shows that you can get a constant speedup (50%?) if you do your table inserts first, then do a separate CREATE INDEX command to build the index only after the table has been fully populated. This will not work, of course, if your index results from a PRIMARY KEY or UNIQUE constraint. You can also get a speedup if you do your INSERTs in something that approximates index order, since that will greatly improve the locality of reference and make the cache much more effective. This problem has been coming up with more and more frequency. I need to either figure out a solution or at least write up some official documentation on it..... -- D. Richard Hipp <[EMAIL PROTECTED]>
