Yes. Search your sqlite3.c for #2966 (there is only one instance and it is in the winOpen function, somewhere around line 44847). The code that is there will be an #ifdef that forces RANDOM_ACCESS only for WINCE. Just change it so that you can compile it with RANDOM_ACCESS set and see if that makes a difference. And yes, it will work in both 32 and 64 bit on Windows ... It will at least make the Windows caching deterministic (technically LRU).
I know that Windows supposedly has some builtin rules about how it sets the default cache mode, but you should really have the whole file in the cache after each command since that is the purpose of the cache (memory not used is money wasted). Then re-run your test without freeing up physical cache memory between the runs (so the database stays in the cache). The first run though the file will take a long time (SYS I/O Time) but subsequent passes should be very fast since the data is all already in the cache ... (the USER time should stay about the same, but the SYS time will change depending on whether the file is in the cache or not). --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of x >Sent: Friday, 15 June, 2018 00:50 >To: SQLite mailing list >Subject: Re: [sqlite] .timer > >Thanks for all the replies and great suggestions. I’m just up but >will shortly investigate all. > > > >After reading this post by Clemens Ladisch > > > >http://sqlite.1065341.n5.nabble.com/This-is-driving-me-nuts- >tp102034p102105.html > > > >I was guessing the answer might be something along the lines of what >is suggested below although I was clueless about how to go about it. >To be honest I’m still not sure Keith. > > > >1 Is #2699 an sqlite ticket? I can’t find it. > >2 Do I add this code to my sqlite3.c file? If so, whereabouts? > >3 Will it still work if I compile in 64 bit mode? > > > >________________________________ >From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on >behalf of Keith Medcalf <kmedc...@dessus.com> >Sent: Thursday, June 14, 2018 10:09:50 PM >To: SQLite mailing list >Subject: Re: [sqlite] .timer > > >See the following web page for how the default "Microsoft Magical >Mystery Cache Mode" works on Windows. The term "Intelligent Read- >Ahead" applies only if you are 12 years old (typical Microsoft >behaviour). > >http://flylib.com/books/en/4.491.1.101/1/ > >Note that the default mode is completely fracking useless for most >intents and purposes, and for databases the SEQUENTIAL mode is bloody >awful as well. > >However, the SEQUENTIAL mode seems to match what is being seen (the >look ahead is only working in one direction and pages are being >unmapped from the system cache at the wrong (read most stoopid) time >possible). > >So, the changes I made are to function winOpen as follows (after the >ticket #2699 comment). Effectively I always set the flags for RANDOM >mode even though I am not Winders Crappy Edition ... > > if( isDelete ){ >#if SQLITE_OS_WINCE > dwFlagsAndAttributes = FILE_ATTRIBUTE_HIDDEN; > isTemp = 1; >#else > dwFlagsAndAttributes = FILE_ATTRIBUTE_TEMPORARY > | FILE_ATTRIBUTE_HIDDEN > | FILE_FLAG_DELETE_ON_CLOSE; >#endif > }else{ > dwFlagsAndAttributes = FILE_ATTRIBUTE_NORMAL; > } > /* Reports from the internet are that performance is always > ** better if FILE_FLAG_RANDOM_ACCESS is used. Ticket #2699. */ >#if SQLITE_OS_WINCE || SQLITE_WIN32_FILE_RANDOM > dwFlagsAndAttributes |= FILE_FLAG_RANDOM_ACCESS; >#elif SQLITE_WIN32_FILE_SEQUENTIAL > dwFlagsAndAttributes |= FILE_FLAG_SEQUENTIAL_SCAN; >#elif SQLITE_WIN32_FILE_WRITETHROUGH > dwFlagsAndAttributes |= FILE_FLAG_WRITE_THROUGH; >#elif SQLITE_WIN32_FILE_NOBUFFER > dwFlagsAndAttributes |= FILE_FLAG_NO_BUFFERING; >#endif > >This adds the SQLITE_WIN32_FILE_ ... defines and applies them in >order to all files opened by the Windows VFS. I define >SQLITE_WIN32_FILE_RANDOM to make sure that the cache mode is always >set for RANDOM access and that read-ahead and idiot-mode (ie, >Microsoft-style) cache pruning are disabled, thus making the >FileSystem cache act in a deterministic LRU page ejecting fashion. > >--- >The fact that there's a Highway to Hell but only a Stairway to Heaven >says a lot about anticipated traffic volume. > > >>-----Original Message----- >>From: sqlite-users [mailto:sqlite-users- >>boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf >>Sent: Thursday, 14 June, 2018 14:16 >>To: SQLite mailing list >>Subject: Re: [sqlite] .timer >> >> >>Cannot reproduce. >> >>I am using the current trunk that I compile myself with MinGW 8.1.0 >>on Windows 10 1803 Pro for Workstations. The laptop has a 4 Ghz >Quad >>Core Xeon and the disk is a Samsung NVMe drive. About the only >>relevant change is that I have forced the Windows caching mode from >>"magical mystery Microsoft mode" to always use "Random access mode". >>It is also using the Win32 heap allocator. I also have 32 GB of RAM >>and no third or fourth level page indirection or Virtual Arena >>diddling (that is, swapping is turned off). I also have SQLite set >>to a 4K pagesize and have set 262144 pages of cache in SQLite by >>default. >> >>I forgot how slow CTE's are until I did this ... almost 3 times >>slower than using generate_series >> >>In RAM only -- Windows caching modes and disk I/O are irrelevant: >> >>>sqlite >>SQLite version 3.25.0 2018-06-13 17:19:20 >>Enter ".help" for usage hints. >>Connected to a transient in-memory database. >>Use ".open FILENAME" to reopen on a persistent database. >>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 34.619 user 34.625000 sys 0.000000 >>sqlite> drop table test; >>Run Time: real 1.578 user 1.578125 sys 0.000000 >>sqlite> create table test (x integer, y text); >>Run Time: real 0.000 user 0.000000 sys 0.000000 >>sqlite> insert into test select value, >>'012345678901234567890123456789' from generate_series where start=1 >>and stop=100000000; >>Run Time: real 12.226 user 12.234375 sys 0.000000 >>sqlite> create temp table tasc1 as select rowid from test order by >>rowid asc; >>Run Time: real 11.408 user 11.140625 sys 0.265625 >>sqlite> create temp table tdesc1 as select rowid from test order by >>rowid desc; >>Run Time: real 10.251 user 9.875000 sys 0.375000 >>sqlite> create temp table tdesc2 as select rowid from test order by >>rowid desc; >>Run Time: real 10.188 user 9.828125 sys 0.359375 >>sqlite> create temp table tasc2 as select rowid from test order by >>rowid asc; >>Run Time: real 11.563 user 11.218750 sys 0.328125 >>sqlite> ^Z >> >>Timings are all pretty constant ... now with the db on disk ... >> >>>sqlite junk.db >>SQLite version 3.25.0 2018-06-13 17:19:20 >>Enter ".help" for usage hints. >>sqlite> create table test (x integer, y text); >>Run Time: real 0.015 user 0.000000 sys 0.000000 >>sqlite> insert into test select value, >>'012345678901234567890123456789' from generate_series where start=1 >>and stop=100000000; >>Run Time: real 21.206 user 13.234375 sys 6.562500 >>sqlite> create temp table tasc1 as select rowid from test order by >>rowid asc; >>Run Time: real 13.658 user 10.000000 sys 3.656250 >>sqlite> create temp table tdesc1 as select rowid from test order by >>rowid desc; >>Run Time: real 12.658 user 9.500000 sys 3.156250 >>sqlite> create temp table tasc2 as select rowid from test order by >>rowid asc; >>Run Time: real 13.017 user 10.031250 sys 2.984375 >>sqlite> create temp table tdesc2 as select rowid from test order by >>rowid desc; >>Run Time: real 13.001 user 9.750000 sys 3.250000 >>sqlite> .exit >> >> >>2018-06-14 13:47:30 PY2 [D:\] >>>dir junk.db >>2018-06-14 13:44 4,450,889,728 junk.db >> 1 File(s) 4,450,889,728 bytes >> 0 Dir(s) 330,480,447,488 bytes free >> >>So we have some difference in SYS time for I/O, but that is about >it. >>Still constant times. >> >>--- >>The fact that there's a Highway to Hell but only a Stairway to >Heaven >>says a lot about anticipated traffic volume. >> >> >>>-----Original Message----- >>>From: sqlite-users [mailto:sqlite-users- >>>boun...@mailinglists.sqlite.org] On Behalf Of x >>>Sent: Thursday, 14 June, 2018 12:31 >>>To: SQLite mailing list >>>Subject: Re: [sqlite] .timer >>> >>>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 >> >> >> >>_______________________________________________ >>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 >_______________________________________________ >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
