> I think some other factor is at play here. > SQLite 2.x's memory databases are still twice as fast at batch inserts > than either 3.x's disk-based databases or 2.x's disk-based databases > when the DB size is less than physical machine memory.
I did some experimentation with an SQLite 2.8.17 :memory: database versus a 3.3.6 :memory: database (with PRAGMA temp_store=memory in both cases) and found something worth noting - SQLite 2.8.17 uses over four times as much in-process RAM to perform a batch insert of one million rows as compared to 3.3.6: version in-process RAM ------- -------------- 2.8.17 268M 3.3.6 64M So although 3.3.6 takes almost twice the time to populate a memory DB with a single transaction, it uses less than a quarter of the RAM that 2.8.17 required for the same operation. It appears to be a classic memory-for-speed tradeoff. I was not able to perform a single batch insert of 4 million rows into a 2.8.17 :memory: database on my machine due to lack of RAM. No such problem with 3.3.6. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com