On Thu, Jul 6, 2017 at 3:50 PM, Clemens Ladisch <clem...@ladisch.de> wrote:
> Dominique Devienne wrote: > > On Thu, Jul 6, 2017 at 12:54 PM, Clemens Ladisch wrote: > >> You could put kcounts into a temporary table. > > > > I could it in a table, but then who updates kcounts when keys (or > keys_tab) > > changes? > > I did not say "table" but "temporary table": > > BEGIN; > CREATE TEMPORARY TABLE kcounts AS SELECT ...; > SELECT ... FROM kcounts ...; > DROP TABLE kcounts; > COMMIT; > > > "Users" just "view" these views in any SQLite clients, typically a GUI > one. > > And they should remain "dynamic" > > into case the table contents are modified with DMLs. > > SQLite is designed as an embedded database, and expects that additional > logic, if needed, can be provided by the application. I never fail to be surprised when every time SQLite doesn't do something one can reasonably expect it would do, people always reach for the "lite" or "embedded" arguments... Just select code_type, count(*) from well_keys group by code_type by itself takes 2.01s. Accessing the other two tables takes < 200ms for both. The main select itself is just formatting, except of course it "transposes" the 4 rows from kcounts as columns. The fact SQLite flattens the kcounts CTE view is in fact "the problem" is this case. Should it instead "materialize" it in an "ephemeral table", the query would be 3x faster (Nx faster is not 4 rows, but N+1 rows in fact). As Richard often points out, there's no bug here, just an optimization opportunity. The result is correct. But saying one should resort to temporary tables to work-around a non-optimization and thus be forced to abandon nice simple views because SQLite is an "embedded" database is just "meh", for lack of a better word :) --DD _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
