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
