Yes, that is correct. Then compile with the preprocessor symbol SQLITE_WIN32_FILE_RANDOM defined.
generate_series is an extension module located in ext/misc/series.c https://www.sqlite.org/src/dir?ci=567e09ef2a8cd84a&name=ext/misc By default I load almost every one of those extensions in every sqlite3.dll and sqlite3.exe I compile (along with the INIT hook to get them loaded on each connection automatically -- basically make them all builtin extensions), along with a bunch of others I wrote myself that do things like add the entire standard math library, running statistics, a bunch of Windows functions (get the computer name, username, sids, translate sids between blobs and strings, check the token for sids, handle uuid functions etc), ip address functions, unicode case/accent folding, etc. It makes the DLL and static shell about 2 MB, but I have no shortage of CPU or memory anywhere ... (though I have to keep remembering whenever I move stuff to other servers and VMs that use actual slow hard drives that I have to keep the I/O rate within the piddly bounds of spinning rust -- NVMe and SSD drives with GB/s rather than KB/s IO rates can kill you). I presume you are running current Win10Pro. There was a "bug" introduced in Defender back in late November that has now supposedly been fixed (don't know if it was in Defender or in Windows itself, but turning off the real-time defender fixed the issue) that adversely affected a bunch of crap (the main thing I noticed is that is doubled or more my build times -- by introducing the generic "doing nothing" (ie, some build processes loaded up all the SMT threads on the processor, and ran at 100% CPU usage for each thread on each core for a couple of minutes -- that time doubled and the CPU usage dropped to about 50% average on each thread) for which Microsoft is famous). That has since been fixed (in March or something) and I haven't noticed anything else acting "weird" -- though like I said I have no idea if it was Defender that was the problem or if that just brought out some other issue in the windows kernel itself. --- 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 02:06 >To: SQLite mailing list >Subject: Re: [sqlite] .timer > >Keith, I can find no instance of 2966 in my sqlite.c code. I have >found the code you mentioned > > > > 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; > > } > > > >Immediately after that section of code I’ve replaced > > > >#if SQLITE_OS_WINCE > > dwFlagsAndAttributes |= FILE_FLAG_RANDOM_ACCESS; > >#endif > > > >With > > > > /* 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 > > > >Is that correct? > > > >BTW ‘select * from generate_series(1,10)’ gives me an error ‘no such >table : generate_series’ in sqlite3.exe. I thought it was compiled >into the shell by default? > > > > > > > >________________________________ >From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on >behalf of Keith Medcalf <kmedc...@dessus.com> >Sent: Friday, June 15, 2018 8:10:19 AM >To: SQLite mailing list >Subject: Re: [sqlite] .timer > > >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 >_______________________________________________ >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
