Re: [sqlite] [EXTERNAL] Re: sqlite3_progress_handler(D,N,X,P)

2018-05-25 Thread x 
Thanks Max. It is a bit less cryptic.


From: Max Vlasov<mailto:max.vla...@gmail.com>
Sent: 25 May 2018 15:29
To: SQLite mailing list<mailto:sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] [EXTERNAL] Re: sqlite3_progress_handler(D,N,X,P)

On Fri, May 25, 2018 at 10:29 AM, x <tam118...@hotmail.com> wrote:

> You could have interrupt checks just before the return of a result row
> yourself by creating a Step fct that  called sqlite3_step and then checked
> for an interrupt. It’s when the sqlite3.c code’s being run interrupt is
> most needed. e.g. if a query has a large result set that needs sorting the
> first sqlite3_step could be a very lengthy operation.
>
> The only way I can see around it is to use “select sum(1) from Tbl” to get
> the count. That uses plenty of VMI’s but is slower.
>
>
I suppose Select count(*) is uninterruptible for some historical reasons.
you can also use Select count(rowid) ... (if the table is not WITHOUT
ROWID), it's interruptible, just checked, at least in v3.23.1. Probably
takes the same time as Select sum(1), but a little bit less cryptic when
reading
___
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] [EXTERNAL] Re: sqlite3_progress_handler(D,N,X,P)

2018-05-25 Thread Max Vlasov 
On Fri, May 25, 2018 at 10:29 AM, x  wrote:

> You could have interrupt checks just before the return of a result row
> yourself by creating a Step fct that  called sqlite3_step and then checked
> for an interrupt. It’s when the sqlite3.c code’s being run interrupt is
> most needed. e.g. if a query has a large result set that needs sorting the
> first sqlite3_step could be a very lengthy operation.
>
> The only way I can see around it is to use “select sum(1) from Tbl” to get
> the count. That uses plenty of VMI’s but is slower.
>
>
I suppose Select count(*) is uninterruptible for some historical reasons.
you can also use Select count(rowid) ... (if the table is not WITHOUT
ROWID), it's interruptible, just checked, at least in v3.23.1. Probably
takes the same time as Select sum(1), but a little bit less cryptic when
reading
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: sqlite3_progress_handler(D,N,X,P)

2018-05-25 Thread x 
You could have interrupt checks just before the return of a result row yourself 
by creating a Step fct that  called sqlite3_step and then checked for an 
interrupt. It’s when the sqlite3.c code’s being run interrupt is most needed. 
e.g. if a query has a large result set that needs sorting the first 
sqlite3_step could be a very lengthy operation.



The only way I can see around it is to use “select sum(1) from Tbl” to get the 
count. That uses plenty of VMI’s but is slower.



Disappointing.




From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of 
Hick Gunter <h...@scigames.at>
Sent: Thursday, May 24, 2018 5:49:21 PM
To: 'SQLite mailing list'
Subject: Re: [sqlite] [EXTERNAL] Re: sqlite3_progress_handler(D,N,X,P)

I can't tell without extensively looking into the source code. I expect not.

From my experience I would infer that ResultRow and maybe a few other VMIs are 
actual "interrupt points", because complex joins of virtual tables can continue 
for extended periods of time (and read tons of rows) before reacting to 
sqlite3_interrupt(), possibly just before a result row would have been returned.

I am not an SQLite developer, so this is just unofficial extrapolation.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von x
Gesendet: Donnerstag, 24. Mai 2018 18:30
An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Betreff: [EXTERNAL] Re: [sqlite] sqlite3_progress_handler(D,N,X,P)

Thanks for your input Ryan. I do think milliseconds would be more intuitive 
rather than having to guess the number of VMI’s that will be computed. The 
problem in the case I quote though is that there is only a few VMI’s for 
“select count(*) from Tbl” one of which is a call to the special count() 
function (see “explain select count(*) from Tbl”). Once it enters the count() 
function there is no opportunity to cancel. I.e. either you get the count or 
you don’t.



Gunter, just seen your interesting post. Do you know if that would work while 
the count() function was being executed?




From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of 
R Smith <ryansmit...@gmail.com>
Sent: Thursday, May 24, 2018 5:03:14 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] sqlite3_progress_handler(D,N,X,P)


On 2018/05/24 5:41 PM, x wrote:
> The parameter N is the approximate number of virtual machine 
> instructions<https://sqlite.org/opcode.html> that are evaluated between 
> successive invocations of the callback X.
>
> Would it not have been better if N was the number of milliseconds between 
> invocations? It’s not much use if you want to abort from a lengthy “select 
> count(*) from Tbl” as there are only a couple of virtual machine instructions 
> whereas “select count(*) from Tbl,Tbl” might have billions of VMI’s.

I think VM steps generally happen in quick succession, never minutes apart.

Either way, milliseconds elapsed is equally useless - plus what could possibly 
be easier than measuring time yourself?

Pseudo code example, but it will work for near any platform with some numeric 
representation of "time".
...
curtime = now;
elapsed = curtime - prevtime;
prevtime = curtime;
...
print elapsed;
...


What you cannot do, is deduce VM steps like that, so indeed you are better off 
with the progress handler reporting VM steps (which you cannot deduce) rather 
than elapsed time (which you can).

What we REALLY want however, is to know the final intended VM steps so that 
curstep/finalstep will give us some idea of the progress, but it is impossible 
to know (even in principle) before the actual query result have been produced 
at least once.

So at this point we are condemned to reporting to the user something like "Look 
mate, it's still busy, but at least the current VM steps > previous VM steps, 
so it ain't hung. Shall we wait some more, or cancel?" - to which the good user 
hopefully do not cancel prematurely (though that is hard to expect with zero 
confidence in which exact VM milestone constitutes "premature").
Luckily this is less of a problem in custom system code, since you can test 
every query, even on big data. It gets hard when you make a DB manager of sorts 
in which allowing ANY query is a necessity.

Point being: There is no good answer to 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


___
 Gunter Hick | Software Engineer | Scientific Games Intern

Re: [sqlite] [EXTERNAL] Re: sqlite3_progress_handler(D,N,X,P)

2018-05-24 Thread Hick Gunter 
I can't tell without extensively looking into the source code. I expect not.

From my experience I would infer that ResultRow and maybe a few other VMIs are 
actual "interrupt points", because complex joins of virtual tables can continue 
for extended periods of time (and read tons of rows) before reacting to 
sqlite3_interrupt(), possibly just before a result row would have been returned.

I am not an SQLite developer, so this is just unofficial extrapolation.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von x
Gesendet: Donnerstag, 24. Mai 2018 18:30
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] sqlite3_progress_handler(D,N,X,P)

Thanks for your input Ryan. I do think milliseconds would be more intuitive 
rather than having to guess the number of VMI’s that will be computed. The 
problem in the case I quote though is that there is only a few VMI’s for 
“select count(*) from Tbl” one of which is a call to the special count() 
function (see “explain select count(*) from Tbl”). Once it enters the count() 
function there is no opportunity to cancel. I.e. either you get the count or 
you don’t.



Gunter, just seen your interesting post. Do you know if that would work while 
the count() function was being executed?




From: sqlite-users  on behalf of 
R Smith 
Sent: Thursday, May 24, 2018 5:03:14 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] sqlite3_progress_handler(D,N,X,P)


On 2018/05/24 5:41 PM, x wrote:
> The parameter N is the approximate number of virtual machine 
> instructions that are evaluated between 
> successive invocations of the callback X.
>
> Would it not have been better if N was the number of milliseconds between 
> invocations? It’s not much use if you want to abort from a lengthy “select 
> count(*) from Tbl” as there are only a couple of virtual machine instructions 
> whereas “select count(*) from Tbl,Tbl” might have billions of VMI’s.

I think VM steps generally happen in quick succession, never minutes apart.

Either way, milliseconds elapsed is equally useless - plus what could possibly 
be easier than measuring time yourself?

Pseudo code example, but it will work for near any platform with some numeric 
representation of "time".
...
curtime = now;
elapsed = curtime - prevtime;
prevtime = curtime;
...
print elapsed;
...


What you cannot do, is deduce VM steps like that, so indeed you are better off 
with the progress handler reporting VM steps (which you cannot deduce) rather 
than elapsed time (which you can).

What we REALLY want however, is to know the final intended VM steps so that 
curstep/finalstep will give us some idea of the progress, but it is impossible 
to know (even in principle) before the actual query result have been produced 
at least once.

So at this point we are condemned to reporting to the user something like "Look 
mate, it's still busy, but at least the current VM steps > previous VM steps, 
so it ain't hung. Shall we wait some more, or cancel?" - to which the good user 
hopefully do not cancel prematurely (though that is hard to expect with zero 
confidence in which exact VM milestone constitutes "premature").
Luckily this is less of a problem in custom system code, since you can test 
every query, even on big data. It gets hard when you make a DB manager of sorts 
in which allowing ANY query is a necessity.

Point being: There is no good answer to 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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users