Thanks for the replies. It is Windows Defender I’m using on a ms surface pro 4 with 16 GB ram and 512 GB SSD. OS is Windows 10 Pro. I’ve come up with the following demo using the sqlite shell. In it I use a couple of small apps called AvlRAM and FlushMem. Apart from minor background tasks sqlite3.exe is the only app running.
I downloaded FlushMem from here https://chadaustin.me/flushmem/ and changed it slightly so that it reported the available RAM before and after the flush. I was primarily interested in how sqlite was performing after restarting the computer before any info was lying about in caches as I was encountering some strange timings with such. FlushMem saved me from continually rebooting and yielded results similar to the ones I was experiencing with rebooting. The code for AvlRAM is as follows double FreeGBs() { MEMORYSTATUSEX status; status.dwLength = sizeof(status); GlobalMemoryStatusEx(&status); return status.ullAvailPhys / (1024.0 * 1024 * 1024); } int _tmain(int argc, _TCHAR* argv[]) { std::cout << FreeGBs() << " GBs" << std::endl; return 0; } Here’s the demo interspersed with comments SQLite version 3.24.0 2018-06-04 19:24:41 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .timer on sqlite> .open MyTemp.db -- open empty DB sqlite> .shell FlushMem Available RAM - pre flush = 12.913 GBs - post flush = 14.1749 GBs sqlite> create table test as -- creates test table with 100 million rows ...> with recursive cte(x,y) as ...> (values(1,'012345678901234567890123456789') ...> union all select x+1,y from cte where x<100000000) ...> select * from cte; Run Time: real 77.239 user 68.750000 sys 7.468750 sqlite> .shell AvlRAM 14.1059 GBs sqlite> create temp table tasc as select RowID from test order by RowID; Run Time: real 32.473 user 25.203125 sys 7.203125 sqlite> .shell AvlRAM 14.1084 GBs // little change to RAM, respectable time sqlite> create temp table tdesc as select RowID from test order by RowID desc; Run Time: real 32.056 user 24.515625 sys 7.531250 sqlite> .shell FlushMem Available RAM - pre flush = 9.96339 GBs - post flush = 14.7108 GBs // why does the RAM drop 4+ GB due to executing the above?, respectable time sqlite> create temp table tasc2 as select RowID from test order by RowID; Run Time: real 38.285 user 26.109375 sys 9.000000 sqlite> .shell FlushMem Available RAM - pre flush = 14.5936 GBs - post flush = 14.7553 GBs // no change to RAM, time a bit more sluggish after ‘reboot’ but still respectable sqlite> create temp table tdesc2 as select RowID from test order by RowID desc; Run Time: real 117.765 user 28.265625 sys 13.828125 sqlite> .shell FlushMem Available RAM - pre flush = 10.5519 GBs - post flush = 14.6888 GBs // RAM drops 4+ GB, time is woeful. Why? // repeat above 2 ‘create temp table’ commands to demonstrate not fluke sqlite> create temp table tasc3 as select RowID from test order by RowID; Run Time: real 41.747 user 26.562500 sys 10.625000 sqlite> .shell FlushMem Available RAM - pre flush = 14.78 GBs - post flush = 14.754 GBs // no change to RAM, time a bit more sluggish after ‘reboot’ but still respectable sqlite> create temp table tdesc3 as select RowID from test order by RowID desc; Run Time: real 118.282 user 29.406250 sys 13.765625 sqlite> .shell FlushMem Available RAM - pre flush = 10.6947 GBs - post flush = 14.5856 GBs // RAM drops 4- GB, time is woeful. Why? sqlite> .exit Why is using ‘RowID desc’ so much slower than ‘RowID asc’ after a ‘reboot’? I get the impression Windows is caching the pages from the ‘desc’ but not the ‘asc’ and that’s what’s slowing it down. On this particular journey I’ve come across tables where the reverse was the case (I.e. it was the ‘asc’ that was woefully slow). Anyone cast any light on this? _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
