Re: [sqlite] Speed-Lost on using expression with combined "AND" and"OR"!

2008-06-04 Thread Igor Tandetnik 
"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

Re: [sqlite] Speed-Lost on using expression with combined "AND" and "OR"!

2008-06-04 Thread Federico Granata 
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" and "OR"!

2008-06-04 Thread Simon Davies 
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-06-04 Thread Markus Wolters 
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"!

2008-06-04 Thread Federico Granata 
"*System.Data.SQLite *is an enhanced version of the original SQLite
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]
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