You could use either, though I use rollback to make it clear that no changes are intended to be kept.
--- 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 11:12 >To: SQLite mailing list >Subject: Re: [sqlite] [EXTERNAL] get stmt row count > >Thanks very much for that explanation Keith. Why ROLLBACK rather than >END though? > > > >________________________________ >From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on >behalf of Keith Medcalf <kmedc...@dessus.com> >Sent: Saturday, January 20, 2018 5:10:20 PM >To: SQLite mailing list >Subject: Re: [sqlite] [EXTERNAL] get stmt row count > > >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 >_______________________________________________ >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
