>(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-boun...@mailinglists.sqlite.org> on behalf of David Raymond <david.raym...@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-boun...@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-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
