On 2017/05/26 7:33 AM, Simon Slavin wrote:
On 26 May 2017, at 6:00am, Wout Mertens <wout.mert...@gmail.com> wrote:
Ideally there'd be some way to know if a _step() call will be served from
buffer…
There are (simplified) three possibilities: quick quick, slow slow, and slow
quick.
A) SQLite finds a good index for the search/sort and can easily locate all the
rows it needs to execute the call. In this case, both the initial _step() and
all the others will execute quickly.
B) SQLite can’t find an ideal index for the query but finds one which will
allow it to execute the query acceptably, just skipping down the table
identifying which rows should be processed. In this case, both the initial
_step() and all the others will execute slowly. But if the table is short, or
if your command needs to execute a large proportion of the rows in the table
that might not be very slowly.
C) SQLite can’t find any helpful indexes and decides that the most efficient
way to execute the command involves making a temporary index. In this case,
the initial _step() can take a long time but subsequent _step()s can be fast.
Just to add, there could be a great amount of processing involved in
even starting the query, such as using WITH clauses or SELECTing from a
sub-query or perhaps using an IN operator on another query and the like.
In these cases, my experience is (on all DB systems I use) that the
initial STEP can take several magnitudes more time than the subsequent
ones. It's hard to pin down a rule, it is not a consistent thing.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
