[sqlite] Speed-Lost on using expression with combined AND and OR!
Hi, I've got a problem in combining expressions. On combining multiple ANDs with an OR within a SELECT WHERE clause, SQLite takes years to respond. (Database has a size of only about 1 MB). Using two SELECTs and combining them by UNION works like a charm! What could be the problem here? This statement takes about 10 seconds after result can be read: SELECT . FROM Scenes AS s, Movies AS m, Categories AS c WHERE m.Id = s.MovieId AND ((s.MovieId = c.MovieId AND s.SceneNo = c.SceneNo AND c.CategorieId IN (60)) OR Rating IN (2)) Working statement (immediate response): SELECT . FROM Scenes AS s, Movies AS m, Categories AS c WHERE m.Id = s.MovieId AND s.MovieId = c.MovieId AND s.SceneNo = c.SceneNo AND c.CategorieId IN (410) UNION SELECT . FROM Scenes AS s, Movies AS m WHERE m.Id = s.MovieId AND s.Rating IN(1) I am using System.Data.SQLite from http://sqlite.phxsoftware.com/ Thanks for any response! Regards, Markus ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Speed-Lost on using expression with combined AND and OR!
*System.Data.SQLite *is an enhanced version of the original SQLite http://www.sqlite.org/database engine. It is a complete drop-in replacement for the original sqlite3.dll If you don't use original sqlite I think you can't get any help here ... -- [image: Just A Little Bit Of Geekness]http://feeds.feedburner.com/%7Er/JustALittleBitOfGeekness/%7E6/1 Le tre grandi virtù di un programmatore: pigrizia, impazienza e arroganza. (Larry Wall). 2008/6/4 Markus Wolters [EMAIL PROTECTED]: Hi, I've got a problem in combining expressions. On combining multiple ANDs with an OR within a SELECT WHERE clause, SQLite takes years to respond. (Database has a size of only about 1 MB). Using two SELECTs and combining them by UNION works like a charm! What could be the problem here? This statement takes about 10 seconds after result can be read: SELECT . FROM Scenes AS s, Movies AS m, Categories AS c WHERE m.Id = s.MovieId AND ((s.MovieId = c.MovieId AND s.SceneNo = c.SceneNo AND c.CategorieId IN (60)) OR Rating IN (2)) Working statement (immediate response): SELECT . FROM Scenes AS s, Movies AS m, Categories AS c WHERE m.Id = s.MovieId AND s.MovieId = c.MovieId AND s.SceneNo = c.SceneNo AND c.CategorieId IN (410) UNION SELECT . FROM Scenes AS s, Movies AS m WHERE m.Id = s.MovieId AND s.Rating IN(1) I am using System.Data.SQLite from http://sqlite.phxsoftware.com/ Thanks for any response! Regards, Markus ___ 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] Speed-Lost on using expression with combined AND and OR!
Well why not? They've just integrated the ADO.NET provider into the DLL. It IS SQLITE... -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:sqlite-users- [EMAIL PROTECTED] Im Auftrag von Federico Granata Gesendet: Mittwoch, 4. Juni 2008 11:28 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Speed-Lost on using expression with combined AND and OR! *System.Data.SQLite *is an enhanced version of the original SQLite http://www.sqlite.org/database engine. It is a complete drop-in replacement for the original sqlite3.dll If you don't use original sqlite I think you can't get any help here ... -- [image: Just A Little Bit Of Geekness]http://feeds.feedburner.com/%7Er/JustALittleBitOfGeekness/%7E 6/1 Le tre grandi virtù di un programmatore: pigrizia, impazienza e arroganza. (Larry Wall). 2008/6/4 Markus Wolters [EMAIL PROTECTED]: Hi, I've got a problem in combining expressions. On combining multiple ANDs with an OR within a SELECT WHERE clause, SQLite takes years to respond. (Database has a size of only about 1 MB). Using two SELECTs and combining them by UNION works like a charm! What could be the problem here? This statement takes about 10 seconds after result can be read: SELECT . FROM Scenes AS s, Movies AS m, Categories AS c WHERE m.Id = s.MovieId AND ((s.MovieId = c.MovieId AND s.SceneNo = c.SceneNo AND c.CategorieId IN (60)) OR Rating IN (2)) Working statement (immediate response): SELECT . FROM Scenes AS s, Movies AS m, Categories AS c WHERE m.Id = s.MovieId AND s.MovieId = c.MovieId AND s.SceneNo = c.SceneNo AND c.CategorieId IN (410) UNION SELECT . FROM Scenes AS s, Movies AS m WHERE m.Id = s.MovieId AND s.Rating IN(1) I am using System.Data.SQLite from http://sqlite.phxsoftware.com/ Thanks for any response! Regards, Markus ___ 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] Speed-Lost on using expression with combined AND and OR!
Hi Markus, http://www.sqlite.org/optoverview.html may help... From earlier performance discussions, ORs can prevent indices being used - do your tables have indices? Try EXPLAIN QUERY PLAN preceding your SELECTs to determine whether indices are being used. Rgds, Simon 2008/6/4 Markus Wolters [EMAIL PROTECTED]: Hi, I've got a problem in combining expressions. On combining multiple ANDs with an OR within a SELECT WHERE clause, SQLite takes years to respond. (Database has a size of only about 1 MB). Using two SELECTs and combining them by UNION works like a charm! What could be the problem here? This statement takes about 10 seconds after result can be read: SELECT . FROM Scenes AS s, Movies AS m, Categories AS c WHERE m.Id = s.MovieId AND ((s.MovieId = c.MovieId AND s.SceneNo = c.SceneNo AND c.CategorieId IN (60)) OR Rating IN (2)) Working statement (immediate response): SELECT . FROM Scenes AS s, Movies AS m, Categories AS c WHERE m.Id = s.MovieId AND s.MovieId = c.MovieId AND s.SceneNo = c.SceneNo AND c.CategorieId IN (410) UNION SELECT . FROM Scenes AS s, Movies AS m WHERE m.Id = s.MovieId AND s.Rating IN(1) I am using System.Data.SQLite from http://sqlite.phxsoftware.com/ Thanks for any response! Regards, Markus ___ 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] Speed-Lost on using expression with combined AND and OR!
2008/6/4 Markus Wolters [EMAIL PROTECTED]: Well why not? They've just integrated the ADO.NET provider into the DLL. It IS SQLITE... I have just quote what they write: It is a complete drop-in replacement for the original sqlite3.dll I don't know what they rewrite and how ... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Speed-Lost on using expression with combined AND andOR!
Markus Wolters [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I've got a problem in combining expressions. On combining multiple ANDs with an OR within a SELECT WHERE clause, SQLite takes years to respond. (Database has a size of only about 1 MB). Using two SELECTs and combining them by UNION works like a charm! What could be the problem here? This statement takes about 10 seconds after result can be read: SELECT . FROM Scenes AS s, Movies AS m, Categories AS c WHERE m.Id = s.MovieId AND ((s.MovieId = c.MovieId AND s.SceneNo = c.SceneNo AND c.CategorieId IN (60)) OR Rating IN (2)) Categories table is not joined to other tables on one side of OR. As a result, you build a full cross-product with Categories table: every record with (m.Id = s.MovieId AND Rating=2) is duplicated once for each category. SELECT . FROM Scenes AS s, Movies AS m, Categories AS c WHERE m.Id = s.MovieId AND s.MovieId = c.MovieId AND s.SceneNo = c.SceneNo AND c.CategorieId IN (410) UNION SELECT . FROM Scenes AS s, Movies AS m WHERE m.Id = s.MovieId AND s.Rating IN(1) The second statement in the UNION doesn't mention Categories table, so no cross-product. The two queries are not equivalent. The fact that they return different number of rows should have given you a hint. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
