Re: [sqlite] .timer

[Keith Medcalf]

Normally there is a small "Memory Mapped" buffer into the FileSystem cache that 
maintains a constant size.  This area is "mapped" into the Cache Working Set.  
As you read/write different parts of files in the cache, the "mapped" are is 
"moved" so that the I/O can be carried out by the pager.  The same thing is 
done when you explicitly create a memory mapped file, except that you are in 
control of the mapped region and its size.

When the heuristics detect "backwards" sequential/strided movement though a 
cached file, the new "beginning" of the mapped area is changed, but the "end" 
of the mapped area is not (like I said, this is probably a missing 
brace-bracket in the code, or a switch that falls though because the "break" 
was left out).  When you are reading randomly or in forward (according to the 
heuristics) *both* the start and end points of the mapping are changed.  This 
is why following a sequential/strided "backwards" read by a sequential/strided 
"forward read" fixes the mapping.

This is a bug in Windows and it has (apparently) existed for quite a while.  I 
guess no one ever reported it to Microsoft (or maybe they have and it was 
either (a) ignored or (b) the ticket was closed before it reached anyone 
capable of understanding it).  Of course, reporting bugs to Microsoft is very 
difficult -- you have to navigate many layers of "flappers" before you get to 
anyone even capable of understanding what you are talking about!

Probably a CVE needs to be generated for this Denial-of-Service bug with a high 
severity in order to get any attention and fix at all.  It probably needs a 
catchy name and a website (plus media press releases) as well.

---
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: Friday, 15 June, 2018 17:12
>To: SQLite mailing list
>Subject: Re: [sqlite] .timer
>
>
>According to Mark Russinovich's fine RAMMap tool (part of the
>SysInternals Suite) Windows is "converting" the file from a
>"standard" FileSystem cached file to a "Memory Mapped" file and back
>again.  "Memory Mapped" files occupy RAM (as in allocate RAM so it
>cannot be used for other purposes) whereas the standard "Cached"
>files are just stored in otherwise unused RAM and are completely
>discardable pages that can be instantly re-used for another purpose).
>
>No idea why Windows is doing this useless crap ... although I do
>recall someone else quite a while back questioning why Windows was
>creating "Memory Mapped" files when none had been requested ... Now I
>think we have the answer.  Bet you Microsoft is missing some brace-
>brackets in their code somewhere after an if statement ... thus
>fricking with their cache management code ...
>
>---
>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: Friday, 15 June, 2018 16:22
>>To: SQLite mailing list
>>Subject: Re: [sqlite] .timer
>>
>>
>>Ok, I have been able to reproduce this but have not figured out what
>>is happening.
>>
>>And you are correct, it appears to be related to "reading backwards"
>>somehow.  It is unaffected by the WIN32 cache mode that is set (I
>>tested all modes and they all behave the same -- except that I could
>>not test the NOBUFFER mode since SQLite3 I/Os are not page and
>>cluster aligned), it happens whether using the WIN32 allocator or
>the
>>system malloc.  It happens with both 32-bit code and 64-bit code.
>>
>>The memory *IS NOT* being allocated to SQLite3 or whatever process
>is
>>running the sqlite3 database engine (its memory usage stays
>constant)
>>and it is not actually being "used by" the FileSystem cache, it is
>>not allocated to the process at all.  Even though it appears to be
>>"eating" several Gigs of RAM (actually, it almost looks like a
>>duplicate cache of the file being read, but this makes no logical
>>sense whatsoever), this ram is not being allocated to the process
>>because the 32-process does not get an Out-of-Memory error, and 32-
>>bit processes have a limited arena to play in.  It is also not
>>"dirty" write buffers since those are accounted for separately.
>>
>>The RAM is being allocated without being committed (so it is a
>>temporary usage thing).  However unlike actual FileSystem cache
>>(which uses RAM not allocated for any other purpose) this appears
>>usage appears to have "priority" over the FileSystem cache and over
>>the process working set thus forcing working set and/or filesystem
>>cache to be discarded.
>>
>>Interestingly, sometimes the allocated memory "cleans itself up",
>>however, if you follow the "desc" by an "asc" operation it appears
>to
>>"clean up" and convert that usage into 

Re: [sqlite] .timer

[Keith Medcalf]

That will do nothing.  

I explicitly set the cache mode.  I always disable all "Mystical Microsoft 
Magical Behaviour" wherever I can find it -- though there are a very few 
"autotuning" things that work properly by themselves, just not many (unless 
they originated with an actual Engineer rather than a Microsoftian).  

I despise magical behaviour since I discovered long ago that it usually does 
not work properly -- and that holds true to this very day.  

If I want something then I will set in explicitly and not depend on 
Microsoft-AutoMagic (prayer mode).  

Determinism and reduction of complexity is how one obtains reliability.  
Enabling (or not disabling) "Magic Modes" is (a) non-deterministic and (b) 
increases complexity, thus reducing reliability and reproducibility.

---
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 Simon Slavin
>Sent: Friday, 15 June, 2018 17:10
>To: SQLite mailing list
>Subject: Re: [sqlite] .timer
>
>On 15 Jun 2018, at 11:21pm, Keith Medcalf 
>wrote:
>
>> And you are correct, it appears to be related to "reading
>backwards" somehow.
>
>If you're comparing the two directions of reading the same file,
>there are ways to fake out Windows' read-ahead tricks and see if
>they're what's messing up your program.
>
>A) Plug in a Flash drive and create/open the file on that instead of
>the boot drive.
>
>B) Rename the file to something which doesn't have a database-like
>extension.  I suggest an extension of ".333'.
>
>Try those -- separately not together -- and see if you see any
>difference.
>
>Simon.
>___
>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

Re: [sqlite] .timer

[Keith Medcalf]

According to Mark Russinovich's fine RAMMap tool (part of the SysInternals 
Suite) Windows is "converting" the file from a "standard" FileSystem cached 
file to a "Memory Mapped" file and back again.  "Memory Mapped" files occupy 
RAM (as in allocate RAM so it cannot be used for other purposes) whereas the 
standard "Cached" files are just stored in otherwise unused RAM and are 
completely discardable pages that can be instantly re-used for another 
purpose).  

No idea why Windows is doing this useless crap ... although I do recall someone 
else quite a while back questioning why Windows was creating "Memory Mapped" 
files when none had been requested ... Now I think we have the answer.  Bet you 
Microsoft is missing some brace-brackets in their code somewhere after an if 
statement ... thus fricking with their cache management code ...

---
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: Friday, 15 June, 2018 16:22
>To: SQLite mailing list
>Subject: Re: [sqlite] .timer
>
>
>Ok, I have been able to reproduce this but have not figured out what
>is happening.
>
>And you are correct, it appears to be related to "reading backwards"
>somehow.  It is unaffected by the WIN32 cache mode that is set (I
>tested all modes and they all behave the same -- except that I could
>not test the NOBUFFER mode since SQLite3 I/Os are not page and
>cluster aligned), it happens whether using the WIN32 allocator or the
>system malloc.  It happens with both 32-bit code and 64-bit code.
>
>The memory *IS NOT* being allocated to SQLite3 or whatever process is
>running the sqlite3 database engine (its memory usage stays constant)
>and it is not actually being "used by" the FileSystem cache, it is
>not allocated to the process at all.  Even though it appears to be
>"eating" several Gigs of RAM (actually, it almost looks like a
>duplicate cache of the file being read, but this makes no logical
>sense whatsoever), this ram is not being allocated to the process
>because the 32-process does not get an Out-of-Memory error, and 32-
>bit processes have a limited arena to play in.  It is also not
>"dirty" write buffers since those are accounted for separately.
>
>The RAM is being allocated without being committed (so it is a
>temporary usage thing).  However unlike actual FileSystem cache
>(which uses RAM not allocated for any other purpose) this appears
>usage appears to have "priority" over the FileSystem cache and over
>the process working set thus forcing working set and/or filesystem
>cache to be discarded.
>
>Interestingly, sometimes the allocated memory "cleans itself up",
>however, if you follow the "desc" by an "asc" operation it appears to
>"clean up" and convert that usage into something else.  It is really
>very weird and is something in Windows itself, though what I have not
>a clue.
>
>I never noticed it because I have lots of physical space and no swap
>file and their associated management overhead/tables, but it does
>show up as "allocated" memory and I presume that if you do not have
>lots and lots of extra RAM available it has an impact on performance
>as Windows fracks and un-fracks 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 10:53
>>To: SQLite mailing list
>>Subject: Re: [sqlite] .timer
>>
>>Thanks Keith. I did look on the performance page but didn’t realise
>>clicking the items on the left brought up different info. I am on
>>windows 10 pro.
>>
>>I haven’t had time to try creating the ‘personalised’ sqlite3.exe so
>>the following relates to my own app run under the original
>conditions
>>(i.e. without your code changes). In that app, rather than asking
>>sqlite to create the table of RowIDs, my code steps through the
>>select and stores the RowIDs in either a vector or a temporary file.
>>For storing to the tmpfile it stores the data in a vector of size
>>8192, writes those 8192 int64’s to the file using fwrite and so on
>in
>>blocks of 8192. Note that if the procedure is run more than once
>>without closing the app the same tmpfile is reused.
>>
>>The following relates to storing the RowIDs in a temp file run in 64
>>bit mode.
>>
>>select RowID from Test order by RowID;
>>-
>>FlushMem
>>Cached < 1 GB
>>Run
>>During run Cached climbs in linear fashion to ~4.7 GB in 20.5 secs.
>>Run again without closing app
>>During run Cached increases slightly (5.4 max) then returns to 4.7
>GB
>>in 16.5 secs.
>>Each subsequent run (without closing or flushing) returns similar
>>results and ditto for flushing and going through the whole 

Re: [sqlite] .timer

[Simon Slavin]

On 15 Jun 2018, at 11:21pm, Keith Medcalf  wrote:

> And you are correct, it appears to be related to "reading backwards" somehow.

If you're comparing the two directions of reading the same file, there are ways 
to fake out Windows' read-ahead tricks and see if they're what's messing up 
your program.

A) Plug in a Flash drive and create/open the file on that instead of the boot 
drive.

B) Rename the file to something which doesn't have a database-like extension.  
I suggest an extension of ".333'.

Try those -- separately not together -- and see if you see any difference.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer

[Keith Medcalf]

Ok, I have been able to reproduce this but have not figured out what is 
happening.  

And you are correct, it appears to be related to "reading backwards" somehow.  
It is unaffected by the WIN32 cache mode that is set (I tested all modes and 
they all behave the same -- except that I could not test the NOBUFFER mode 
since SQLite3 I/Os are not page and cluster aligned), it happens whether using 
the WIN32 allocator or the system malloc.  It happens with both 32-bit code and 
64-bit code.

The memory *IS NOT* being allocated to SQLite3 or whatever process is running 
the sqlite3 database engine (its memory usage stays constant) and it is not 
actually being "used by" the FileSystem cache, it is not allocated to the 
process at all.  Even though it appears to be "eating" several Gigs of RAM 
(actually, it almost looks like a duplicate cache of the file being read, but 
this makes no logical sense whatsoever), this ram is not being allocated to the 
process because the 32-process does not get an Out-of-Memory error, and 32-bit 
processes have a limited arena to play in.  It is also not "dirty" write 
buffers since those are accounted for separately.

The RAM is being allocated without being committed (so it is a temporary usage 
thing).  However unlike actual FileSystem cache (which uses RAM not allocated 
for any other purpose) this appears usage appears to have "priority" over the 
FileSystem cache and over the process working set thus forcing working set 
and/or filesystem cache to be discarded.

Interestingly, sometimes the allocated memory "cleans itself up", however, if 
you follow the "desc" by an "asc" operation it appears to "clean up" and 
convert that usage into something else.  It is really very weird and is 
something in Windows itself, though what I have not a clue.

I never noticed it because I have lots of physical space and no swap file and 
their associated management overhead/tables, but it does show up as "allocated" 
memory and I presume that if you do not have lots and lots of extra RAM 
available it has an impact on performance as Windows fracks and un-fracks 
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 10:53
>To: SQLite mailing list
>Subject: Re: [sqlite] .timer
>
>Thanks Keith. I did look on the performance page but didn’t realise
>clicking the items on the left brought up different info. I am on
>windows 10 pro.
>
>I haven’t had time to try creating the ‘personalised’ sqlite3.exe so
>the following relates to my own app run under the original conditions
>(i.e. without your code changes). In that app, rather than asking
>sqlite to create the table of RowIDs, my code steps through the
>select and stores the RowIDs in either a vector or a temporary file.
>For storing to the tmpfile it stores the data in a vector of size
>8192, writes those 8192 int64’s to the file using fwrite and so on in
>blocks of 8192. Note that if the procedure is run more than once
>without closing the app the same tmpfile is reused.
>
>The following relates to storing the RowIDs in a temp file run in 64
>bit mode.
>
>select RowID from Test order by RowID;
>-
>FlushMem
>Cached < 1 GB
>Run
>During run Cached climbs in linear fashion to ~4.7 GB in 20.5 secs.
>Run again without closing app
>During run Cached increases slightly (5.4 max) then returns to 4.7 GB
>in 16.5 secs.
>Each subsequent run (without closing or flushing) returns similar
>results and ditto for flushing and going through the whole thing
>again.
>App closed down.
>Cached stays ~ 4.7 GB
>Run returns much the same results had it not been shut down.
>All of this is pretty much the results I’d expect – first run a bit
>slower than subsequent runs where the data is already cached. Nothing
>to see here imo.
>
>select RowID from Test order by RowID desc;
>
>FlushMem
>Cached < 1 GB
>Run
>During run Cached rises very slowly but never above 1.5 GB. When the
>procedure finishes running Cached is showing under 1 GB but at the
>moment it finishes it jumps to 5 GB. Time = 91.4 secs.
>Run again without closing app.
>During run Cached gradually reduces from 5GB to 1GB in linear fashion
>but bursts back up 5 GB when procedure finishes. Time = 16 secs.
>Each subsequent run (without closing or flushing) returns similar
>results and ditto for flushing and going through the whole thing
>again (first run again takes over 90 secs with Cached < 1 GB
>throughout but bursts to 4.7 GB as procedure finishes).
>Plenty to fathom here.
>
>We now switch to vector mode. Select is stepped through and each
>RowID returned is stored in the vector of size 100,000,000. The
>tmpfile is never created. NB I’m doing the desc select first this

Re: [sqlite] Recursive query

[R Smith]

I confirm your query determining the fully expanded list of A_ids works as
I explicitly tried it.

Now. I have actually simplified the problem before presenting it here in
the list. But I just tried to extrapolate the solution back  to the
original problem and failed miserably :-(


Yes, that is the problem with simplifying problems - one is liable to 
simplify-out some essential properties of the problem.



The original problem is not much more complicated, and for clarity, I will
get rid of the Table B step as I can understand that 2nd step perfectly
well.

So there is a 1 to 1 relationship between X objects and A objects.
A objects are complex and have elements. This elements then refer to either
another X (never the containing X) or refer to a B object.

Table X
idA_id
=
21  1
23  3


Table A
A_id
==
1
3
...

Table Aelements
object_idindex  X_idB_id
=
10  NULL  5
11  23   NULL
12  NULL  7
30  NULL  2
31  NULL  3
...

Now extrapolating your recursive query, with the main input object is (X
id=21) I've tried:

WITH RECURSIVE AE(object_id, X_id, B_id) AS (
 SELECT object_id , X_id , B_id FROM Aelements WHERE object_id IN
(SELECT A_id FROM X WHERE id=21) AND X_id IS NULL
 UNION ALL
 SELECT Aelements.object_id , Aelements.X_id , Aelements.B_id FROM
Aelements, AE WHERE AE.object_id IN (SELECT A_id FROM X WHERE id IN (SELECT
X_id FROM Aelements WHERE object_id IN (SELECT A_id FROM X WHERE id=21) AND
X_id IS NOT NULL)))
SELECT * FROM AE;

But I think I'm failing to express the recursion in the above.
It doesn't work.

Basically in above, the resulting flattened Aelements list should be  1 3,
and therefore the corresponding B_id should be 5 7 2 3 , and finally the
last step from table B.


Right, this schema was made by a masochist. :)

I see David R. already had a go at a solution which seems perfect and it 
already probably works for you, I'm only going to elaborate a bit more 
for education purposes since you seem interested in learning about CTE 
and recursive queries.


Firstly, the table A is analogous to an Index and completely superfluous 
to our problem, so it can be ignored from the start. Aelements already 
contains the object id's we need, so we do not need table A.

I'm going to refer to Aelements simply as "E" from now on.

So if we start with Table X, the problem states (if my understanding is 
correct) that we need to check for the X.A_id of the given X.id, if 
there are any E.object_id's which has a reiterating reference back to 
table X in the E.X_id column and expand those corresponding X.A_id 
relational entries until we have exhaustively added every implicated 
A_id that is a 1st, 2nd, ... or n-th tier relation to the original X.id.
This list we will then match to the B values later (which is easy and 
not part of this immediate problem, so I remove the B references 
completely from the CTE).


In the first (top) part of any CTE table is the root or origin query - 
which for our case is simply the original referenced X.id, so if we make 
a CTE with only the root part, it would be:

WITH AE(X_id, A_id) AS (
    SELECT X.id, X.A_id FROM X WHERE X.id = ?1
)
SELECT * FROM AE;

Note that we start from X_id and we look to expand for it the A_id 
values, and I know later we will need to refer back to the X_id values, 
so that is why I chose those field names in that order in the CTE.  The 
result of this query should be the single line from the X table that 
gives the correct root id. (Nice thing about CTEs is that you can at any 
point select from them, like we do here, to see what the results are so 
far).


Now we are ready to add the recursive part - We need for every X_id 
added to that CTE table, to also add any related A_id values which it 
points to AND which has a corresponding X_id in the E table that refers 
back to another X.id in the X table (i.e where the E.X_id column is not 
null).
The recursive query part is simply the query that follows the root via a 
UNION *and* that refers back to the containing CTE. So to make AE a 
recursive CTE, we will add our id-expanding query after the UNION and 
refer (JOIN) back to the same AE cte  *as if it always contained the 
full list of X_id references we need* (this is the bit that feels a 
little like magic).


WITH AE(X_id, A_id) AS (
    SELECT X.id, X.A_id, NULL FROM X WHERE X.id = ?1
    UNION ALL
    SELECT E.X_id, X.A_id  FROM Aelements AS E JOIN X ON X.id = E.X_id 
WHERE E.object_id = AE.A_id AND E.X_id IS NOT NULL

)
SELECT * FROM AE;

As we scan the Aelements table for the object_id(s) that matches the 
A_id which our root query produced

[ ...FROM Aelements AS E ... WHERE E.object_id = AE.A_id... ],
any entries found with a valid X_id
[ ... WHERE ... E.X_id IS NOT NULL ]
is then matched back to the X 

[sqlite] Mistake in documentation about xCreate/xConnect vtab methods

[sqlite]

The documentation for the xCreate and xConnect methods for virtual tables give 
the incorrect type.
It says "char**argv" but the actual type should be "const char*const*argv".
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Recursive query

[David Raymond]

with recursive foo (X_id) as (
  values (21)
  union
  select Aelements.X_id
  from X inner join Aelements
on X.A_id = Aelements.object_id
  where X_id is not null
)
select Aelements.*
from X inner join Aelements
  on X.A_id = Aelements.object_id
where X.id in foo
  and Aelements.B_id is not null;

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of MM
Sent: Friday, June 15, 2018 2:03 PM
To: SQLite mailing list; ryansmit...@gmail.com
Subject: Re: [sqlite] Recursive query

>
> On 2018/06/15 3:19 PM, MM wrote:
> I have table A of rows with column "object_id", and 2 NULL columns A_id
> and B_id.
> Each row of A (among rows with the same object_id) either references
> table A itself or table B, ie columns A_id and B_id are mutually
> exclusive.
> e.g.
> Table A
> object_id A_id B_id
> ...
> 1 NULL  5
> 1 3 NULL
> 1 NULL  7
> 3 NULL  2
> 3 NULL  3
> ...
> Table B
> object_id data
> ...
> 2  15
> 3  16
> 5  17
> 7  18
> ...
> My objective is to collect all unique "data" in all the B rows that
> belong to a given A object_id (like '1') and all its "descendants".
> For e.g.:
> given  object "1", the result of the query would be the list 17, 18.
> 15, 16 in no particular order
> given  object "3", the result of the query would be the list 15 16
> Can this be done with a recursive cet query? If so, how?
> Yes it can be done.
> First you need, for a given object_id in A, all the references from A that
> will eventually point to B (i.e. a non-null B_id). That is, you need all
> the A_id items that point back into A to resolve to the entire list of
> object_id rows in A that refers to B_id (and not A_id).
> Thereafter it's a simple thing of taking the A list with B links, linking
> the B values, and getting the Unique entries.
> Step 1: Expand all the object_id items in A which point to another
> object_id in A: This can be done recursively:
> (Assume the given object ID is set in: ?1)
> WITH AE(o_id,A_id) AS (
> SELECT A.object_id, A.A_id  FROM A WHERE A.object_id = ?1
> UNION ALL
> SELECT A.object_id, A.A_id FROM A, AE WHERE AE.A_id = A.object_id
> )
> SELECT AE.o_id FROM AE;
> This gives the fully expanded list of A_id's that are referenced by either
> the given id (?1) or a linking id in A (A_id).
> Now let's simply join all the A table items that is in the list described
> by AE, then join to that the B items where those links exist, and then get
> the distinct items from those:
> WITH AE(o_id,A_id) AS (
> SELECT A.object_id, A.A_id  FROM A WHERE A.object_id = ?1
> UNION ALL
> SELECT A.object_id, A.A_id FROM A, AE WHERE AE.A_id = A.object_id
> )
> SELECT DISTINCT B.data
>   FROM AE
>   JOIN A ON A.object_id = AE.o_id
>   JOIN B ON B.object_id = A.B_id
> ;
> I don't have a testbed handy, but this should work - if not, please post
> again (perhaps with a little more example data) so we can fix it.
> Cheers!
> Ryan


 Hi Ryan,

I confirm your query determining the fully expanded list of A_ids works as
I explicitly tried it.

Now. I have actually simplified the problem before presenting it here in
the list. But I just tried to extrapolate the solution back  to the
original problem and failed miserably :-(
The original problem is not much more complicated, and for clarity, I will
get rid of the Table B step as I can understand that 2nd step perfectly
well.

So there is a 1 to 1 relationship between X objects and A objects.
A objects are complex and have elements. This elements then refer to either
another X (never the containing X) or refer to a B object.

Table X
idA_id
=
21  1
23  3


Table A
A_id
==
1
3
...

Table Aelements
object_idindex  X_idB_id
=
10  NULL  5
11  23   NULL
12  NULL  7
30  NULL  2
31  NULL  3
...

Now extrapolating your recursive query, with the main input object is (X
id=21) I've tried:

WITH RECURSIVE AE(object_id, X_id, B_id) AS (
SELECT object_id , X_id , B_id FROM Aelements WHERE object_id IN
(SELECT A_id FROM X WHERE id=21) AND X_id IS NULL
UNION ALL
SELECT Aelements.object_id , Aelements.X_id , Aelements.B_id FROM
Aelements, AE WHERE AE.object_id IN (SELECT A_id FROM X WHERE id IN (SELECT
X_id FROM Aelements WHERE object_id IN (SELECT A_id FROM X WHERE id=21) AND
X_id IS NOT NULL)))
SELECT * FROM AE;

But I think I'm failing to express the recursion in the above.
It doesn't work.

Basically in above, the resulting flattened Aelements list should be  1 3,
and therefore the corresponding B_id should be 5 7 2 3 , and finally the
last step from table B.

Rds,
MM

Re: [sqlite] SQLite query planner doesn't seem to know when a partial index is COVERING

[David Raymond]

I've seen similar. I think the issue is that since a isn't an indexed column 
then it's not technically covering, despite being given a in the where clause.


sqlite> create index Foo_partial_with_a on Foo (a, b, c) where a = 1;

sqlite> analyze;

sqlite> explain query plan select b from Foo where a = 1 and b = 2 order by c;
selectid|order|from|detail
0|0|0|SEARCH TABLE Foo USING COVERING INDEX Foo_partial_with_a (a=? AND b=?)


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Deon Brewis
Sent: Friday, June 15, 2018 1:09 PM
To: SQLite mailing list
Subject: [sqlite] SQLite query planner doesn't seem to know when a partial 
index is COVERING

Looks like a missed optimization opportunity here. Well, more than that - there 
doesn't appear to be a way to get SQLITE to automatically use a partial index 
if a similar non-partial index exists.

E.g.
create table Foo(a,b,c,d,e);
create index Foo_inx on Foo(a,b,c);
create index Foo_partial_inx on Foo(b,c) where a = 1;

insert into Foo(a,b,c,d,e) values(1,2,3,4,5);
insert into Foo(a,b,c,d,e) values(1,2,2,4,6);
insert into Foo(a,b,c,d,e) values(1,2,5,1,1);
insert into Foo(a,b,c,d,e) values(2,1,6,1,1);
insert into Foo(a,b,c,d,e) values(6,4,6,1,1);

analyze Foo;
explain query plan select b from Foo where a=1 and b=2 order by c;
> SEARCH TABLE Foo USING COVERING INDEX Foo_inx (a=? AND b=?)


In this case SQLite picks Foo_inx over Foo_partial_inx. I suspect it's because 
it doesn't know Foo_partial_inx is effectively a covering index because if I 
force the index by hand, it doesn't list it as a COVERING index:

explain query plan select b from Foo indexed by Foo_partial_inx where a=1 and 
b=2 order by c;
> SEARCH TABLE Foo USING INDEX Foo_partial_inx (b=?)

And I suspect that's why it picks Foo_inx over Foo_partial_inx.  But otherwise 
this behavior seems to be exactly what I want though (will need to step through 
an 'explain' to make sure it doesn't do main table lookups), but it requires an 
INDEXED BY to get there.


As a workaround, if I repeat the WHERE clause field ('a') in the partial index 
field list, THEN it starts using the partial index automatically:

create index Foo_partial_inx2 on Foo(a,b,c) where a = 1;
analyze Foo;
> SEARCH TABLE Foo USING COVERING INDEX Foo_partial_inx2 (a=? AND b=?)

But that has 2 problems:
a) this makes the index bigger due to the extra (very unnecessary) column
b) the executer doesn't seem to take into the account that this is a partial 
index so it searches for 'a' (minor issue)

Of course the partial index still has less rows than without partial so it's 
still a win, but still - it shouldn't need 'a' to be repeated on every row. 
Either way, though there are issues with the workaround, the bigger issue is 
that it doesn't automatically pick the original Foo_partial_inx in the first 
place.

- Deon

___
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

Re: [sqlite] [OT} Posting through Gmane

[Igor Tandetnik]

On 6/15/2018 9:33 AM, Lifepillar wrote:

I use Gmane to follow this mailing list and Fossil's. I have
subscribed to both lists. While I can post through Gmane to this
list, however, I cannot post to fossil-users: messages keep bouncing
back with "Address not found, ..., The response was 550 unknown
user".


Fellow Gmane user here. At one point, sqlite-users mailing list changed its 
email address (from sqlite-us...@sqlite.org to 
sqlite-users@mailinglists.sqlite.org if I recall correctly), but Gmane had the 
old address. I asked Gmane to have the email updated in their system, and they 
did - simple as that. Maybe something similar happened to fossil-users

However, that was before 
https://lars.ingebrigtsen.no/2016/07/28/the-end-of-gmane , while gmane.org site 
was still functioning and Lars still responded to email. I have no idea who's 
in charge now, if anyone, and how to go about support requests.
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Back on-line. Was: Mailing list shutting down...

[J. King]

On June 15, 2018 12:17:31 PM EDT, dmp  wrote:
>> Mailing lists are now back on-line and once again accepting
>> subscriptions.  I have implemented measures to block the subscription
>> robots and to better log subscription activity to better detect
>future
>> mischief.
>
>> I consider this to be a stop-gap measure that will buy me some time
>> to implement and test a better log-term solution. .
>
>> D. Richard Hipp
>> d...@sqlite.org
>
>As a comment, again with past post with regard to Mailing List.
>
>This mailing list is a very informative, simple, and a conveniant
>method to disperse information in a bulk format. A change to a web
>interface, (forum, other), that requires a login each day is most
>likely going to push me away.
>
>Hope a fix can be accomplished.
>
>danap.
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Opinions on this have made the rounds here before, but I will reiterate that I 
feel the same. I would be especially sad to see this discussion list move to 
Discourse (as was suggested and apparently explored earlier this week), as I 
find that software very unresponsive and difficult to use on Firefox for 
Android, and I do much of my reading of this list on the go (like right now).


-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Recursive query

[MM]

>
> On 2018/06/15 3:19 PM, MM wrote:
> I have table A of rows with column "object_id", and 2 NULL columns A_id
> and B_id.
> Each row of A (among rows with the same object_id) either references
> table A itself or table B, ie columns A_id and B_id are mutually
> exclusive.
> e.g.
> Table A
> object_id A_id B_id
> ...
> 1 NULL  5
> 1 3 NULL
> 1 NULL  7
> 3 NULL  2
> 3 NULL  3
> ...
> Table B
> object_id data
> ...
> 2  15
> 3  16
> 5  17
> 7  18
> ...
> My objective is to collect all unique "data" in all the B rows that
> belong to a given A object_id (like '1') and all its "descendants".
> For e.g.:
> given  object "1", the result of the query would be the list 17, 18.
> 15, 16 in no particular order
> given  object "3", the result of the query would be the list 15 16
> Can this be done with a recursive cet query? If so, how?
> Yes it can be done.
> First you need, for a given object_id in A, all the references from A that
> will eventually point to B (i.e. a non-null B_id). That is, you need all
> the A_id items that point back into A to resolve to the entire list of
> object_id rows in A that refers to B_id (and not A_id).
> Thereafter it's a simple thing of taking the A list with B links, linking
> the B values, and getting the Unique entries.
> Step 1: Expand all the object_id items in A which point to another
> object_id in A: This can be done recursively:
> (Assume the given object ID is set in: ?1)
> WITH AE(o_id,A_id) AS (
> SELECT A.object_id, A.A_id  FROM A WHERE A.object_id = ?1
> UNION ALL
> SELECT A.object_id, A.A_id FROM A, AE WHERE AE.A_id = A.object_id
> )
> SELECT AE.o_id FROM AE;
> This gives the fully expanded list of A_id's that are referenced by either
> the given id (?1) or a linking id in A (A_id).
> Now let's simply join all the A table items that is in the list described
> by AE, then join to that the B items where those links exist, and then get
> the distinct items from those:
> WITH AE(o_id,A_id) AS (
> SELECT A.object_id, A.A_id  FROM A WHERE A.object_id = ?1
> UNION ALL
> SELECT A.object_id, A.A_id FROM A, AE WHERE AE.A_id = A.object_id
> )
> SELECT DISTINCT B.data
>   FROM AE
>   JOIN A ON A.object_id = AE.o_id
>   JOIN B ON B.object_id = A.B_id
> ;
> I don't have a testbed handy, but this should work - if not, please post
> again (perhaps with a little more example data) so we can fix it.
> Cheers!
> Ryan


 Hi Ryan,

I confirm your query determining the fully expanded list of A_ids works as
I explicitly tried it.

Now. I have actually simplified the problem before presenting it here in
the list. But I just tried to extrapolate the solution back  to the
original problem and failed miserably :-(
The original problem is not much more complicated, and for clarity, I will
get rid of the Table B step as I can understand that 2nd step perfectly
well.

So there is a 1 to 1 relationship between X objects and A objects.
A objects are complex and have elements. This elements then refer to either
another X (never the containing X) or refer to a B object.

Table X
idA_id
=
21  1
23  3


Table A
A_id
==
1
3
...

Table Aelements
object_idindex  X_idB_id
=
10  NULL  5
11  23   NULL
12  NULL  7
30  NULL  2
31  NULL  3
...

Now extrapolating your recursive query, with the main input object is (X
id=21) I've tried:

WITH RECURSIVE AE(object_id, X_id, B_id) AS (
SELECT object_id , X_id , B_id FROM Aelements WHERE object_id IN
(SELECT A_id FROM X WHERE id=21) AND X_id IS NULL
UNION ALL
SELECT Aelements.object_id , Aelements.X_id , Aelements.B_id FROM
Aelements, AE WHERE AE.object_id IN (SELECT A_id FROM X WHERE id IN (SELECT
X_id FROM Aelements WHERE object_id IN (SELECT A_id FROM X WHERE id=21) AND
X_id IS NOT NULL)))
SELECT * FROM AE;

But I think I'm failing to express the recursion in the above.
It doesn't work.

Basically in above, the resulting flattened Aelements list should be  1 3,
and therefore the corresponding B_id should be 5 7 2 3 , and finally the
last step from table B.

Rds,
MM
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite query planner doesn't seem to know when a partial index is COVERING

[Deon Brewis]

Looks like a missed optimization opportunity here. Well, more than that - there 
doesn't appear to be a way to get SQLITE to automatically use a partial index 
if a similar non-partial index exists.

E.g.
create table Foo(a,b,c,d,e);
create index Foo_inx on Foo(a,b,c);
create index Foo_partial_inx on Foo(b,c) where a = 1;

insert into Foo(a,b,c,d,e) values(1,2,3,4,5);
insert into Foo(a,b,c,d,e) values(1,2,2,4,6);
insert into Foo(a,b,c,d,e) values(1,2,5,1,1);
insert into Foo(a,b,c,d,e) values(2,1,6,1,1);
insert into Foo(a,b,c,d,e) values(6,4,6,1,1);

analyze Foo;
explain query plan select b from Foo where a=1 and b=2 order by c;
> SEARCH TABLE Foo USING COVERING INDEX Foo_inx (a=? AND b=?)


In this case SQLite picks Foo_inx over Foo_partial_inx. I suspect it's because 
it doesn't know Foo_partial_inx is effectively a covering index because if I 
force the index by hand, it doesn't list it as a COVERING index:

explain query plan select b from Foo indexed by Foo_partial_inx where a=1 and 
b=2 order by c;
> SEARCH TABLE Foo USING INDEX Foo_partial_inx (b=?)

And I suspect that's why it picks Foo_inx over Foo_partial_inx.  But otherwise 
this behavior seems to be exactly what I want though (will need to step through 
an 'explain' to make sure it doesn't do main table lookups), but it requires an 
INDEXED BY to get there.


As a workaround, if I repeat the WHERE clause field ('a') in the partial index 
field list, THEN it starts using the partial index automatically:

create index Foo_partial_inx2 on Foo(a,b,c) where a = 1;
analyze Foo;
> SEARCH TABLE Foo USING COVERING INDEX Foo_partial_inx2 (a=? AND b=?)

But that has 2 problems:
a) this makes the index bigger due to the extra (very unnecessary) column
b) the executer doesn't seem to take into the account that this is a partial 
index so it searches for 'a' (minor issue)

Of course the partial index still has less rows than without partial so it's 
still a win, but still - it shouldn't need 'a' to be repeated on every row. 
Either way, though there are issues with the workaround, the bigger issue is 
that it doesn't automatically pick the original Foo_partial_inx in the first 
place.

- Deon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer

[x]

Thanks Keith. I did look on the performance page but didn’t realise clicking 
the items on the left brought up different info. I am on windows 10 pro.

I haven’t had time to try creating the ‘personalised’ sqlite3.exe so the 
following relates to my own app run under the original conditions (i.e. without 
your code changes). In that app, rather than asking sqlite to create the table 
of RowIDs, my code steps through the select and stores the RowIDs in either a 
vector or a temporary file. For storing to the tmpfile it stores the data in a 
vector of size 8192, writes those 8192 int64’s to the file using fwrite and so 
on in blocks of 8192. Note that if the procedure is run more than once without 
closing the app the same tmpfile is reused.

The following relates to storing the RowIDs in a temp file run in 64 bit mode.

select RowID from Test order by RowID;
-
FlushMem
Cached < 1 GB
Run
During run Cached climbs in linear fashion to ~4.7 GB in 20.5 secs.
Run again without closing app
During run Cached increases slightly (5.4 max) then returns to 4.7 GB in 16.5 
secs.
Each subsequent run (without closing or flushing) returns similar results and 
ditto for flushing and going through the whole thing again.
App closed down.
Cached stays ~ 4.7 GB
Run returns much the same results had it not been shut down.
All of this is pretty much the results I’d expect – first run a bit slower than 
subsequent runs where the data is already cached. Nothing to see here imo.

select RowID from Test order by RowID desc;

FlushMem
Cached < 1 GB
Run
During run Cached rises very slowly but never above 1.5 GB. When the procedure 
finishes running Cached is showing under 1 GB but at the moment it finishes it 
jumps to 5 GB. Time = 91.4 secs.
Run again without closing app.
During run Cached gradually reduces from 5GB to 1GB in linear fashion but 
bursts back up 5 GB when procedure finishes. Time = 16 secs.
Each subsequent run (without closing or flushing) returns similar results and 
ditto for flushing and going through the whole thing again (first run again 
takes over 90 secs with Cached < 1 GB throughout but bursts to 4.7 GB as 
procedure finishes).
Plenty to fathom here.

We now switch to vector mode. Select is stepped through and each RowID returned 
is stored in the vector of size 100,000,000. The tmpfile is never created. NB 
I’m doing the desc select first this time just in case.

select RowID from Test order by RowID desc;
-
FlushMem
Cached < 1 GB
Run
During run Cached never gets beyond 600 MB (for about 20 secs it seemed frozen 
on 297 MB). When the procedure finishes it’s showing 600 MB then shortly after 
shows 4.8 GB. Time = 85.3 secs.
Run again without closing app.
During run Cached linearly decreases to 900 MB then bursts to 4.8 GB on 
completion. Time =  14 secs.
Each subsequent run (without closing or flushing) returns similar results and 
ditto for flushing and going through the whole thing again (first run takes 96? 
secs with Cached < 1 GB throughout but bursts to 4.7 GB as procedure finishes).
Similar to previous test.

select RowID from Test order by RowID;
-
FlushMem
Cached < 1 GB
Run
During run Cached climbs in linear fashion to ~4.7 GB in 63.3 secs. Unexpected 
time?
Run again without closing app
During run Cached stays approximately the same (~4.7 GB) in 14 secs.
Each subsequent run (without closing or flushing) returns similar results and 
ditto for flushing and going through the whole thing again (first run again 
took 60 secs).
App closed down.
Cached stays ~ 4.7 GB
Run returns much the same results had it not been shut down (14s).

This last unexpected set of results suggests it’s nothing to do with the select 
direction. Because it differed so much from the first test I went through test 
4 several times but always got similar results. I also ran the first test 
(tmpfile version) another couple of times but, again, there was no change. It 
was the only one that gave me the expected results. I’m totally lost.







___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Back on-line. Was: Mailing list shutting down...

[dmp]

> Mailing lists are now back on-line and once again accepting
> subscriptions.  I have implemented measures to block the subscription
> robots and to better log subscription activity to better detect future
> mischief.

> I consider this to be a stop-gap measure that will buy me some time
> to implement and test a better log-term solution. .

> D. Richard Hipp
> d...@sqlite.org

As a comment, again with past post with regard to Mailing List.

This mailing list is a very informative, simple, and a conveniant
method to disperse information in a bulk format. A change to a web
interface, (forum, other), that requires a login each day is most
likely going to push me away.

Hope a fix can be accomplished.

danap.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Recursive query

[R Smith]

On 2018/06/15 3:19 PM, MM wrote:

I have table A of rows with column "object_id", and 2 NULL columns
A_id and B_id.
Each row of A (among rows with the same object_id) either references
table A itself or table B, ie columns A_id and B_id are mutually
exclusive.

e.g.

Table A
object_id A_id B_id
...
1 NULL  5
1 3 NULL
1 NULL  7
3 NULL  2
3 NULL  3
...

Table B
object_id data
...
2  15
3  16
5  17
7  18
...

My objective is to collect all unique "data" in all the B rows that
belong to a given A object_id (like '1') and all its "descendants".

For e.g.:
given  object "1", the result of the query would be the list 17, 18.
15, 16 in no particular order
given  object "3", the result of the query would be the list 15 16

Can this be done with a recursive cet query? If so, how?


Yes it can be done.

First you need, for a given object_id in A, all the references from A 
that will eventually point to B (i.e. a non-null B_id). That is, you 
need all the A_id items that point back into A to resolve to the entire 
list of object_id rows in A that refers to B_id (and not A_id).


Thereafter it's a simple thing of taking the A list with B links, 
linking the B values, and getting the Unique entries.


Step 1: Expand all the object_id items in A which point to another 
object_id in A: This can be done recursively:

(Assume the given object ID is set in: ?1)

WITH AE(o_id,A_id) AS (
    SELECT A.object_id, A.A_id  FROM A WHERE A.object_id = ?1
    UNION ALL
    SELECT A.object_id, A.A_id FROM A, AE WHERE AE.A_id = A.object_id
)
SELECT AE.o_id FROM AE;

This gives the fully expanded list of A_id's that are referenced by 
either the given id (?1) or a linking id in A (A_id).


Now let's simply join all the A table items that is in the list 
described by AE, then join to that the B items where those links exist, 
and then get the distinct items from those:


WITH AE(o_id,A_id) AS (
    SELECT A.object_id, A.A_id  FROM A WHERE A.object_id = ?1
    UNION ALL
    SELECT A.object_id, A.A_id FROM A, AE WHERE AE.A_id = A.object_id
)
SELECT DISTINCT B.data
  FROM AE
  JOIN A ON A.object_id = AE.o_id
  JOIN B ON B.object_id = A.B_id
;

I don't have a testbed handy, but this should work - if not, please post 
again (perhaps with a little more example data) so we can fix it.


Cheers!
Ryan



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Recursive query

[David Raymond]

Something like this? Your seed number will go into the values statement at the 
start.

with recursive foo (A_id) as (
  values (1)

  union

  select A.A_id
  from
  foo inner join A
on foo.A_id = A.object_id
  where A.A_id is not null
)
select distinct B.data
from
foo inner join A
  on foo.A_id = A.object_id
inner join B
  on A.B_id = B.object_id;



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of MM
Sent: Friday, June 15, 2018 9:19 AM
To: SQLite mailing list
Subject: [sqlite] Recursive query

I have table A of rows with column "object_id", and 2 NULL columns
A_id and B_id.
Each row of A (among rows with the same object_id) either references
table A itself or table B, ie columns A_id and B_id are mutually
exclusive.

e.g.

Table A
object_id A_id B_id
...
1 NULL  5
1 3 NULL
1 NULL  7
3 NULL  2
3 NULL  3
...

Table B
object_id data
...
2  15
3  16
5  17
7  18
...

My objective is to collect all unique "data" in all the B rows that
belong to a given A object_id (like '1') and all its "descendants".

For e.g.:
given  object "1", the result of the query would be the list 17, 18.
15, 16 in no particular order
given  object "3", the result of the query would be the list 15 16

Can this be done with a recursive cet query? If so, how?

Rds,
___
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

Re: [sqlite] .timer

[Keith Medcalf]

Your other "right", the one on the left :)


---
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: Friday, 15 June, 2018 07:56
>To: SQLite mailing list
>Subject: Re: [sqlite] .timer
>
>
>You are using Windows 10?  It is on the "Performance" tab, select the
>wee graph on the right for "Memory".  In the detail, right underneath
>"Available" and beside "Committed" at the bottom where all the text
>is.  Oh, you have to be in "more details" view, not in the "simple"
>default view ...
>
>---
>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 curmudgeon
>>Sent: Friday, 15 June, 2018 07:13
>>To: sqlite-users@mailinglists.sqlite.org
>>Subject: Re: [sqlite] .timer
>>
>>PS I can't find 'cache' in task manager.
>>
>>
>>
>>--
>>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
>
>
>
>___
>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

Re: [sqlite] .timer

[Keith Medcalf]

You are using Windows 10?  It is on the "Performance" tab, select the wee graph 
on the right for "Memory".  In the detail, right underneath "Available" and 
beside "Committed" at the bottom where all the text is.  Oh, you have to be in 
"more details" view, not in the "simple" default view ...

---
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 curmudgeon
>Sent: Friday, 15 June, 2018 07:13
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] .timer
>
>PS I can't find 'cache' in task manager.
>
>
>
>--
>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



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer

[Keith Medcalf]

>You spoon fed me on a previous thread how to load extensions using a
>core_init function placed at the end of the sqlite3.c code. I do have
>the series.c in my core_init so it is available to me and works fine
>in my cpp code. I don’t see how that relates to sqlite3.exe though.
>How do you get generate_series into that (to say nothing of those
>code changes)? The documentation says

shell.c is the "application" that gets linked with "sqlite3.c" SQLite Library 
to create "sqlite3.exe".  You can create a file which has shell.c appended to 
the end of sqlite3.c (which already has the additional series.c and core_init 
appended) and compile the result as an executable to get sqlite3.exe, or you 
can compile the two separately and link them together to form an sqlite3.exe 
that has your additional extensions available.

>“The generate_series(START,END,STEP) table-valued
>function is a loadable
>extension included in the SQLite
>source tree, and compiled into the command-line
>shell.”

I don't think that the CLI includes generate_series, though you can compile 
series.c as a loadable extension (its own DLL) and load it into the CLI.

>That said, I can tell from my own app that having
>SQLITE_WIN32_FILE_RANDOM defined made no difference, in fact it made
>it considerably worse for the descending query. With the memory
>flushed the descending query was taking 10 times the time it took
>with the cache unflushed. That’s up from around 4.

That is very strange indeed!

>Having widows defender turned off changed nothing.

Ok, so it is not defender.

Something however is interfering with being able to read a file properly and 
that strikes me as very strange -- especially since on an SSD you should be 
able to read forward-sequential, backward-sequential, and in completely random 
order in about the same elapsed time ...

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] [OT} Posting through Gmane

[Lifepillar]

I use Gmane to follow this mailing list and Fossil's. I have
subscribed to both lists. While I can post through Gmane to this
list, however, I cannot post to fossil-users: messages keep bouncing
back with "Address not found, ..., The response was 550 unknown
user".

Is there any difference in the configuration of the two mailing
lists? AFAIK, both lists are non-public.

Note that I had this issue quite some time before the "Mailing list
shutting down" event, so I don't think that it is related to the
recent changes. I have tried today again, however, and I have the
same issue.

Thanks,
Life.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Recursive query

[MM]

I have table A of rows with column "object_id", and 2 NULL columns
A_id and B_id.
Each row of A (among rows with the same object_id) either references
table A itself or table B, ie columns A_id and B_id are mutually
exclusive.

e.g.

Table A
object_id A_id B_id
...
1 NULL  5
1 3 NULL
1 NULL  7
3 NULL  2
3 NULL  3
...

Table B
object_id data
...
2  15
3  16
5  17
7  18
...

My objective is to collect all unique "data" in all the B rows that
belong to a given A object_id (like '1') and all its "descendants".

For e.g.:
given  object "1", the result of the query would be the list 17, 18.
15, 16 in no particular order
given  object "3", the result of the query would be the list 15 16

Can this be done with a recursive cet query? If so, how?

Rds,
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer

[curmudgeon]

PS I can't find 'cache' in task manager.



--
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

Re: [sqlite] .timer

[x]

Keith,



You spoon fed me on a previous thread how to load extensions using a core_init 
function placed at the end of the sqlite3.c code. I do have the series.c in my 
core_init so it is available to me and works fine in my cpp code. I don’t see 
how that relates to sqlite3.exe though. How do you get generate_series into 
that (to say nothing of those code changes)? The documentation says



“The generate_series(START,END,STEP) table-valued 
function is a loadable 
extension included in the SQLite source tree, 
and compiled into the command-line shell.”



That said, I can tell from my own app that having SQLITE_WIN32_FILE_RANDOM 
defined made no difference, in fact it made it considerably worse for the 
descending query. With the memory flushed the descending query was taking 10 
times the time it took with the cache unflushed. That’s up from around 4.



Having widows defender turned off changed nothing.




From: sqlite-users  on behalf of 
Keith Medcalf 
Sent: Friday, June 15, 2018 9:41:18 AM
To: SQLite mailing list
Subject: Re: [sqlite] .timer


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=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 

Re: [sqlite] .timer

[David Raymond]

2 MB, not 2 GB.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Warren Young
Sent: Thursday, June 14, 2018 5:54 PM
To: SQLite mailing list
Subject: Re: [sqlite] .timer

By default, SQLite will only cache a smidge under 2 GiB, so it cannot explain 
your result:

https://sqlite.org/pragma.html#pragma_cache_size

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer

[Keith Medcalf]

The increase/decrease  in memory is almost certainly the cache (after running 
the command once and before flushing look and see what Task Manager says for 
"Cached", then look again after you do the flush and see if it releases it.  
This is memory that would otherwise be unused being used by the filesystem 
cache.  However, since USER+SYS is not equal or close to REAL, that means that 
there is something else going on that is consuming time (waiting, defender, or 
that something is being dispatched as a DPC and its execution time is not being 
attributed to your process).


---
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:23
>To: SQLite mailing list
>Subject: Re: [sqlite] .timer
>
>>Actually I cannot reproduce even if I turn off forced RANDOM mode
>for the cache, reduce the size of the >sqlite3 cache to the default,
>and make sure the temp_store is on disk (not my default).
>
>Are you rebooting or flushing the cache between the commands? I don’t
>have any problems (except maybe on the first call) if I don’t flush.
>
>>I do note, however, that the actual CPU used is relatively constant
>(USER+SYS) and that it is the REAL time >only that is going "bonkers"
>which suggests some process other than sqlite is what is causing the
>>slowdown.  It could be something the hardware is doing -- I have
>never used a "tablet" as if it were a >computer ...
>
>After rebooting it doesn’t matter how long I wait before running the
>test. If it’s some background task it must surely be triggered by
>what I’m doing. That said, if the slowness and increasing /
>decreasing memory is down to windows caching the data why is ‘sys’
>not able to include it?
>___
>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

Re: [sqlite] .timer

[Keith Medcalf]

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=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  on
>behalf of Keith Medcalf 
>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 

Re: [sqlite] .timer

[x]

>Actually I cannot reproduce even if I turn off forced RANDOM mode for the 
>cache, reduce the size of the >sqlite3 cache to the default, and make sure the 
>temp_store is on disk (not my default).

Are you rebooting or flushing the cache between the commands? I don’t have any 
problems (except maybe on the first call) if I don’t flush.

>I do note, however, that the actual CPU used is relatively constant (USER+SYS) 
>and that it is the REAL time >only that is going "bonkers" which suggests some 
>process other than sqlite is what is causing the >slowdown.  It could be 
>something the hardware is doing -- I have never used a "tablet" as if it were 
>a >computer ...

After rebooting it doesn’t matter how long I wait before running the test. If 
it’s some background task it must surely be triggered by what I’m doing. That 
said, if the slowness and increasing / decreasing memory is down to windows 
caching the data why is ‘sys’ not able to include it?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Just noticed ... fileio.c cannot be compiled as an extension ...

[Keith Medcalf]

It looks for an external sqlite3_win32_utf8_to_unicode function.

Although this is an exported API function the linker on Windows cannot resolve 
it at compile time.  Even if it could, the loader trampoline could not link it 
back to the API in the original (loading) sqlite3 code (especially not if that 
were an executable and not a DLL).

The function in SQLite3 needs to be added to SQLite3 API call indirection table 
and the "extern" definition removed in the extension, or the code needs to be 
duplicated and made static with a different name in the fileio.c extension file 
itself (so as not to cause symbol collisions when it is included as a builtin). 
 In the latter case it could probably be made static inline in both places 
since it is very small ...

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.





___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer

[x]

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  on behalf of 
Keith Medcalf 
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  on
>behalf of Keith Medcalf 
>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 

Re: [sqlite] .timer

[Keith Medcalf]

Actually I cannot reproduce even if I turn off forced RANDOM mode for the 
cache, reduce the size of the sqlite3 cache to the default, and make sure the 
temp_store is on disk (not my default).

I do note, however, that the actual CPU used is relatively constant (USER+SYS) 
and that it is the REAL time only that is going "bonkers" which suggests some 
process other than sqlite is what is causing the slowdown.  It could be 
something the hardware is doing -- I have never used a "tablet" as if it were a 
computer ...

---
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  on
>behalf of Keith Medcalf 
>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 

Re: [sqlite] .timer

[Keith Medcalf]

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  on
>behalf of Keith Medcalf 
>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 

Re: [sqlite] .timer

[x]

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  on behalf of 
Keith Medcalf 
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<1)
>   ...> select * from cte;
>Run Time: real 34.619 user 34.625000 sys 0.00
>sqlite> drop table test;
>Run Time: real 1.578 user 1.578125 sys 0.00
>sqlite> create table test (x integer, y text);
>Run Time: real 0.000 user 0.00 sys 0.00
>sqlite> insert into test select value,
>'012345678901234567890123456789' from generate_series where start=1
>and stop=1;
>Run Time: real 12.226 user 12.234375 sys 0.00
>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