On Monday, 18 September, 2017 02:53, David Wellman <dwell...@ward-analytics.com> wrote:
>" 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 This is where the error occurs in your assumptions. Only after the query is run to completion is the number of result rows known. sqlite3_step does not "build the result set", it returns the first result row. If you want to know how many rows a query will return, you run it returning count(*). It will run the entire query and return the count of the result rows. Of course, this number may be different next time you run the query *unless* you run the two queries inside the same transaction (which can be a read transaction if in WAL journal mode, otherwise it must be an update (BEGIN IMMEDIATE) transaction). >>>> 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. or issue the query returning count(*) that is if the query is SELECT <a bunch of data columns> FROM <a bunch of tables> WHERE <a bunch of conditions> you run the query(s) BEGIN [IMMEDIATE]; SELECT count(*) FROM <the same bunch of tables> WHERE <the same bunch of conditions>; SELECT <a bunch of data columns> FROM <a bunch of tables> WHERE <a bunch of conditions>; ROLLBACK; The first will return the count of the number of rows in the result set, and the second query will return the actual results. The bare BEGIN (if in WAL journal mode) or BEGIN IMMEDIATE (if in any journal mode but WAL) is to ensure there are no updates affecting row visibility between the two queries. Alternatively, if the query is "expensive" to compute (for whatever your definition of expensive), you could to the following to limit its computation to once: BEGIN IMMEDIATE; CREATE TEMPORARY TABLE mytemptable AS SELECT <a bunch of columns> INTO temp.mytemptable FROM <a bunch of tables> WHERE <a bunch of conditions>; SELECT count(*) FROM temp.mytemptable; SELECT <the columns by name or use *> FROM temp.mytemptable; DROP TABLE temp.mytemptable; COMMIT or ROLLBACK; This way, however, since you are updating the database you need to be inside an update transaction and therefore you will be locking other writers out of the database ... >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:sqlite-users- >boun...@mailinglists.sqlite.org] 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:sqlite-users- >boun...@mailinglists.sqlite.org] Im Auftrag von David Wellman >Gesendet: Montag, 18. September 2017 10:27 >An: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org> >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 >sqlite-users@mailinglists.sqlite.org >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: h...@scigames.at > >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 >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
