Just seen this Each temporary table and index is given its own page cache which can store a maximum number of database pages determined by the SQLITE_DEFAULT_TEMP_CACHE_SIZE compile-time parameter. (The default value is 500 pages.) The maximum number of database pages in the page cache is the same for every temporary table and index. The value cannot be changed at run-time or on a per-table or per-index basis. Each temporary file gets its own private page cache with its own SQLITE_DEFAULT_TEMP_CACHE_SIZE page limit.
That suggests the cache for the temp table isn’t affected by setting cache_size at runtime and so it would seem all the above tests were run with the temp table cache having a default size of 500 KB. I can only guess the slower results were down to some work sqlite had to do within the ordinary cache and that was slowed down when cache_size was set to less than -500. I don’t know though. I ran the tests again using all the sqlite default settings so that temp_store would be 1 (i.e. FILE). The results were slower but not by much. For the temp table with 112 million records it slowed down by ~12%. For a temp table with 2.4 million records it was slower by more like 33% but negligible in real time. I also tried running the test involving the temp table with 112 million records in win32 with temp_store = 2 (MEMORY) to see if it would crash due to the restricted memory but it didn’t. It was around 12% faster than when temp_store = 1. I even tried setting the SQLITE_DEFAULT_TEMP_CACH_SIZE to 5 pages and then 0 pages but it made no difference to the results. For one last win64 test I tried setting temp_store = 2 with SQLITE_DEFAULT_TEMP_CACHE = 1,000,000 ( giving a temp table cache size of 1 GB) but it didn’t speed it up any. Given the TEMP_CACHE_SIZE was inadequate for the temp table in all the tests I wonder why the SQLITE_TEMP_STORE default isn’t 2 (MEMORY) as it does seem to give speed gains of between 12 & 33% yet didn’t crash due to the inadequate size of the temp cache. As Simon said though, the OS mode of operation muddies the water. Q. So, after all that what’s the conclusion Tom? A. I don’t know Tom. ☹ _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
