select sum(code_type == 'SET') as "#sets",
sum(code_type == 'CST') as "#constants",
sum(code_type == 'CMT') as #comments",
sum(code_type == 'LOG') as "#logs"
from keys;
will do a single table scan and return the sums in a single statement with no
complications ...
--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
> -----Original Message-----
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Dominique Devienne
> Sent: Thursday, 6 July, 2017 02:52
> To: General Discussion of SQLite Database
> Subject: [sqlite] Any way to avoid scanning a large table several times?
>
> I have a view gathering statistics from 3 different tables, one of which
> is
> largish (~ 2M rows).
>
> The view is similar to this:
>
> with
> kcounts(t, c) as (
> select code_type, count(*)
> from keys
> group by code_type
> ),
> ...
> select ...,
> (select c from kcounts where t = 'SET') as "#sets",
> (select c from kcounts where t = 'CST') as "#constants",
> (select c from kcounts where t = 'CMT') as "#comments",
> (select c from kcounts where t = 'LOG') as "#logs",
> ...;
>
> With all 4 counts, the views takes ~ 6.5s to compute its 1 row (with the
> file in cache, was 13.5s with a cold filesystem cache, while DB is only
> 380MB in size).
>
> If I remove 3 of the lines, keeping a single "select c from kcounts where
> t
> = ..." that drops to 2.15s, or about one third. (this is inside SQLiteSpy
> using SQLite 3.13.0 BTW).
>
> As a programmer, I can see this can be done in a single pass, but most
> likely the SQL as written does several passes over kcounts, despite using
> a
> CTE (I had hoped kcounts would be instantiated into an ephemeral table,
> and
> thus accessing a 4 row tables would be "free").
>
> An explain query plan indeed shows 4x "SCAN TABLE keys" steps, not just 1.
>
> Is there a way to reformulate this query differently to somehow achieve a
> single pass on the keys table? Or force the query planner to "instantiate"
> the kcounts "CTE view" to achieve that single scan, via some kind of hint?
> If none exists, could one such hint be added? Ideally the planner itself
> would decide kcounts should be instantiated, but I'm happy to tell him it
> should via a hint.
>
> The speed up is non-negligible at 3x, thus my request for comments from
> experts on this.
>
> Thanks, --DD
>
> PS: BTW, I did run ANALYZE and that didn't change the plan or execution
> time.
> _______________________________________________
> 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
