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- >boun...@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-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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
