Thanks Simon/Gunter. I thought those sections cleared things up until I tried a few tests. I opened the DB, set temp_store to STORE, cache_size to CACHE and then calculated the average secs taken (over 2 runs) to run the following
[Tbl has integer primary key ID, contains 10,570 records & is cross joined to itself to create a (pointless and not distinct) large insert of 111,724,900 recs] create temp table Key (ID int); INSERT = secs to run insert into Key select ID from Tbl cross join Tbl using (ID); DELETE = secs to run delete from Key; The results for different values of temp_store and cache_size were as follows STORE, CACHE, INSERT, DELETE 0, 0, 23.00, 1.87 2, 0, 21.10, 1.27 2, -8, 20.36, 1.30 2, -80, 20.61, 1.30 2, -200, 20.70, 1.30 2, -300, 20.72, 1.30 2, -400, 20.70, 1.30 2, -500, 13.58, 1.29 2, -800, 13.48, 1.29 2, -8000, 13.52, 1.28 2, -8000000, 13.64, 1.29 At cache_size = -500 the timings come more into line with the memory results from my old tests. Some things I need cleared up 1) I read in those links that each temp table is given (by default) its own page cache of 500 pages. Is this a separately created page cache or is it 500 pages from THEE page chache? If it's the latter that will explain the slowdown for cache_size < -500. Or does the cache_size pragma dictate the size of the separately created page cache? 2) My DB page size is 1024. If the temp table is allocated a separate page cache of 500 * 1024 bytes this means that sqlite managed to store 111,724,900 records in 500 KB? That's an average of 0.0046 bytes per record? 3) Similar to the original question, if you set temp_store = 2 (memory) and there isn't enough memory for the table what happens? -- Sent from: http://sqlite.1065341.n5.nabble.com/ _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
