Number of rows in answer set

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
25 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Number of rows in answer set

Dave Wellman
Hi,

If I run a SELECT statement that returns an answer set is there an api call
that will tell me "number of rows in answer set" - something like
"sqlite3_row_count"? I have looked at the calls but couldn't find any -
sorry if I've missed it.

 

I know could use a "create temporary table xxx as my-select" and then
"select count(*) from xxx" but was wondering if SQLite holds that
information in an accessible place.

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion

Tel: +44 (0) 118 9740191

Fax: +44 (0) 118 9740192

www:  <http://www.ward-analytics.com> http://www.ward-analytics.com

 

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR

Registered company number: 3917021 Registered in England and Wales.

 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Number of rows in answer set

Hick Gunter
SQLite uses some nifty heuristics to estimate the number of rows it expects to process while formulating a query plan. The only way to come up with the exact number of result rows is to actually run the query. At which point you already know how many rows have been returned.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von David Wellman
Gesendet: Montag, 18. September 2017 10:27
An: 'SQLite mailing list' <[hidden email]>
Betreff: [EXTERNAL] [sqlite] Number of rows in answer set

Hi,

If I run a SELECT statement that returns an answer set is there an api call that will tell me "number of rows in answer set" - something like "sqlite3_row_count"? I have looked at the calls but couldn't find any - sorry if I've missed it.



I know could use a "create temporary table xxx as my-select" and then "select count(*) from xxx" but was wondering if SQLite holds that information in an accessible place.



Cheers,

Dave



Ward Analytics Ltd - information in motion

Tel: +44 (0) 118 9740191

Fax: +44 (0) 118 9740192

www:  <http://www.ward-analytics.com> http://www.ward-analytics.com



Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, United Kingdom, GU1 3SR

Registered company number: 3917021 Registered in England and Wales.



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Number of rows in answer set

Dominique Devienne
On Mon, Sep 18, 2017 at 10:37 AM, Hick Gunter <[hidden email]> wrote:

> SQLite uses some nifty heuristics to estimate the number of rows it
> expects to process while formulating a query plan. [...]
>

Is there any way to get at that estimate? That would be interesting
to pre-size some result buffers ahead of time to avoid reallocs. Even
with all the usual disclaimer and caveats that it's just an estimate if
would be very useful IMHO. Could we have an API for that? --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Number of rows in answer set

Dave Wellman
In reply to this post by Hick Gunter
Hi Hick,

" The only way to come up with the exact number of result rows is to actually run the query." - agreed

" At which point you already know how many rows have been returned."  That's the point, the application doesn't know the exact number although presumably sqlite does.

Let me explain a bit more.

To run a select statement the application code has to:
sqlite3_prepare: parse the sql, make sure it's valid, build the plan (using "nifty heuristics" :-) )
sqlite3_step - this one runs the sql, builds an answer set and then returns the first row
>>>   AT THIS POINT the application doesn't know how many rows are in the answer set only that there is at least 1. <<<
(but presumably sqlite does know how many there are)

AFAIK, if the application wants to know how many rows are in the answer set it has to 'sqlite3_step' to retrieve every row.

This isn't a major issue but I thought I would ask.

Cheers,
Dave

Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, United Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Hick Gunter
Sent: 18 September 2017 09:37
To: 'SQLite mailing list'
Subject: Re: [sqlite] [EXTERNAL] Number of rows in answer set

SQLite uses some nifty heuristics to estimate the number of rows it expects to process while formulating a query plan. The only way to come up with the exact number of result rows is to actually run the query. At which point you already know how many rows have been returned.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von David Wellman
Gesendet: Montag, 18. September 2017 10:27
An: 'SQLite mailing list' <[hidden email]>
Betreff: [EXTERNAL] [sqlite] Number of rows in answer set

Hi,

If I run a SELECT statement that returns an answer set is there an api call that will tell me "number of rows in answer set" - something like "sqlite3_row_count"? I have looked at the calls but couldn't find any - sorry if I've missed it.



I know could use a "create temporary table xxx as my-select" and then "select count(*) from xxx" but was wondering if SQLite holds that information in an accessible place.



Cheers,

Dave



Ward Analytics Ltd - information in motion

Tel: +44 (0) 118 9740191

Fax: +44 (0) 118 9740192

www:  <http://www.ward-analytics.com> http://www.ward-analytics.com



Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, United Kingdom, GU1 3SR

Registered company number: 3917021 Registered in England and Wales.



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Number of rows in answer set

Richard Hipp-3
On 9/18/17, David Wellman <[hidden email]> wrote:
> sqlite3_step - this one runs the sql, builds an answer set and then returns
> the first row

No.  sqlite3_step() does not "build the answer set".  It only begins
computing the answer, stopping at the first row.  The sqlite3_step()
routine has no idea how many more rows will follow at that point.

The only way to find out how many rows there are in the answer set is
to run sqlite3_step() repeatedly and count the number of times it
returns SQLITE_ROW before returning SQLITE_DONE.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Number of rows in answer set

Hick Gunter
In reply to this post by Dave Wellman
You presumtion is wrong. SQLite does not know in advance how many rows are in the result set until it actually goes looking for them. Calling sqlite3_step() instructs SQLite to go look for the next result row and return as soon as it has found it, so that the application can do something useful (like displaying it on screen for the user).

Some (IMHO not so well designed) queries require records to be returned in a specific order that is not faciliated by having suitable indices and so require a sort operation. This means that, technically, the number of result rows of such a query is known before the first row can be returned. But it also means that all the result rows have to be retrieved before the first row can be returned, which is generally longer than an interactive user is willing to wait.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von David Wellman
Gesendet: Montag, 18. September 2017 10:53
An: 'SQLite mailing list' <[hidden email]>
Betreff: Re: [sqlite] [EXTERNAL] Number of rows in answer set

Hi Hick,

" The only way to come up with the exact number of result rows is to actually run the query." - agreed

" At which point you already know how many rows have been returned."  That's the point, the application doesn't know the exact number although presumably sqlite does.

Let me explain a bit more.

To run a select statement the application code has to:
sqlite3_prepare: parse the sql, make sure it's valid, build the plan (using "nifty heuristics" :-) ) sqlite3_step - this one runs the sql, builds an answer set and then returns the first row
>>>   AT THIS POINT the application doesn't know how many rows are in
>>> the answer set only that there is at least 1. <<<
(but presumably sqlite does know how many there are)

AFAIK, if the application wants to know how many rows are in the answer set it has to 'sqlite3_step' to retrieve every row.

This isn't a major issue but I thought I would ask.

Cheers,
Dave

Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, United Kingdom, GU1 3SR Registered company number: 3917021 Registered in England and Wales.


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Hick Gunter
Sent: 18 September 2017 09:37
To: 'SQLite mailing list'
Subject: Re: [sqlite] [EXTERNAL] Number of rows in answer set

SQLite uses some nifty heuristics to estimate the number of rows it expects to process while formulating a query plan. The only way to come up with the exact number of result rows is to actually run the query. At which point you already know how many rows have been returned.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von David Wellman
Gesendet: Montag, 18. September 2017 10:27
An: 'SQLite mailing list' <[hidden email]>
Betreff: [EXTERNAL] [sqlite] Number of rows in answer set

Hi,

If I run a SELECT statement that returns an answer set is there an api call that will tell me "number of rows in answer set" - something like "sqlite3_row_count"? I have looked at the calls but couldn't find any - sorry if I've missed it.



I know could use a "create temporary table xxx as my-select" and then "select count(*) from xxx" but was wondering if SQLite holds that information in an accessible place.



Cheers,

Dave



Ward Analytics Ltd - information in motion

Tel: +44 (0) 118 9740191

Fax: +44 (0) 118 9740192

www:  <http://www.ward-analytics.com> http://www.ward-analytics.com



Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, United Kingdom, GU1 3SR

Registered company number: 3917021 Registered in England and Wales.



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Number of rows in answer set

Rowan Worth-2
In reply to this post by Dave Wellman
On 18 September 2017 at 16:52, David Wellman <[hidden email]>
wrote:

> To run a select statement the application code has to:
> sqlite3_prepare: parse the sql, make sure it's valid, build the plan
> (using "nifty heuristics" :-) )
> sqlite3_step - this one runs the sql, builds an answer set and then
> returns the first row
> >>>   AT THIS POINT the application doesn't know how many rows are in the
> answer set only that there is at least 1. <<<
> (but presumably sqlite does know how many there are)
>

This presumption does not hold, in general. SQLite has no magical
background processing powers - each time you call sqlite3_step it picks up
where it left off and does the work to retrieve the next result row.

A similar common misconception (which I was guilty of) is that SQLite must
know how many rows are in a particular table, so it can optimise "SELECT
count(*) FROM t" to be super fast. It doesn't -- it needs to walk the btree
to count the number of rows.

-Rowan
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Number of rows in answer set

Hick Gunter
In reply to this post by Dominique Devienne
This is documented at https://sqlite.org/eqp.html#the_explain_query_plan_command, complete with the caveat that it is for interactive debugging only and subject to change without notice. Some releases would output the estimated number of rows, and the virtual table interface requires the implementation to return it.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Dominique Devienne
Gesendet: Montag, 18. September 2017 10:45
An: SQLite mailing list <[hidden email]>
Betreff: Re: [sqlite] [EXTERNAL] Number of rows in answer set

On Mon, Sep 18, 2017 at 10:37 AM, Hick Gunter <[hidden email]> wrote:

> SQLite uses some nifty heuristics to estimate the number of rows it
> expects to process while formulating a query plan. [...]
>

Is there any way to get at that estimate? That would be interesting to pre-size some result buffers ahead of time to avoid reallocs. Even with all the usual disclaimer and caveats that it's just an estimate if would be very useful IMHO. Could we have an API for that? --DD _______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Number of rows in answer set

Hick Gunter
In reply to this post by Rowan Worth-2
The reason "select count(*) from t" is super fast is the special "count" opcode that does the "running" in just one go, instead of calling "Column", "AggStep" and "Next" in a loop.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Rowan Worth
Gesendet: Montag, 18. September 2017 11:08
An: SQLite mailing list <[hidden email]>
Betreff: Re: [sqlite] [EXTERNAL] Number of rows in answer set

On 18 September 2017 at 16:52, David Wellman <[hidden email]>
wrote:

> To run a select statement the application code has to:
> sqlite3_prepare: parse the sql, make sure it's valid, build the plan
> (using "nifty heuristics" :-) ) sqlite3_step - this one runs the sql,
> builds an answer set and then returns the first row
> >>>   AT THIS POINT the application doesn't know how many rows are in
> >>> the
> answer set only that there is at least 1. <<< (but presumably sqlite
> does know how many there are)
>

This presumption does not hold, in general. SQLite has no magical background processing powers - each time you call sqlite3_step it picks up where it left off and does the work to retrieve the next result row.

A similar common misconception (which I was guilty of) is that SQLite must know how many rows are in a particular table, so it can optimise "SELECT
count(*) FROM t" to be super fast. It doesn't -- it needs to walk the btree to count the number of rows.

-Rowan
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Number of rows in answer set

Rowan Worth-2
My point was it's not super fast though -- rather than being an O(1) lookup
it uses I/O and compute proportional to the number of rows in the table
(which I first noticed when a SELECT count(*) was taking minutes on a 50GB
database).

I'm not complaining, and I'm aware you can use triggers to implement that
sort of functionality where desired. My point is just that one should be
carefule regarding presumptions as sqlite tends to not do anything more
than what it has been asked to do (which is a good thing!).

-Rowan

On 18 September 2017 at 17:15, Hick Gunter <[hidden email]> wrote:

> The reason "select count(*) from t" is super fast is the special "count"
> opcode that does the "running" in just one go, instead of calling "Column",
> "AggStep" and "Next" in a loop.
>
> -----Ursprüngliche Nachricht-----
> Von: sqlite-users [mailto:[hidden email]]
> Im Auftrag von Rowan Worth
> Gesendet: Montag, 18. September 2017 11:08
> An: SQLite mailing list <[hidden email]>
> Betreff: Re: [sqlite] [EXTERNAL] Number of rows in answer set
>
> On 18 September 2017 at 16:52, David Wellman <[hidden email]>
> wrote:
>
> > To run a select statement the application code has to:
> > sqlite3_prepare: parse the sql, make sure it's valid, build the plan
> > (using "nifty heuristics" :-) ) sqlite3_step - this one runs the sql,
> > builds an answer set and then returns the first row
> > >>>   AT THIS POINT the application doesn't know how many rows are in
> > >>> the
> > answer set only that there is at least 1. <<< (but presumably sqlite
> > does know how many there are)
> >
>
> This presumption does not hold, in general. SQLite has no magical
> background processing powers - each time you call sqlite3_step it picks up
> where it left off and does the work to retrieve the next result row.
>
> A similar common misconception (which I was guilty of) is that SQLite must
> know how many rows are in a particular table, so it can optimise "SELECT
> count(*) FROM t" to be super fast. It doesn't -- it needs to walk the
> btree to count the number of rows.
>
> -Rowan
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___________________________________________
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: [hidden email]
>
> This communication (including any attachments) is intended for the use of
> the intended recipient(s) only and may contain information that is
> confidential, privileged or legally protected. Any unauthorized use or
> dissemination of this communication is strictly prohibited. If you have
> received this communication in error, please immediately notify the sender
> by return e-mail message and delete all copies of the original
> communication. Thank you for your cooperation.
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Number of rows in answer set

Dave Wellman
In reply to this post by Richard Hipp-3
First, thanks to everyone for their input on this. It has resulted in a much bigger discussion than I had assumed would happen...

I obviously have a fundamental misunderstanding of how SQLite processes a request, (I'll read the link that Hick gave me - thanks for that).

So to answer my original question: there isn't an api that gives this value ** because ** SQLite doesn't build the full answer set before returning from that first sqlite3_step function call.

I'll do some reading and come back with any further questions

Cheers,
Dave


Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, United Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Richard Hipp
Sent: 18 September 2017 10:03
To: SQLite mailing list
Subject: Re: [sqlite] [EXTERNAL] Number of rows in answer set

On 9/18/17, David Wellman <[hidden email]> wrote:
> sqlite3_step - this one runs the sql, builds an answer set and then returns
> the first row

No.  sqlite3_step() does not "build the answer set".  It only begins
computing the answer, stopping at the first row.  The sqlite3_step()
routine has no idea how many more rows will follow at that point.

The only way to find out how many rows there are in the answer set is
to run sqlite3_step() repeatedly and count the number of times it
returns SQLITE_ROW before returning SQLITE_DONE.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Number of rows in answer set

Dominique Devienne
On Mon, Sep 18, 2017 at 11:41 AM, David Wellman <[hidden email]
> wrote:

> [...] there isn't an api that gives this value ** because ** SQLite
> doesn't build the full answer set before returning from that first
> sqlite3_step function call.
>

[DD] Well, the answer is more that sometimes it does, and sometimes it
doesn't.
[DD] So since it cannot make that determination for the general case, it
doesn't at all.

[DD] Example cases where it can is when there's a sorting step, e.g. an
order-by,
[DD] and even then only when an index doesn't help getting the rows in the
proper
[DD] order w/o explicit sorting. Specifically for those queries, often
times the full result-set
[DD] must be computed before the first _step() can return. But that will
never be O(1),
[DD] and I think even select count(*) from t is O(N) in SQLite.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Number of rows in answer set

Simon Slavin-3
In reply to this post by Dave Wellman


On 18 Sep 2017, at 10:41am, David Wellman <[hidden email]> wrote:

> So to answer my original question: there isn't an api that gives this value ** because ** SQLite doesn't build the full answer set before returning from that first sqlite3_step function call.

Correct.  SQLite knows how many results it will return only when you tell it to take another sqlite3_step() and it cannot find any more rows which fit your "WHERE" clause.

This is because SQLite was designed to be small and work on tiny embedded computers (i.e. a handheld recording device or nowdays a watch) and storing the complete result-set before returning the first row might take a lot of memory.  It might have thousands of rows in.

If you want to know how many rows will be returned, use

SELECT COUNT(*) WHERE whatever

This returns just one row, and does its calculation without having to store each row that satisfies the WHERE clause.  With an appropriate index it can be surprisingly fast and might let your application work the way you had expected.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Number of rows in answer set

J Decker
What if the select had an ORDER BY ? wouldn't it have to pull the full set
of rows ?

On Mon, Sep 18, 2017 at 5:11 AM, Simon Slavin <[hidden email]> wrote:

>
>
> On 18 Sep 2017, at 10:41am, David Wellman <[hidden email]>
> wrote:
>
> > So to answer my original question: there isn't an api that gives this
> value ** because ** SQLite doesn't build the full answer set before
> returning from that first sqlite3_step function call.
>
> Correct.  SQLite knows how many results it will return only when you tell
> it to take another sqlite3_step() and it cannot find any more rows which
> fit your "WHERE" clause.
>
> This is because SQLite was designed to be small and work on tiny embedded
> computers (i.e. a handheld recording device or nowdays a watch) and storing
> the complete result-set before returning the first row might take a lot of
> memory.  It might have thousands of rows in.
>
> If you want to know how many rows will be returned, use
>
> SELECT COUNT(*) WHERE whatever
>
> This returns just one row, and does its calculation without having to
> store each row that satisfies the WHERE clause.  With an appropriate index
> it can be surprisingly fast and might let your application work the way you
> had expected.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: *** suspected spam or bulk *** Re: [EXTERNAL] Number of rows in answer set

Hick Gunter
This was already addressed. If the ORDER BY clause cannot be fulfilled by virtue of the query plan, the full result set must be retrieved and sorted before even the first row can be returned. This is expensive (memory and/or IO bandwidth) and makes the query seem unresponsive and therefore should be avoided if possible.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von J Decker
Gesendet: Montag, 18. September 2017 14:59
An: SQLite mailing list <[hidden email]>
Betreff: *** suspected spam or bulk *** Re: [sqlite] [EXTERNAL] Number of rows in answer set

What if the select had an ORDER BY ? wouldn't it have to pull the full set of rows ?

On Mon, Sep 18, 2017 at 5:11 AM, Simon Slavin <[hidden email]> wrote:

>
>
> On 18 Sep 2017, at 10:41am, David Wellman
> <[hidden email]>
> wrote:
>
> > So to answer my original question: there isn't an api that gives
> > this
> value ** because ** SQLite doesn't build the full answer set before
> returning from that first sqlite3_step function call.
>
> Correct.  SQLite knows how many results it will return only when you
> tell it to take another sqlite3_step() and it cannot find any more
> rows which fit your "WHERE" clause.
>
> This is because SQLite was designed to be small and work on tiny
> embedded computers (i.e. a handheld recording device or nowdays a
> watch) and storing the complete result-set before returning the first
> row might take a lot of memory.  It might have thousands of rows in.
>
> If you want to know how many rows will be returned, use
>
> SELECT COUNT(*) WHERE whatever
>
> This returns just one row, and does its calculation without having to
> store each row that satisfies the WHERE clause.  With an appropriate
> index it can be surprisingly fast and might let your application work
> the way you had expected.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Number of rows in answer set

Richard Hipp-3
In reply to this post by J Decker
On 9/18/17, J Decker <[hidden email]> wrote:
> What if the select had an ORDER BY ? wouldn't it have to pull the full set
> of rows ?

Not necessarily.  The query planner works hard to try to get the rows
to come out naturally in the correct order, without sorting, as doing
so makes the query run faster.  Sometimes, it is only able to get the
rows to come out partially ordered, with subsets of rows needing
sorting but not the whole set.  In that case too, the final number of
output rows is still unknown until the final sqlite3_step() call.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Number of rows in answer set

Simon Slavin-3
In reply to this post by J Decker


On 18 Sep 2017, at 1:59pm, J Decker <[hidden email]> wrote:

> What if the select had an ORDER BY ? wouldn't it have to pull the full set
> of rows ?

No.  Suppose you declare an index on the same sort order.  Then SQLite could just read the rows starting from the top of the index.

CREATE TABLE directory (firstname TEXT COLLATE NOCASE,
                        secondname TEXT COLLATE NOCASE,
                        phone TEXT);

CREATE INDEX d_sf ON directory (secondname, firstname);

SELECT * FROM directory
        WHERE secondname >= 'd' AND secondname < 'g'
        ORDER BY secondname;

SQLite is extremely clever at trying to find a way to satisfy your query without having to scan the whole table or make up its own index.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Number of rows in answer set

David Raymond
In reply to this post by Dave Wellman
As far as a use case, something like this would be great for use in the CLI for example. Sometimes I'll run a query expecting a dozen results only to get page after page scrolling by. Having something akin to "select changes();" that returns an instant answer of how many were selected would be great, as opposed to re-running the whole query as a "select count(*) from (original query);" Or something like the .changes option that would show a final line of "selected: 123,456 rows"

Side note: The page after page of unexpected results scrolling by is why I'd love a ctrl-c event in the CLI that stops the current query but keeps the connection open. If I've attached a few databases and populated a handful of temp tables then it's a choice of getting some coffee while all the results scroll by, or killing it and spending the same time re-populating the temp tables. There's no way to just stop the query and keep the CLI open. (That I'm aware of anyway)
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Number of rows in answer set

Simon Slavin-3


On 18 Sep 2017, at 4:31pm, David Raymond <[hidden email]> wrote:

> As far as a use case, something like this would be great for use in the CLI for example. Sometimes I'll run a query expecting a dozen results only to get page after page scrolling by. Having something akin to "select changes();" that returns an instant answer of how many were selected would be great, as opposed to re-running the whole query as a "select count(*) from (original query);" Or something like the .changes option that would show a final line of "selected: 123,456 rows"

Sorry, but SQLite wouldn’t have the information needed.  If your control-c interrupted SQLite after it showed the first 20,000 rows, it wouldn’t have got around to processing any more than that.  For all it knows you interrupted it just before it would have run out of data.

Unless your "select changes()" is just an alias for "select count(*) from (original query)" and does exactly the same work, it couldn’t do what you wanted it to do.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Number of rows in answer set

David Raymond
I think I didn't make it clear that I had 2 trains of thought. I'd only expect a total count if I let it run to completion, and not if I killed it. The second part was just a lament that I have to kill the entire session and not just the 1 in-progress query.

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Simon Slavin
Sent: Monday, September 18, 2017 12:02 PM
To: SQLite mailing list
Subject: Re: [sqlite] Number of rows in answer set



On 18 Sep 2017, at 4:31pm, David Raymond <[hidden email]> wrote:

> As far as a use case, something like this would be great for use in the CLI for example. Sometimes I'll run a query expecting a dozen results only to get page after page scrolling by. Having something akin to "select changes();" that returns an instant answer of how many were selected would be great, as opposed to re-running the whole query as a "select count(*) from (original query);" Or something like the .changes option that would show a final line of "selected: 123,456 rows"

Sorry, but SQLite wouldn’t have the information needed.  If your control-c interrupted SQLite after it showed the first 20,000 rows, it wouldn’t have got around to processing any more than that.  For all it knows you interrupted it just before it would have run out of data.

Unless your "select changes()" is just an alias for "select count(*) from (original query)" and does exactly the same work, it couldn’t do what you wanted it to do.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12