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
