Thanks for the answer guys... I'm afraid this is going beyond my SQL knowledge, so I'll let Stephane come back with a better example (when he gets his internet back :p)
Guillaume Le vendredi 1 juin 2012 09:30:06 Richard Hipp a écrit : > 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 -- 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
