> Specifically, when I said "using directly the select of the view", I mean > execute this query: > > EXPLAIN QUERY PLAN SELECT wg.*, count(DISTINCT w.idWord) AS itemsCount, > count(DISTINCT dw.Document_idDocument) AS documentsCount > > FROM WordGroup wg > LEFT OUTER JOIN Word w ON wg.idWordGroup = w.WordGroup_idWordGroup > LEFT OUTER JOIN Document_Word dw ON w.idWord = dw.Word_idWord > Where idWordGroup = 1 > GROUP BY wg.idWordGroup;
This is a completely different query and its good performance is not surprising at all. > Anyway, I am thinking that perhaps the solution is create some triggers in > order to calculate these statistical measures, and add some new columns with > this data to my current tables. That's always a better solution for any statistical information IMHO. Pavel On Fri, Dec 2, 2011 at 11:01 AM, Manuel Jesus Cobo Martin <mjc...@gmail.com> wrote: > Hello Pavel, > > Thank you for your answer. > > Probably, as you have commented, the problem is that the optimizer of SQLite > does not work fine in this case. > > Specifically, when I said "using directly the select of the view", I mean > execute this query: > > EXPLAIN QUERY PLAN SELECT wg.*, count(DISTINCT w.idWord) AS itemsCount, > count(DISTINCT dw.Document_idDocument) AS documentsCount > > FROM WordGroup wg > LEFT OUTER JOIN Word w ON wg.idWordGroup = w.WordGroup_idWordGroup > LEFT OUTER JOIN Document_Word dw ON w.idWord = dw.Word_idWord > Where idWordGroup = 1 > GROUP BY wg.idWordGroup; > > This query perform properly, whereas the same query using the view performs > an SCAN over the table WordGroup when it is unnecessary. > > Anyway, I am thinking that perhaps the solution is create some triggers in > order to calculate these statistical measures, and add some new columns with > this data to my current tables. > > Best Regards, > > Manuel Jesús. > > El 02/12/2011 15:49, Pavel Ivanov escribió: > >>> I have been analyzing the Query PLAN. In the query "Select * FROM >>> WordGroupView Where idWordGroup = 1;" the database engine perform a scan >>> over WordGroup table, and it is not necessary since the where clause is >>> defined. However, using directly the query of the view change the SCAN by >>> a >>> SEARCH, so the query is faster answered. >>> >>> Please, someone know what is the problem? >> >> Probably SQLite's optimizer is not so smart to figure out that >> combination of GROUP BY in the inner SELECT and WHERE in the outer one >> on the same field can result in removing of GROUP BY and adding WHERE >> to inner query. >> >> Or when you say that using select from view directly results in faster >> query do you use query like the following? >> >> Select * FROM ( >> SELECT wg.*, count(DISTINCT w.idWord) AS itemsCount, count(DISTINCT >> dw.Document_idDocument) AS documentsCount >> FROM WordGroup wg >> LEFT OUTER JOIN Word w ON wg.idWordGroup = w.WordGroup_idWordGroup >> LEFT OUTER JOIN Document_Word dw ON w.idWord = dw.Word_idWord >> GROUP BY wg.idWordGroup; >> ) >> Where idWordGroup = 1; >> >> >> Pavel >> >> >> On Thu, Dec 1, 2011 at 7:42 PM, Manuel Jesus Cobo Martin >> <mjc...@gmail.com> wrote: >>> >>> Hello, >>> >>> I am new in this mailing list. >>> >>> I am developing a Java tool and I use SQLite as file format. At this >>> moments, I did not have any problem, and SQLite works quite fine. >>> >>> I want to create some views to generate statistical and aggregate data in >>> order to show more information to the user. >>> >>> The follow code is an example of a View: >>> >>> CREATE VIEW WordGroupView AS >>> SELECT wg.*, count(DISTINCT w.idWord) AS itemsCount, count(DISTINCT >>> dw.Document_idDocument) AS documentsCount >>> FROM WordGroup wg >>> LEFT OUTER JOIN Word w ON wg.idWordGroup = w.WordGroup_idWordGroup >>> LEFT OUTER JOIN Document_Word dw ON w.idWord = dw.Word_idWord >>> GROUP BY wg.idWordGroup; >>> >>> The problem is when I am using the view in other query. >>> >>> For example, the query "Select * FROM WordGroupView Where idWordGroup = >>> 1;" >>> is very slow. Whereas, if I use directly the main query of the view, is >>> faster (1400ms vs 7ms). >>> >>> I think that I am doing something in wrong way but I do not understand >>> what >>> is happens. >>> >>> I have been analyzing the Query PLAN. In the query "Select * FROM >>> WordGroupView Where idWordGroup = 1;" the database engine perform a scan >>> over WordGroup table, and it is not necessary since the where clause is >>> defined. However, using directly the query of the view change the SCAN by >>> a >>> SEARCH, so the query is faster answered. >>> >>> Please, someone know what is the problem? >>> >>> Thank you in advance. >>> >>> Best Regards, >>> >>> Manuel Jesús. >>> _______________________________________________ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
