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
