[sqlite] get stmt row count

[x]

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?

[Hick Gunter]

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-***@mailinglists.sqlite.org] Im Auftrag von x
Gesendet: Freitag, 19. Jänner 2018 10:43
An: sqlite-***@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-***@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) [phone] - 0

May be privileged. May be confidential. Please delete if not the addressee.

[x]

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-***@mailinglists.sqlite.org> on behalf of Hick Gunter <***@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-***@mailinglists.sqlite.org] Im Auftrag von x
Gesendet: Freitag, 19. Jänner 2018 10:43
An: sqlite-***@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-***@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) [phone] - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
sqlite-***@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Simon Slavin]

On 19 Jan 2018, at 12:06pm, x <***@hotmail.com> wrote:

> Int Count=0;
>
> while (sqlite3_step(...)) Count++;

That’s a slow way to count rows. Do it in SQL:

SELECT COUNT(*) FROM MyTable WHERE b > 11;

You get just one row back which makes everything far faster.

Simon.

[R Smith]

On 2018/01/19 2:16 PM, Simon Slavin wrote:
>
> On 19 Jan 2018, at 12:06pm, x <***@hotmail.com> wrote:
>
>> Int Count=0;
>>
>> while (sqlite3_step(...)) Count++;
> That’s a slow way to count rows. Do it in SQL:
>
> SELECT COUNT(*) FROM MyTable WHERE b > 11;
>
> You get just one row back which makes everything far faster.

I think the OP is not interested in the actual row count for that
specific query, rather he is interested in a mechanism by which he can
receive both the rows and their count (and possibly numeric index) in
the space of a single query, hence the counter while looping the cursor
- which is in fact the very best way to do it.

[Keith Medcalf]

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-
>***@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-***@mailinglists.sqlite.org> on
>behalf of Hick Gunter <***@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-
>***@mailinglists.sqlite.org] Im Auftrag von x
>Gesendet: Freitag, 19. Jänner 2018 10:43
>An: sqlite-***@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-***@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) [phone] - 0
>
>May be privileged. May be confidential. Please delete if not the
>addressee.
>_______________________________________________
>sqlite-users mailing list
>sqlite-***@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>_______________________________________________
>sqlite-users mailing list
>sqlite-***@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[x]

Keith & Simon, are you not both missing the point? I want the rows and the count but without having to run two queries.

I tried the following (let stmt1 represent the original query and stmt2 the count(*) version of that query).

stmt1 took 6+ secs for the first step.
stmt2 took 6+ secs to get the count.

Counting using

int Count=1;
while (sqlite3_step(stmt1)) Count++;

after the first step took under 2 secs BUT in order to then get the rows I’d have to reset stmt1 which would result in the pre-first step code being run again at the first step call (another 6+ secs down the drain).

I’m thinking (but I’m by no means sure) that sqlite’s pre-first step code should be able to calculate the row count as it went along at virtually no cost. I realise it would only be a solution for queries sorted by non-indexed columns (i.e. where sqlite’s pre-first step code had to consider all result set rows).

[David Raymond]

Maybe use a temp table as another possible solution?

create temp table queryResults (id int);
insert into queryResults select rowid from Tbl where Col > ?1 order by Col;
select count(*) from queryResults;
select id from queryResults order by rowid;
drop table queryResults;



The whole issue of CTE's being calculated more than once is an issue, otherwise I would say...

with queryResults as (select rowid from Tbl where Col > ?1 order by Col)
select count(*) from queryResults union all select rowid from queryResults;

Then the first record would be the count, followed by all the rowids. But it looks like it's running the CTE twice, which defeats the advantage.


-----Original Message-----
From: sqlite-users [mailto:sqlite-users-***@mailinglists.sqlite.org] On Behalf Of x
Sent: Friday, January 19, 2018 8:50 AM
To: SQLite mailing list
Subject: Re: [sqlite] [EXTERNAL] get stmt row count

Keith & Simon, are you not both missing the point? I want the rows and the count but without having to run two queries.

I tried the following (let stmt1 represent the original query and stmt2 the count(*) version of that query).

stmt1 took 6+ secs for the first step.
stmt2 took 6+ secs to get the count.

Counting using

int Count=1;
while (sqlite3_step(stmt1)) Count++;

after the first step took under 2 secs BUT in order to then get the rows I’d have to reset stmt1 which would result in the pre-first step code being run again at the first step call (another 6+ secs down the drain).

I’m thinking (but I’m by no means sure) that sqlite’s pre-first step code should be able to calculate the row count as it went along at virtually no cost. I realise it would only be a solution for queries sorted by non-indexed columns (i.e. where sqlite’s pre-first step code had to consider all result set rows).





_______________________________________________
sqlite-users mailing list
sqlite-***@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[x]

>(and possibly numeric index)



You’re on my wavelength Ryan as, I think, is David.



Incidentally, the stmt1 query was suited to the harvesting of base table RowIDs that I’ve been banging on about in other threads. For the query in question I replaced the column list with BaseTbl.RowID and ran the resultant query, It took just over 2 secs to store all the sorted base table RowIDs in a vector, the size of which yielded the row count.



Compare that with the 12+ secs it took using the standard methods. Certainly sqlite was storing more of the query’s column data but It took only slightly longer using the RowID method to store all the results in a 2-dim vector of strings than it did to step through stmt1 and do the same.





________________________________
From: sqlite-users <sqlite-users-***@mailinglists.sqlite.org> on behalf of David Raymond <***@tomtom.com>
Sent: Friday, January 19, 2018 3:22:56 PM
To: SQLite mailing list
Subject: Re: [sqlite] [EXTERNAL] get stmt row count

Maybe use a temp table as another possible solution?

create temp table queryResults (id int);
insert into queryResults select rowid from Tbl where Col > ?1 order by Col;
select count(*) from queryResults;
select id from queryResults order by rowid;
drop table queryResults;



The whole issue of CTE's being calculated more than once is an issue, otherwise I would say...

with queryResults as (select rowid from Tbl where Col > ?1 order by Col)
select count(*) from queryResults union all select rowid from queryResults;

Then the first record would be the count, followed by all the rowids. But it looks like it's running the CTE twice, which defeats the advantage.


-----Original Message-----
From: sqlite-users [mailto:sqlite-users-***@mailinglists.sqlite.org] On Behalf Of x
Sent: Friday, January 19, 2018 8:50 AM
To: SQLite mailing list
Subject: Re: [sqlite] [EXTERNAL] get stmt row count

Keith & Simon, are you not both missing the point? I want the rows and the count but without having to run two queries.

I tried the following (let stmt1 represent the original query and stmt2 the count(*) version of that query).

stmt1 took 6+ secs for the first step.
stmt2 took 6+ secs to get the count.

Counting using

int Count=1;
while (sqlite3_step(stmt1)) Count++;

after the first step took under 2 secs BUT in order to then get the rows I’d have to reset stmt1 which would result in the pre-first step code being run again at the first step call (another 6+ secs down the drain).

I’m thinking (but I’m by no means sure) that sqlite’s pre-first step code should be able to calculate the row count as it went along at virtually no cost. I realise it would only be a solution for queries sorted by non-indexed columns (i.e. where sqlite’s pre-first step code had to consider all result set rows).





_______________________________________________
sqlite-users mailing list
sqlite-***@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-***@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[x]

Not unusually for me I may have caused confusion in my earlier posts. In my opening post I mentioned the query



SELECT RowID from Tbl where Col > ?1 order by Col; // there’s no index on Col



However, the “moderately complex query” I mentioned in my reply to Gunter is of the following form



select ID1, ID2, ID3, ID4, IntColA, IntColB, TextCol2, TextCol3, TextCol4 from

BaseTbl

left join Tbl2 using (ID2)

left join Tbl3 using (ID3)

left join Tbl4 using (ID4)

order by Tbl2.NonIndexedTextCol;



and this is the query I’ve been referring to (and quoting timings for) since. The query



select BaseTbl.RowID from

BaseTbl

left join Tbl2 using (ID2)

left join Tbl3 using (ID3)

left join Tbl4 using (ID4)

order by Tbl2.NonIndexedTextCol;



is shortened to



select BaseTbl.RowID from

BaseTbl

left join Tbl2 using (ID2)

order by Tbl2.NonIndexedTextCol;



by the query planner. The query is therefore slightly biased towards my base table RowIDs modus operandi.









________________________________
From: sqlite-users <sqlite-users-***@mailinglists.sqlite.org> on behalf of x <***@hotmail.com>
Sent: Friday, January 19, 2018 4:09:49 PM
To: SQLite mailing list
Subject: Re: [sqlite] [EXTERNAL] get stmt row count

>(and possibly numeric index)



You’re on my wavelength Ryan as, I think, is David.



Incidentally, the stmt1 query was suited to the harvesting of base table RowIDs that I’ve been banging on about in other threads. For the query in question I replaced the column list with BaseTbl.RowID and ran the resultant query, It took just over 2 secs to store all the sorted base table RowIDs in a vector, the size of which yielded the row count.



Compare that with the 12+ secs it took using the standard methods. Certainly sqlite was storing more of the query’s column data but It took only slightly longer using the RowID method to store all the results in a 2-dim vector of strings than it did to step through stmt1 and do the same.





________________________________
From: sqlite-users <sqlite-users-***@mailinglists.sqlite.org> on behalf of David Raymond <***@tomtom.com>
Sent: Friday, January 19, 2018 3:22:56 PM
To: SQLite mailing list
Subject: Re: [sqlite] [EXTERNAL] get stmt row count

Maybe use a temp table as another possible solution?

create temp table queryResults (id int);
insert into queryResults select rowid from Tbl where Col > ?1 order by Col;
select count(*) from queryResults;
select id from queryResults order by rowid;
drop table queryResults;



The whole issue of CTE's being calculated more than once is an issue, otherwise I would say...

with queryResults as (select rowid from Tbl where Col > ?1 order by Col)
select count(*) from queryResults union all select rowid from queryResults;

Then the first record would be the count, followed by all the rowids. But it looks like it's running the CTE twice, which defeats the advantage.


-----Original Message-----
From: sqlite-users [mailto:sqlite-users-***@mailinglists.sqlite.org] On Behalf Of x
Sent: Friday, January 19, 2018 8:50 AM
To: SQLite mailing list
Subject: Re: [sqlite] [EXTERNAL] get stmt row count

Keith & Simon, are you not both missing the point? I want the rows and the count but without having to run two queries.

I tried the following (let stmt1 represent the original query and stmt2 the count(*) version of that query).

stmt1 took 6+ secs for the first step.
stmt2 took 6+ secs to get the count.

Counting using

int Count=1;
while (sqlite3_step(stmt1)) Count++;

after the first step took under 2 secs BUT in order to then get the rows I’d have to reset stmt1 which would result in the pre-first step code being run again at the first step call (another 6+ secs down the drain).

I’m thinking (but I’m by no means sure) that sqlite’s pre-first step code should be able to calculate the row count as it went along at virtually no cost. I realise it would only be a solution for queries sorted by non-indexed columns (i.e. where sqlite’s pre-first step code had to consider all result set rows).





_______________________________________________
sqlite-users mailing list
sqlite-***@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-***@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-***@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Simon Slavin]

On 19 Jan 2018, at 3:22pm, David Raymond <***@tomtom.com> wrote:

> Keith & Simon, are you not both missing the point? I want the rows and the count but without having to run two queries.

Yeah, I didn’t see that. Sorry.

Simon.

[x]

>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-***@mailinglists.sqlite.org> on behalf of Keith Medcalf <***@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-
>***@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-***@mailinglists.sqlite.org> on
>behalf of Hick Gunter <***@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-
>***@mailinglists.sqlite.org] Im Auftrag von x
>Gesendet: Freitag, 19. Jänner 2018 10:43
>An: sqlite-***@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-***@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) [phone] - 0
>
>May be privileged. May be confidential. Please delete if not the
>addressee.
>_______________________________________________
>sqlite-users mailing list
>sqlite-***@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>_______________________________________________
>sqlite-users mailing list
>sqlite-***@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
sqlite-***@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Clemens Ladisch]

x wrote:
> is there an easy way of checking if the db file has been modified
> since the RowIDs query has been run?

The easiest way is to keep the transaction open, then you know that there
have not been any modifications, as far as you can see. :o)

> 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.

<http://www.sqlite.org/fileformat2.html#file_change_counter>
<http://www.sqlite.org/pragma.html#pragma_data_version>
| The integer values returned by two invocations of "PRAGMA data_version"
| from the same connection will be different if changes were committed to
| the database by any other connection in the interim.


Regards,
Clemens

[x]

Thanks Clemens.



________________________________
From: sqlite-users <sqlite-users-***@mailinglists.sqlite.org> on behalf of Clemens Ladisch <***@ladisch.de>
Sent: Saturday, January 20, 2018 10:39:15 AM
To: sqlite-***@mailinglists.sqlite.org
Subject: Re: [sqlite] [EXTERNAL] get stmt row count

x wrote:
> is there an easy way of checking if the db file has been modified
> since the RowIDs query has been run?

The easiest way is to keep the transaction open, then you know that there
have not been any modifications, as far as you can see. :o)

> 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.

<http://www.sqlite.org/fileformat2.html#file_change_counter>
<http://www.sqlite.org/pragma.html#pragma_data_version>
| The integer values returned by two invocations of "PRAGMA data_version"
| from the same connection will be different if changes were committed to
| the database by any other connection in the interim.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
sqlite-***@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Keith Medcalf]

The trouble is that it is only "by happenstance" that you can obtain the count() after a single step. There may be cases where you cannot, or where the rows are ordered by subsorted blocks, or by other mythical magical creatures that return some "random" number of computer results per step.

Executing a query returns one row for each step. How many rows are availalbe "in the background" in one call to step is a detail of magic upon which you cannot rely.


---
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-
>***@mailinglists.sqlite.org] On Behalf Of x
>Sent: Friday, 19 January, 2018 08:50
>To: SQLite mailing list
>Subject: Re: [sqlite] [EXTERNAL] get stmt row count
>
>Keith & Simon, are you not both missing the point? I want the rows
>and the count but without having to run two queries.
>
>I tried the following (let stmt1 represent the original query and
>stmt2 the count(*) version of that query).
>
>stmt1 took 6+ secs for the first step.
>stmt2 took 6+ secs to get the count.
>
>Counting using
>
>int Count=1;
>while (sqlite3_step(stmt1)) Count++;
>
>after the first step took under 2 secs BUT in order to then get the
>rows I’d have to reset stmt1 which would result in the pre-first step
>code being run again at the first step call (another 6+ secs down the
>drain).
>
>I’m thinking (but I’m by no means sure) that sqlite’s pre-first step
>code should be able to calculate the row count as it went along at
>virtually no cost. I realise it would only be a solution for queries
>sorted by non-indexed columns (i.e. where sqlite’s pre-first step
>code had to consider all result set rows).
>
>
>
>
>
>_______________________________________________
>sqlite-users mailing list
>sqlite-***@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Keith Medcalf]

Thus you learned from the experience that the efficiency of your application programming lanuguage has far more effect than anything you might do fiddlefaddling with the query's.


---
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-
>***@mailinglists.sqlite.org] On Behalf Of x
>Sent: Friday, 19 January, 2018 11:10
>To: SQLite mailing list
>Subject: Re: [sqlite] [EXTERNAL] get stmt row count
>
>>(and possibly numeric index)
>
>
>
>You’re on my wavelength Ryan as, I think, is David.
>
>
>
>Incidentally, the stmt1 query was suited to the harvesting of base
>table RowIDs that I’ve been banging on about in other threads. For
>the query in question I replaced the column list with BaseTbl.RowID
>and ran the resultant query, It took just over 2 secs to store all
>the sorted base table RowIDs in a vector, the size of which yielded
>the row count.
>
>
>
>Compare that with the 12+ secs it took using the standard methods.
>Certainly sqlite was storing more of the query’s column data but It
>took only slightly longer using the RowID method to store all the
>results in a 2-dim vector of strings than it did to step through
>stmt1 and do the same.
>
>
>
>
>
>________________________________
>From: sqlite-users <sqlite-users-***@mailinglists.sqlite.org> on
>behalf of David Raymond <***@tomtom.com>
>Sent: Friday, January 19, 2018 3:22:56 PM
>To: SQLite mailing list
>Subject: Re: [sqlite] [EXTERNAL] get stmt row count
>
>Maybe use a temp table as another possible solution?
>
>create temp table queryResults (id int);
>insert into queryResults select rowid from Tbl where Col > ?1 order
>by Col;
>select count(*) from queryResults;
>select id from queryResults order by rowid;
>drop table queryResults;
>
>
>
>The whole issue of CTE's being calculated more than once is an issue,
>otherwise I would say...
>
>with queryResults as (select rowid from Tbl where Col > ?1 order by
>Col)
>select count(*) from queryResults union all select rowid from
>queryResults;
>
>Then the first record would be the count, followed by all the rowids.
>But it looks like it's running the CTE twice, which defeats the
>advantage.
>
>
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>***@mailinglists.sqlite.org] On Behalf Of x
>Sent: Friday, January 19, 2018 8:50 AM
>To: SQLite mailing list
>Subject: Re: [sqlite] [EXTERNAL] get stmt row count
>
>Keith & Simon, are you not both missing the point? I want the rows
>and the count but without having to run two queries.
>
>I tried the following (let stmt1 represent the original query and
>stmt2 the count(*) version of that query).
>
>stmt1 took 6+ secs for the first step.
>stmt2 took 6+ secs to get the count.
>
>Counting using
>
>int Count=1;
>while (sqlite3_step(stmt1)) Count++;
>
>after the first step took under 2 secs BUT in order to then get the
>rows I’d have to reset stmt1 which would result in the pre-first step
>code being run again at the first step call (another 6+ secs down the
>drain).
>
>I’m thinking (but I’m by no means sure) that sqlite’s pre-first step
>code should be able to calculate the row count as it went along at
>virtually no cost. I realise it would only be a solution for queries
>sorted by non-indexed columns (i.e. where sqlite’s pre-first step
>code had to consider all result set rows).
>
>
>
>
>
>_______________________________________________
>sqlite-users mailing list
>sqlite-***@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>_______________________________________________
>sqlite-users mailing list
>sqlite-***@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>_______________________________________________
>sqlite-users mailing list
>sqlite-***@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Keith Medcalf]

Does not the statement of the problem define the answer?


---
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-
>***@mailinglists.sqlite.org] On Behalf Of Simon Slavin
>Sent: Friday, 19 January, 2018 11:27
>To: SQLite mailing list
>Subject: Re: [sqlite] [EXTERNAL] get stmt row count
>
>
>
>On 19 Jan 2018, at 3:22pm, David Raymond <***@tomtom.com>
>wrote:
>
>> Keith & Simon, are you not both missing the point? I want the rows
>and the count but without having to run two queries.
>
>Yeah, I didn’t see that. Sorry.
>
>Simon.
>_______________________________________________
>sqlite-users mailing list
>sqlite-***@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Keith Medcalf]

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-
>***@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-***@mailinglists.sqlite.org> on
>behalf of Keith Medcalf <***@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-
>>***@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-***@mailinglists.sqlite.org> on
>>behalf of Hick Gunter <***@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-
>>***@mailinglists.sqlite.org] Im Auftrag von x
>>Gesendet: Freitag, 19. Jänner 2018 10:43
>>An: sqlite-***@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-***@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) [phone] - 0
>>
>>May be privileged. May be confidential. Please delete if not the
>>addressee.
>>_______________________________________________
>>sqlite-users mailing list
>>sqlite-***@mailinglists.sqlite.org
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>_______________________________________________
>>sqlite-users mailing list
>>sqlite-***@mailinglists.sqlite.org
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>_______________________________________________
>sqlite-users mailing list
>sqlite-***@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>_______________________________________________
>sqlite-users mailing list
>sqlite-***@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[x]

Thanks very much for that explanation Keith. Why ROLLBACK rather than END though?



________________________________
From: sqlite-users <sqlite-users-***@mailinglists.sqlite.org> on behalf of Keith Medcalf <***@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-
>***@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-***@mailinglists.sqlite.org> on
>behalf of Keith Medcalf <***@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-
>>***@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-***@mailinglists.sqlite.org> on
>>behalf of Hick Gunter <***@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-
>>***@mailinglists.sqlite.org] Im Auftrag von x
>>Gesendet: Freitag, 19. Jänner 2018 10:43
>>An: sqlite-***@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-***@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) [phone] - 0
>>
>>May be privileged. May be confidential. Please delete if not the
>>addressee.
>>_______________________________________________
>>sqlite-users mailing list
>>sqlite-***@mailinglists.sqlite.org
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>_______________________________________________
>>sqlite-users mailing list
>>sqlite-***@mailinglists.sqlite.org
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>_______________________________________________
>sqlite-users mailing list
>sqlite-***@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>_______________________________________________
>sqlite-users mailing list
>sqlite-***@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Keith Medcalf]

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-
>***@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-***@mailinglists.sqlite.org> on
>behalf of Keith Medcalf <***@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-
>>***@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-***@mailinglists.sqlite.org> on
>>behalf of Keith Medcalf <***@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-
>>>***@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-***@mailinglists.sqlite.org>
>on
>>>behalf of Hick Gunter <***@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-
>>>***@mailinglists.sqlite.org] Im Auftrag von x
>>>Gesendet: Freitag, 19. Jänner 2018 10:43
>>>An: sqlite-***@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-***@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) [phone] - 0
>>>
>>>May be privileged. May be confidential. Please delete if not the
>>>addressee.
>>>_______________________________________________
>>>sqlite-users mailing list
>>>sqlite-***@mailinglists.sqlite.org
>>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>>_______________________________________________
>>>sqlite-users mailing list
>>>sqlite-***@mailinglists.sqlite.org
>>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>>
>>
>>_______________________________________________
>>sqlite-users mailing list
>>sqlite-***@mailinglists.sqlite.org
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>_______________________________________________
>>sqlite-users mailing list
>>sqlite-***@mailinglists.sqlite.org
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>_______________________________________________
>sqlite-users mailing list
>sqlite-***@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>_______________________________________________
>sqlite-users mailing list
>sqlite-***@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users