Clemens is correct, the best way is to keep the transaction open (perhaps if necessary dedicate a connection to this data).
In WAL the open transaction will continue to see the "same data snapshot" independent of other read/write taking place on other connections (which will not be blocked). While holding a transaction active when in another (non-WAL) journal mode will have the same effect, it will lock out all database writers for the duration of the transactions (which achieves the same stability but by prohibiting changes rather than by repeatable-read isolation). BEGIN; SELECT RowID FROM ... WHERE ... ; ... SELECT ...data... using RowID from above ... ROLLBACK; The view of the data will be stable (repeatable read isolation) between the BEGIN/END transaction. Other connections may read/update the database and will see the "current" database (or the applicable snapshot in effect when they started their transactions). This is how other "heavyweight" database engines/cli's implement scrollable cursors. They just wrote the complicated code for you (that is why they are "heavy" -- as in 10,000 tonnes -- rather than "lite" as in 400 milligrams -- SQLite being in the "Lite" category.) SQLite does everything you need to be able to implement all the heavyweight featuritis you need -- it is just that YOU have to do it, it is not done for you (so it works the way you want it to work, at the speed you want it to work at, not in the manner and speed that someone else thought was splendid for 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: Saturday, 20 January, 2018 02:42 >To: SQLite mailing list >Subject: Re: [sqlite] [EXTERNAL] get stmt row count > >>Getting both requires TWO queries (and may not be the same from one >nanosecond to the next). > > > >I hadn’t given that much thought until your post Keith but I was >thinking last night that my vector of RowIDs might no longer be valid >when I use them to retrieve a grid of data. It’s easily dealt with in >the app I’m working on but, for the general case, is there an easy >way of checking if the db file has been modified since the RowIDs >query has been run? I’m sure I read something about a db related >integer that was incremented after every update or vacuum but can’t >find the post. > > > > > >________________________________ >From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on >behalf of Keith Medcalf <kmedc...@dessus.com> >Sent: Friday, January 19, 2018 12:12:15 PM >To: SQLite mailing list >Subject: Re: [sqlite] [EXTERNAL] get stmt row count > > >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 >_______________________________________________ >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
