Re: [sqlite] Slow View when it is used in a where clause
> 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 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 >> 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-b
Re: [sqlite] Slow View when it is used in a where clause
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 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
Re: [sqlite] Slow View when it is used in a where clause
> 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 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
