Hello, On Wed, Apr 12, 2006 at 09:02:34PM -0400, [EMAIL PROTECTED] wrote: > 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.
I think you're perfectly correct. Now I understand. > 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 could probably be done. I'll see if I can do it. Thanks to ALL for your answers. bye Jerome Alet
