Hi Richard, In fact, the real need is "common subexpression elimination". As you can see in attached email, we already had this discussion.
To improve performances on SELECT in skrooge (see datamodel in test.sqlite), I created some tables (named vm_xxx) updated after each transaction through the corresponding view (named v_xxx).
This is a kind of "materialized view". *Do you plan something around CSE?* Thank you again for sqlite. Regards, Stephane Le 01/06/2012 15:30, Richard Hipp a écrit :
On Fri, Jun 1, 2012 at 10:33 AM, Guillaume DE BURE <[email protected] <mailto:[email protected]>> wrote:Hi all, In Skrooge (http://skrooge.org), we use extensively views, and views based on views. However, it seems that in such a case, the linked views are computed several times. To better explain the issue, Stephane, our main developper, created a small use case, explaining the issue : CREATE TABLE t(x); INSERT INTO t (x) VALUES ('A'); INSERT INTO t (x) VALUES ('B'); INSERT INTO t (x) VALUES ('C'); CREATE VIEW v_t1 AS SELECT x, random() as "Y" from t; CREATE VIEW v_t2 AS SELECT x, Y as "Y1", (Y+1) as "Y2" from v_t1; SELECT x, Y2-Y1 from v_t2; The result is: A|3495515542145967690 B|-2850173736877580107 C|5413870427642078391 instead of A|1 B|1 C|1 ==> WRONG RESULT The query flattener converts SELECT x, Y2-Y1 FROM v_t2; Into this: SELECT x, (random()+1)-random() FROM t;Is that an invalid transformation? I'm not so sure. It would certainly run faster if T were a large table and there was a WHERE clause that could be indexed. And it is not difficult to come up with an example where the query flattener is a huge performance win. But in any event, if you turn off the query flattener, you get the "right" (or, I would say, "expected") answer. To see this, run the following script in the sqlite3.exe command-line shell:CREATE TABLE t(x); INSERT INTO t (x) VALUES ('A'); INSERT INTO t (x) VALUES ('B'); INSERT INTO t (x) VALUES ('C'); CREATE VIEW v_t1 AS SELECT x, random() as "Y" from t; CREATE VIEW v_t2 AS SELECT x, Y as "Y1", (Y+1) as "Y2" from v_t1; SELECT x, Y2-Y1 from v_t2; .testctrl optimizations 1 SELECT x, Y2-Y1 FROM v_t2;The query, you see, is run twice. The first time with query flattening enabled and the second time with it disabled. The second time gives the answer you were expecting.While the problem illustrated here leads to a wrong result, the real problem for us is on the perfo side : underlying views are recomputed several times for a top level view.In the example above, the views are never computed at all - they are optimized out by the query flattener. Maybe you can come up with a better example to illustrate your problem?Or are you asking for common subexpression elimination (CSE)? Do you think there is room for improvement here ? Does this sound feasible ? Thanks :) Guillaume -- Skrooge, a free, Open Source, personal finances software for linux, Mac OS, Windows http://skrooge.org _______________________________________________ sqlite-users mailing list [email protected] <mailto:[email protected]> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp [email protected] <mailto:[email protected]>
_______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
