==> SUMMARY <== ==> There is indeed no difference between 3.3.7 and 3.3.8 ==> However, sqlite hits the disk a lot in a temp file??!! ==> PRAGMA temp_store = MEMORY; helps ==> Why is sqlite hitting the disk with a 70MB database?
Further tests shows that there is no difference between 3.3.7 and 3.3.8. The problem was, that I was using sqlite.exe interactively. The in the 3.3.8 shell I have been running some tests that created and deleted some temp tables before I did performance tests. It turns out that the query hits the disk when the table exceeds a certain size. There's a certain size of my tables when performance goes down dramatically. It takes 14 sec for 100,000 rows and 300 for 200,000. The CPU goes down to almost 0 and the disk gets very active. My database: pragma cache_size = 20000; pragma page_size = 4096; Database file (after vacuum) 70MB with about 450,000 records > time ./sqlite3.3.8.exe db.sqlite "SELECT * FROM files where id < 100000 ORDER BY size, name;"|wc 99999 103445 11352384 real 0m14.281s user 0m7.260s sys 0m3.775s Peak memory 35 MB >time ./sqlite3.3.8.exe db.sqlite "SELECT * FROM files where id < 200000 ORDER BY size, name;"|wc 199999 204598 24676875 real 4m49.947s user 0m18.386s sys 0m13.318s Peak memory 35 MB I captured the performance using sysinternals procexp: http://www.microsoft.com/technet/sysinternals/SystemInformation/ProcessExplorer.mspx See the attached screen shot. It's interesting that half of the memory is allocated in the last seconds... When I prepend the query with PRAGMA temp_store = MEMORY; The queries are fast, but the process needs a lot of memory (about 5 times the size of the .dump size of the result table) > time ./sqlite3.3.8.exe db.sqlite "PRAGMA temp_store = MEMORY;SELECT * FROM where id < 100000 ORDER BY size, name;"|wc 99999 103445 11352384 real 0m8.262s user 0m6.659s sys 0m0.210s Peak memory 58 MB > time ./sqlite3.3.8.exe db.sqlite "PRAGMA temp_store = MEMORY;SELECT * FROM where id < 200000 ORDER BY size, name;"|wc 199999 204598 24676875 real 0m13.329s user 0m12.187s sys 0m0.310s Peak memory 75 MB What surprises me, is that the temp file is not kept in cache. I have 2GB of memory and much bigger files can be kept in cache. Why is sqlite "hitting the disk"? What is going on here? The maximum file cache needed would be 70 MB for the database + 75 MB for the temp table. 150MB is nothing on a 2GB system. I thought maybe PRAGMA synchronous = OFF; would help. But it does not. Michael [EMAIL PROTECTED] wrote: > Michael Sizaki <[EMAIL PROTECTED]> wrote: >> What has changed in 3.3.8 to make it so slow? >> > > There were no changes to the query optimizer between 3.3.7 > and 3.3.8. None. Nada. Zilch. ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
