Re: [sqlite] "cursored" queries and total rows
One more thing: On Sun, Jun 3, 2018 at 2:16 PM R Smith wrote: > > - If it is an Index, /and/ the Key repeats magnificently much (Imagine > adding an "Age" column to a phone-book and then filtering on Age, there > will be thousands of people who are all 34, for instance) then you are > better off extracting the set of records to a TEMP table and then > paginating through the temp table's complete dataset and COUNT(*) its > rows. This will be extremely fast, especially if the DB is otherwise > quite hefty, and will allow using the new table's rowids (invisible in > the query) as pagination pegs. Be sure to use a memory-oriented journal > mode and cache settings for this, or if not possible, perhaps even a > second attached in-memory or memory-oriented DB. I'd be worried about memory usage, and also having to keep the temp table between queries until it's no longer needed… I can't predict when the client is no longer interested in the query. If the query would match 100k records, won't they all be copied? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "cursored" queries and total rows
Argh, I just discovered that gmail was sending all sqlite mails to spam :( Thank you all for the answers! Clemens, R Smith: Sorry, I should have given a full production query. Here's an example: SELECT "id" AS _3,"json" AS _5,ifNull(json_extract(json, '$.name'),'') AS _0 FROM "agents" tbl WHERE(ifNull(json_extract(json, '$.name'),'') LIKE '%a%') ORDER BY _0,_3 LIMIT 10 SELECT COUNT(*) as t from "agents" tbl WHERE(ifNull(json_extract(json, '$.name'),'') LIKE '%a%') And when I then want to continue from the agent Al with id 123: SELECT "id" AS _3,"json" AS _5,ifNull(json_extract(json, '$.name'),'') AS _0 FROM "agents" tbl WHERE(ifNull(json_extract(json, '$.name'),'') LIKE '%a%')AND((_0>='Al' AND (_0!='Al' OR _3>123))) ORDER BY _0,_3 LIMIT 10 SELECT COUNT(*) as t from "agents" tbl WHERE(ifNull(json_extract(json, '$.name'),'') LIKE '%a%') heribert: I prefer using queries like these because if you use offset, the engine needs to skip rows one by one, and if you instead keep the order-by values the engine can use the index to find the next set of results. For small databases it doesn't matter of course. On Mon, Jun 4, 2018 at 6:00 PM heribert wrote: > I'm using also paged queries. I'm adding an OFFSET to the select-limit > query. > Works for me. > > Am 03.06.2018 um 14:16 schrieb R Smith: > > > > On 2018/06/03 1:13 PM, Wout Mertens wrote: > >> Hi all, > >> > >> To do paged queries on a query like > >> > >> SELECT colVal FROM t WHERE b=? LIMIT 10 > >> > >> I keep track of column values and construct a query that will get the > >> next > >> item in a query by augmenting the query like > >> > >> SELECT colVal FROM t WHERE b=? AND colVal > ? LIMIT 10 > >> > >> > >> To know how many rows there are in the query, I do > >> > >> SELECT COUNT(*) FROM t WHERE b=? > >> > >> > >> Are there any efficiency tricks here? Is it better to run the count > >> before > >> the query or vice versa? > > > > I assume b is not the Primary Key, (since the use case suggests that > > it repeats), but hopefully it is at least an Index. > > > > - If it is not an Index, the entire system is inefficient. > > > > - If it is an Index, then it doesn't matter which comes first[*], the > > queries are dissimilar enough to not have differing caching advantages > > based on order of execution, except... > > > > - If it is an Index, /and/ the Key repeats magnificently much (Imagine > > adding an "Age" column to a phone-book and then filtering on Age, > > there will be thousands of people who are all 34, for instance) then > > you are better off extracting the set of records to a TEMP table and > > then paginating through the temp table's complete dataset and COUNT(*) > > its rows. This will be extremely fast, especially if the DB is > > otherwise quite hefty, and will allow using the new table's rowids > > (invisible in the query) as pagination pegs. Be sure to use a > > memory-oriented journal mode and cache settings for this, or if not > > possible, perhaps even a second attached in-memory or memory-oriented DB. > > > > [*] - The above assumes there are no FTS tables (or other special > > virtual tables) involved, nor any computed Keys - all of which may > > need more specific considerations. > > > > > > Cheers, > > Ryan > > > > ___ > > 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
Re: [sqlite] "cursored" queries and total rows
I'm using also paged queries. I'm adding an OFFSET to the select-limit query. Works for me. Am 03.06.2018 um 14:16 schrieb R Smith: On 2018/06/03 1:13 PM, Wout Mertens wrote: Hi all, To do paged queries on a query like SELECT colVal FROM t WHERE b=? LIMIT 10 I keep track of column values and construct a query that will get the next item in a query by augmenting the query like SELECT colVal FROM t WHERE b=? AND colVal > ? LIMIT 10 To know how many rows there are in the query, I do SELECT COUNT(*) FROM t WHERE b=? Are there any efficiency tricks here? Is it better to run the count before the query or vice versa? I assume b is not the Primary Key, (since the use case suggests that it repeats), but hopefully it is at least an Index. - If it is not an Index, the entire system is inefficient. - If it is an Index, then it doesn't matter which comes first[*], the queries are dissimilar enough to not have differing caching advantages based on order of execution, except... - If it is an Index, /and/ the Key repeats magnificently much (Imagine adding an "Age" column to a phone-book and then filtering on Age, there will be thousands of people who are all 34, for instance) then you are better off extracting the set of records to a TEMP table and then paginating through the temp table's complete dataset and COUNT(*) its rows. This will be extremely fast, especially if the DB is otherwise quite hefty, and will allow using the new table's rowids (invisible in the query) as pagination pegs. Be sure to use a memory-oriented journal mode and cache settings for this, or if not possible, perhaps even a second attached in-memory or memory-oriented DB. [*] - The above assumes there are no FTS tables (or other special virtual tables) involved, nor any computed Keys - all of which may need more specific considerations. Cheers, Ryan ___ 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
Re: [sqlite] "cursored" queries and total rows
On 2018/06/03 1:13 PM, Wout Mertens wrote: Hi all, To do paged queries on a query like SELECT colVal FROM t WHERE b=? LIMIT 10 I keep track of column values and construct a query that will get the next item in a query by augmenting the query like SELECT colVal FROM t WHERE b=? AND colVal > ? LIMIT 10 To know how many rows there are in the query, I do SELECT COUNT(*) FROM t WHERE b=? Are there any efficiency tricks here? Is it better to run the count before the query or vice versa? I assume b is not the Primary Key, (since the use case suggests that it repeats), but hopefully it is at least an Index. - If it is not an Index, the entire system is inefficient. - If it is an Index, then it doesn't matter which comes first[*], the queries are dissimilar enough to not have differing caching advantages based on order of execution, except... - If it is an Index, /and/ the Key repeats magnificently much (Imagine adding an "Age" column to a phone-book and then filtering on Age, there will be thousands of people who are all 34, for instance) then you are better off extracting the set of records to a TEMP table and then paginating through the temp table's complete dataset and COUNT(*) its rows. This will be extremely fast, especially if the DB is otherwise quite hefty, and will allow using the new table's rowids (invisible in the query) as pagination pegs. Be sure to use a memory-oriented journal mode and cache settings for this, or if not possible, perhaps even a second attached in-memory or memory-oriented DB. [*] - The above assumes there are no FTS tables (or other special virtual tables) involved, nor any computed Keys - all of which may need more specific considerations. Cheers, Ryan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "cursored" queries and total rows
Wout Mertens wrote: > To do paged queries on a query like > > SELECT colVal FROM t WHERE b=? LIMIT 10 This does not make sense without an ORDER BY. > To know how many rows there are in the query, I do > > SELECT COUNT(*) FROM t WHERE b=? > > Are there any efficiency tricks here? No. SQLite computes rows on demand, so without the COUNT(*) query, it never even goes to the last rows. > Is it better to run the count before the query or vice versa? There is unlikely to be any noticable difference. (If there might be concurrent queries that modify the data, you should use a transaction to get consistent results.) Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] "cursored" queries and total rows
Hi all, To do paged queries on a query like SELECT colVal FROM t WHERE b=? LIMIT 10 I keep track of column values and construct a query that will get the next item in a query by augmenting the query like SELECT colVal FROM t WHERE b=? AND colVal > ? LIMIT 10 To know how many rows there are in the query, I do SELECT COUNT(*) FROM t WHERE b=? Are there any efficiency tricks here? Is it better to run the count before the query or vice versa? Thanks, Wout. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
