Re: [sqlite] Query efficiency

[Pavel Ivanov]

> sqlite> select * from aa where a_id1 in (select distinct a_id1 from ab
> where ab.a_id2 = 1 and ab.b_id = 1) and a_id2 in (select distinct a_id1
> from ab where ab.a_id2 = 1 and ab.b_id = 1) ;

With your schema this can be transformed the same way:

select aa.*
from aa, ab ab1, ab ab2
where aa.a_id1 = ab1.a_id1
and ab1.a_id2 = 1
and ab1.b_id = 1
and aa.a_id2 = ab2.a_id1
and ab2.a_id2 = 1
and ab2.b_id = 1;


Pavel


On Thu, May 19, 2011 at 1:33 PM, Matthew Jones <matthew.jo...@hp.com> wrote:
> First of all I couldn't for the life of we work out why that new query
> would work but I'm sure that's just a limit of my knowledge. I then
> realised that the database definition I had used was really very
> different from what I was trying to do so I've had another go and then
> tried to use the query with the new schema. Unfortunately, I have failed
> miserably so here's the schema I'm working with now.
>
> Note that table b isn't used by I've created it to show that table ab is
> a list of references of a records to b records and b records contain an a_id
>
> sqlite> create table a (a_id int primary key);
> sqlite> create table b (a_id int, b_id int, primary key(a_id, b_id));
> sqlite> insert into a values(1);
> sqlite> insert into a values(2);
> sqlite> create table aa (a_id1 int, a_id2 int, primary key(a_id1, a_id2));
> sqlite> insert into aa values (1, 1);
> sqlite> insert into aa values (1, 2);
> sqlite> insert into aa values (2, 1);
> sqlite> insert into aa values (3, 1);
> sqlite> insert into aa values (1, 3);
> sqlite> select * from aa;
> 1|1
> 1|2
> 2|1
> 3|1
> 1|3
> sqlite> create table ab (a_id1 int, a_id2 int, b_id int, primary
> key(a_id1, a_id2, b_id));
> sqlite> insert into ab values(1, 1, 1);
> sqlite> insert into ab values(2, 1, 1);
> sqlite> select * from aa where a_id1 in (select distinct a_id1 from ab
> where ab.a_id2 = 1 and ab.b_id = 1) and a_id2 in (select distinct a_id1
> from ab where ab.a_id2 = 1 and ab.b_id = 1) ;
> 1|1
> 1|2
> 2|1
> sqlite> select aa.* from aa ar, ab ab1, ab ab2;
> Error: no such table: aa
> sqlite> select aa.* from aa as ar, ab ab1, ab ab2;
> Error: no such table: aa
> sqlite>
>
> At this point I got confused but realised I had asked about a very
> different schema and obviously must be doing something wrong as this
> failed before adding any sort of where clause.
>
>
>> From: Pavel Ivanov<paiva...@gmail.com>
>> Subject: Re: [sqlite] Query efficiency
>>
>>> >  That is, is leaving it to the
>>> >  query optimiser to figure out that I only need the sub select once the
>>> >  best thing to do?
>> AFAIK, SQLite's optimizer is not that smart to collapse two identical
>> sub-queries and reuse once generated result.
>>
>>> >  Is the select I'm doing where both a_id1&  2 are "in" the exact same
>>> >  select the most efficient way to do this?
>> I'd say that the following query will work faster in this particular
>> case (with this set of tables and indexes):
>>
>> select ar.*
>> from a_relation ar, ab ab1, ab ab2
>> where ar.a_id1 = ab1.a_id
>> and ab1.b_id = 1
>> and ar.a_id2 = ab2.a_id
>> and ab2.b_id = 1;
>>
>> But this query could be not transformable to your real case. Also
>> performance in real schema could be different.
>>
>>
>> Pavel
>>
>>
>> On Tue, May 17, 2011 at 5:29 AM, Matthew Jones<matthew.jo...@hp.com>  wrote:
>>> >  O.k. So this is a very cut down example but it illustrates the question:
>>> >
>>> >  sqlite>  create table a (a_id int primary key);
>>> >  sqlite>  create table b (b_id int primary key);
>>> >  sqlite>  create table ab (a_id int, b_id int, primary key(a_id, b_id));
>>> >  sqlite>  create table a_relation (a_id1 int, a_id2, primary key(a_id1,
>>> >  a_id2));
>>> >  sqlite>  select * from a_relation where
>>> >  ? ?...>  a_id1 in (select a_id from ab where b_id = 1) and
>>> >  ? ?...>  a_id2 in (select a_id from ab where b_id = 1);
>>> >
>>> >  Is the select I'm doing where both a_id1&  2 are "in" the exact same
>>> >  select the most efficient way to do this? That is, is leaving it to the
>>> >  query optimiser to figure out that I only need the sub select once the
>>> >  best thing to do?
>>> >
>>> >  (The actual tables in question are a little more complicated and I have
>>> >  versions to cope with but this effectively what I'm doing in C++ [so I'm
>>> >  preparing and binding etc.]. The actual sub select have a group by a_id
>>> >  to cope with multiple entries with different versions.)
>>> >
>>> >  Thanks
>>> >
>>> >  --
>>> >  Matthew Jones
>>> >  Hewlett-Packard Ltd
>>> >
>
> Thanks
>
> --
> Matthew Jones
> Hewlett-Packard Ltd
> ___
> 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] Query efficiency

[Jim Morris]

You must use the alias if specified:

select ar.* from aa ar, ab ab1, ab ab2;
rather than
select aa.* from aa ar, ab ab1, ab ab2;




On 5/19/2011 10:33 AM, Matthew Jones wrote:
> select aa.* from aa ar, ab ab1, ab ab2;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query efficiency

[Matthew Jones]

First of all I couldn't for the life of we work out why that new query 
would work but I'm sure that's just a limit of my knowledge. I then 
realised that the database definition I had used was really very 
different from what I was trying to do so I've had another go and then 
tried to use the query with the new schema. Unfortunately, I have failed 
miserably so here's the schema I'm working with now.

Note that table b isn't used by I've created it to show that table ab is 
a list of references of a records to b records and b records contain an a_id

sqlite> create table a (a_id int primary key);
sqlite> create table b (a_id int, b_id int, primary key(a_id, b_id));
sqlite> insert into a values(1);
sqlite> insert into a values(2);
sqlite> create table aa (a_id1 int, a_id2 int, primary key(a_id1, a_id2));
sqlite> insert into aa values (1, 1);
sqlite> insert into aa values (1, 2);
sqlite> insert into aa values (2, 1);
sqlite> insert into aa values (3, 1);
sqlite> insert into aa values (1, 3);
sqlite> select * from aa;
1|1
1|2
2|1
3|1
1|3
sqlite> create table ab (a_id1 int, a_id2 int, b_id int, primary 
key(a_id1, a_id2, b_id));
sqlite> insert into ab values(1, 1, 1);
sqlite> insert into ab values(2, 1, 1);
sqlite> select * from aa where a_id1 in (select distinct a_id1 from ab 
where ab.a_id2 = 1 and ab.b_id = 1) and a_id2 in (select distinct a_id1 
from ab where ab.a_id2 = 1 and ab.b_id = 1) ;
1|1
1|2
2|1
sqlite> select aa.* from aa ar, ab ab1, ab ab2;
Error: no such table: aa
sqlite> select aa.* from aa as ar, ab ab1, ab ab2;
Error: no such table: aa
sqlite>

At this point I got confused but realised I had asked about a very 
different schema and obviously must be doing something wrong as this 
failed before adding any sort of where clause.


> From: Pavel Ivanov<paiva...@gmail.com>
> Subject: Re: [sqlite] Query efficiency
>
>> >  That is, is leaving it to the
>> >  query optimiser to figure out that I only need the sub select once the
>> >  best thing to do?
> AFAIK, SQLite's optimizer is not that smart to collapse two identical
> sub-queries and reuse once generated result.
>
>> >  Is the select I'm doing where both a_id1&  2 are "in" the exact same
>> >  select the most efficient way to do this?
> I'd say that the following query will work faster in this particular
> case (with this set of tables and indexes):
>
> select ar.*
> from a_relation ar, ab ab1, ab ab2
> where ar.a_id1 = ab1.a_id
> and ab1.b_id = 1
> and ar.a_id2 = ab2.a_id
> and ab2.b_id = 1;
>
> But this query could be not transformable to your real case. Also
> performance in real schema could be different.
>
>
> Pavel
>
>
> On Tue, May 17, 2011 at 5:29 AM, Matthew Jones<matthew.jo...@hp.com>  wrote:
>> >  O.k. So this is a very cut down example but it illustrates the question:
>> >
>> >  sqlite>  create table a (a_id int primary key);
>> >  sqlite>  create table b (b_id int primary key);
>> >  sqlite>  create table ab (a_id int, b_id int, primary key(a_id, b_id));
>> >  sqlite>  create table a_relation (a_id1 int, a_id2, primary key(a_id1,
>> >  a_id2));
>> >  sqlite>  select * from a_relation where
>> >  ? ?...>  a_id1 in (select a_id from ab where b_id = 1) and
>> >  ? ?...>  a_id2 in (select a_id from ab where b_id = 1);
>> >
>> >  Is the select I'm doing where both a_id1&  2 are "in" the exact same
>> >  select the most efficient way to do this? That is, is leaving it to the
>> >  query optimiser to figure out that I only need the sub select once the
>> >  best thing to do?
>> >
>> >  (The actual tables in question are a little more complicated and I have
>> >  versions to cope with but this effectively what I'm doing in C++ [so I'm
>> >  preparing and binding etc.]. The actual sub select have a group by a_id
>> >  to cope with multiple entries with different versions.)
>> >
>> >  Thanks
>> >
>> >  --
>> >  Matthew Jones
>> >  Hewlett-Packard Ltd
>> >

Thanks

-- 
Matthew Jones
Hewlett-Packard Ltd
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query efficiency

[Pavel Ivanov]

> That is, is leaving it to the
> query optimiser to figure out that I only need the sub select once the
> best thing to do?

AFAIK, SQLite's optimizer is not that smart to collapse two identical
sub-queries and reuse once generated result.

> Is the select I'm doing where both a_id1 & 2 are "in" the exact same
> select the most efficient way to do this?

I'd say that the following query will work faster in this particular
case (with this set of tables and indexes):

select ar.*
from a_relation ar, ab ab1, ab ab2
where ar.a_id1 = ab1.a_id
and ab1.b_id = 1
and ar.a_id2 = ab2.a_id
and ab2.b_id = 1;

But this query could be not transformable to your real case. Also
performance in real schema could be different.


Pavel


On Tue, May 17, 2011 at 5:29 AM, Matthew Jones  wrote:
> O.k. So this is a very cut down example but it illustrates the question:
>
> sqlite> create table a (a_id int primary key);
> sqlite> create table b (b_id int primary key);
> sqlite> create table ab (a_id int, b_id int, primary key(a_id, b_id));
> sqlite> create table a_relation (a_id1 int, a_id2, primary key(a_id1,
> a_id2));
> sqlite> select * from a_relation where
>    ...> a_id1 in (select a_id from ab where b_id = 1) and
>    ...> a_id2 in (select a_id from ab where b_id = 1);
>
> Is the select I'm doing where both a_id1 & 2 are "in" the exact same
> select the most efficient way to do this? That is, is leaving it to the
> query optimiser to figure out that I only need the sub select once the
> best thing to do?
>
> (The actual tables in question are a little more complicated and I have
> versions to cope with but this effectively what I'm doing in C++ [so I'm
> preparing and binding etc.]. The actual sub select have a group by a_id
> to cope with multiple entries with different versions.)
>
> Thanks
>
> --
> Matthew Jones
> Hewlett-Packard Ltd
> ___
> 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] Query efficiency

[Matthew Jones]

O.k. So this is a very cut down example but it illustrates the question:

sqlite> create table a (a_id int primary key);
sqlite> create table b (b_id int primary key);
sqlite> create table ab (a_id int, b_id int, primary key(a_id, b_id));
sqlite> create table a_relation (a_id1 int, a_id2, primary key(a_id1, 
a_id2));
sqlite> select * from a_relation where
...> a_id1 in (select a_id from ab where b_id = 1) and
...> a_id2 in (select a_id from ab where b_id = 1);

Is the select I'm doing where both a_id1 & 2 are "in" the exact same 
select the most efficient way to do this? That is, is leaving it to the 
query optimiser to figure out that I only need the sub select once the 
best thing to do?

(The actual tables in question are a little more complicated and I have 
versions to cope with but this effectively what I'm doing in C++ [so I'm 
preparing and binding etc.]. The actual sub select have a group by a_id 
to cope with multiple entries with different versions.)

Thanks

-- 
Matthew Jones
Hewlett-Packard Ltd
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users