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
