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:[email protected]] Im Auftrag von David Wellman Gesendet: Montag, 18. September 2017 10:53 An: 'SQLite mailing list' <[email protected]> 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:[email protected]] 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:[email protected]] Im Auftrag von David Wellman Gesendet: Montag, 18. September 2017 10:27 An: 'SQLite mailing list' <[email protected]> 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 [email protected] 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: [email protected] 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 [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] 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: [email protected] 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 [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
