On Fri, Jun 1, 2012 at 10:33 AM, Guillaume DE BURE < guillaume.deb...@gmail.com> 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 > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
