Then you should have asked for the count(*) which would have returned the count rather than you having to count the rows as they are returned.
Make up your mind what you want -- and ye shall get that for which you asked -- the rows or the count of the rows? Getting both requires TWO queries (and may not be the same from one nanosecond to the next). Or you can request the rows AND count them if you need both the rows and the count of them. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of x >Sent: Friday, 19 January, 2018 07:07 >To: SQLite mailing list >Subject: Re: [sqlite] [EXTERNAL] get stmt row count > >Gunter, I just ran a moderately complex query sorted on a non-indexed >column which returned 2.4 million rows. As you say, after the first >step practically all of the work is done yet it still took almost 2 >secs to run > > > >Int Count=0; > >while (sqlite3_step(...)) Count++; > > > >that’s on a laptop with an SSD and 16 GB RAM. It’s not exactly >insignificant if you’re looking to set up a grid scroll bar. > > > >Tom > > > >________________________________ >From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on >behalf of Hick Gunter <h...@scigames.at> >Sent: Friday, January 19, 2018 10:27:30 AM >To: 'SQLite mailing list' >Subject: Re: [sqlite] [EXTERNAL] get stmt row count > >No. The only viable query plan is scan and sort (see "explain query >plan" output). The sqlite3_prepare() family of calls creates the SQL >program (see "explain" output) and sets ist initial state. If the >value you require is not null, you must call one of the >sqlite3_bind() functions. This sets the value of the variable (line >2). Calling sqlite3_step() on the statement causes the program to run >up to either the ResultRow or the Halt statement. By the time the >firs call to sqlite3_step() returns, all the table rows have been >read, the values sorted and the first output row produced. This is >practically all of the work. Stopping now saves nothing. > >There is no count of "records selected from base table" or "records >inserted into sorter". Keeping such scores would add work to be done, >with no benefit except perhaps satisfying your intellectual >curiosity. > >asql> create temp table test (rowid integer primary key, value >integer); >asql> .explain >asql> explain query plan select rowid from test where value > ?1 >order by value; >sele order from deta >---- ------------- ---- ---- >0 0 0 SCAN TABLE test (~333333 rows) >0 0 0 USE TEMP B-TREE FOR ORDER BY >asql> explain select rowid from test where value > ?1 order by value; >addr opcode p1 p2 p3 p4 p5 comment >---- ------------- ---- ---- ---- ------------- -- ----------- >-- >0 Trace 0 0 0 00 NULL >1 SorterOpen 1 3 0 Keyinfo(1,BINARY) 00 NULL >2 Variable 1 1 0 ?1 00 NULL >3 Goto 0 27 0 00 NULL >4 OpenRead 0 2 1 2 00 test >5 Rewind 0 16 0 00 NULL >6 Column 0 1 2 00 test.value >7 Le 1 15 2 collseq(BINARY) 6c NULL >8 Rowid 0 4 0 00 NULL >9 MakeRecord 4 1 2 00 NULL >10 Column 0 1 5 00 test.value >11 Sequence 1 6 0 00 NULL >12 Move 2 7 1 00 NULL >13 MakeRecord 5 3 3 00 NULL >14 SorterInsert 1 3 0 00 NULL >15 Next 0 6 0 01 NULL >16 Close 0 0 0 00 NULL >17 OpenPseudo 2 2 1 00 NULL >18 OpenPseudo 3 8 3 00 NULL >19 SorterSort 1 25 0 00 NULL >20 SorterData 1 8 0 00 NULL >21 Column 3 2 2 20 NULL >22 Column 2 0 4 20 NULL >23 ResultRow 4 1 0 00 NULL >24 SorterNext 1 20 0 00 NULL >25 Close 2 0 0 00 NULL >26 Halt 0 0 0 00 NULL >27 Transaction 1 0 0 00 NULL >28 VerifyCookie 1 1 0 00 NULL >29 TableLock 1 2 0 test 00 NULL >30 Goto 0 4 0 00 NULL > >-----Ursprüngliche Nachricht----- >Von: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] Im Auftrag von x >Gesendet: Freitag, 19. Jänner 2018 10:43 >An: sqlite-users@mailinglists.sqlite.org >Betreff: [EXTERNAL] [sqlite] get stmt row count > >I’ve read up on the subject and realise that in most cases there’s no >way of getting the row count without stepping through all the rows. >That said, is there not times when sqlite could help to avoid this? >Take the following simple query > >SELECT RowID from Tbl where Col > ?1 order by Col; // there’s no >index on Col > >I’m assuming sqlite can’t complete the first step without first >obtaining and sorting the result set and that, in doing so, it >already knows the row count. Is there any way of obtaining this or is >there a case for a function > >sqlite3_row_count(stmt) > >which returns -1 when it can’t be determined but otherwise completes >the pre first step code and returns the row count? > >_______________________________________________ >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 >_______________________________________________ >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
