Re: [sqlite] How to decide which table is the outer table and which table is the inner table?

[Kristoffer Danielsson]

Yes!!

SQLITE_ENABLE_STAT2 did the trick. I can see that the SQLite library increased 
some 10 kB after this change. No big deal.

 

Why isn't this flag on by default?


 
> From: kristoffer.daniels...@live.se
> To: sqlite-users@sqlite.org
> Date: Sun, 1 Nov 2009 17:04:41 +0100
> Subject: Re: [sqlite] How to decide which table is the outer table and which 
> table is the inner table?
> 
> 
> The parentheses around A is a workaround to avoid the "natural self-join" bug 
> (fixed in 3.6.20+ as far as I know). In this particular case they can be 
> omitted, with no difference. The parenthesis around B+C+D are there to get a 
> valid inner join - B and D do not contain A_ID, but C does.
> 
> 
> 
> These are ultra-fast:
> 
> SELECT COUNT(A_ID) FROM A INNER JOIN (B NATURAL JOIN C NATURAL JOIN D) USING 
> (A_ID);
> 
> SELECT COUNT(A_ID) FROM B NATURAL JOIN C NATURAL JOIN D NATURAL JOIN A;
> 
> 
> 
> These are ultra-slow:
> 
> SELECT COUNT(A_ID) FROM A NATURAL JOIN (B NATURAL JOIN C NATURAL JOIN D);
> 
> SELECT COUNT(A.A_ID) FROM (B NATURAL JOIN C NATURAL JOIN D) NATURAL JOIN A;
> 
> 
> 
> I'm wondering if the optimizer makes a stupid choice due to the 
> SQLITE_ENABLE_STAT2 flag, which was not set during compilation. I'll try the 
> ANALYZE command after recompiling with this flag activated, and see if it 
> makes any difference.
> 
> 
> 
> And if it doesn't, I'll make sure to produce a sample that illustrates the 
> problem.
> 
> 
> 
> Thanks.
> 
> 
> 
> > From: paiva...@gmail.com
> > Date: Sun, 1 Nov 2009 10:33:58 -0400
> > To: sqlite-users@sqlite.org
> > Subject: Re: [sqlite] How to decide which table is the outer table and 
> > which table is the inner table?
> > 
> > > (B + C + D) have two columns that are present in A, of which only one is 
> > > indexed. Could that be the culprit?
> > 
> > There's an easy way to check that: just re-write your query so that it
> > contains joins with explicitly pointed condition which columns join
> > should be made on.
> > 
> > BTW, why are you putting parenthesis there? Maybe it makes less
> > freedom to choose for SQLite's optimizer?
> > 
> > Pavel
> > 
> > On Sat, Oct 31, 2009 at 7:27 PM, Kristoffer Danielsson
> >  wrote:
> > >
> > > All I know is that SQLite chooses the wrong order when I give it my query 
> > > at this form:
> > >
> > >
> > >
> > > SELECT COUNT(A_ID) FROM (A) NATURAL JOIN (B NATURAL JOIN C NATURAL JOIN 
> > > D);
> > >
> > >
> > >
> > > Where:
> > >
> > > A = 50,000 rows.
> > >
> > > B = 2 rows.
> > >
> > > C = 10,000 rows
> > >
> > > D = 250,000 rows
> > >
> > >
> > >
> > >
> > >
> > > (B + C + D) have two columns that are present in A, of which only one is 
> > > indexed. Could that be the culprit?
> > >
> > >
> > >
> > > Though, if I put A last, the query is blistering fast. Clearly, SQLite 
> > > does something wrong in this case.
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >> From: paiva...@gmail.com
> > >> Date: Fri, 30 Oct 2009 12:16:17 -0400
> > >> To: sqlite-users@sqlite.org
> > >> Subject: Re: [sqlite] How to decide which table is the outer table and 
> > >> which table is the inner table?
> > >>
> > >> Of course SQLite wasn't changed much in this part since November 2008
> > >> but the citation you gave is either wrong or the key words in it are
> > >> "something like" in phrase "SQlite does something like this". Because
> > >> SQLite is smart enough to choose smaller table as an outer one and
> > >> bigger table as an inner one. Although it can choose other way round
> > >> if you have index on Id in smaller table and don't have index on Id in
> > >> bigger table. And in this case there's no performance hit in such
> > >> decision, only benefit.
> > >>
> > >> Pavel
> > >>
> > >> On Fri, Oct 30, 2009 at 12:07 PM, Kristoffer Danielsson
> > >>  wrote:
> > >> >
> > >> > Quote from: http://sqlite.phxsoftware.com/forums/p/1495/6629.aspx
> > >> >
> > >> >
> > >> >
> > >> > SQLite uses only nested loops to implement joins. Given a query like 
> > >> > the following:
> > >> >
> > >> > SELECT ...
> > >> > FROM OuterTable O INNER JOIN InnerTable I ON O.Id = I.Id
> > >> >
> > >> >
> > >> >
> > >> > SQlite does something like this:
> > >> >
> > >> >
> > >> >
> > >> > for each row in OuterTable
> > >> > Seek all rows in InnerTable where InnerTable.Id = OuterTable.Id
> > >> > end for each
> > >> >
> > >> >
> > >> >
> > >> > I assume SQLite has not improved on JOIN precedence since then, which 
> > >> > means that if OuterTable is HUGE, there will be an huge performance 
> > >> > hit!
> > >> >
> > >> >
> > >> >
> > >> > "Some database engines like SQL Server decide which table is the outer 
> > >> > table and which table is the inner table"
> > >> >
> > >> >
> > >> > How do I simulate that behavior in SQLite? A misformed SQL statement 
> > >> > from the user results in unacceptable lockups...
> > >> >
> > >> > _
> > >> > Nya 

Re: [sqlite] How to decide which table is the outer table and which table is the inner table?

[Kristoffer Danielsson]

The parentheses around A is a workaround to avoid the "natural self-join" bug 
(fixed in 3.6.20+ as far as I know). In this particular case they can be 
omitted, with no difference. The parenthesis around B+C+D are there to get a 
valid inner join - B and D do not contain A_ID, but C does.

 

These are ultra-fast:

SELECT COUNT(A_ID) FROM A INNER JOIN (B NATURAL JOIN C NATURAL JOIN D) USING 
(A_ID);

SELECT COUNT(A_ID) FROM B NATURAL JOIN C NATURAL JOIN D NATURAL JOIN A;

 

These are ultra-slow:

SELECT COUNT(A_ID) FROM A NATURAL JOIN (B NATURAL JOIN C NATURAL JOIN D);

SELECT COUNT(A.A_ID) FROM (B NATURAL JOIN C NATURAL JOIN D) NATURAL JOIN A;

 

I'm wondering if the optimizer makes a stupid choice due to the 
SQLITE_ENABLE_STAT2 flag, which was not set during compilation. I'll try the 
ANALYZE command after recompiling with this flag activated, and see if it makes 
any difference.

 

And if it doesn't, I'll make sure to produce a sample that illustrates the 
problem.

 

Thanks.


 
> From: paiva...@gmail.com
> Date: Sun, 1 Nov 2009 10:33:58 -0400
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] How to decide which table is the outer table and which 
> table is the inner table?
> 
> > (B + C + D) have two columns that are present in A, of which only one is 
> > indexed. Could that be the culprit?
> 
> There's an easy way to check that: just re-write your query so that it
> contains joins with explicitly pointed condition which columns join
> should be made on.
> 
> BTW, why are you putting parenthesis there? Maybe it makes less
> freedom to choose for SQLite's optimizer?
> 
> Pavel
> 
> On Sat, Oct 31, 2009 at 7:27 PM, Kristoffer Danielsson
>  wrote:
> >
> > All I know is that SQLite chooses the wrong order when I give it my query 
> > at this form:
> >
> >
> >
> > SELECT COUNT(A_ID) FROM (A) NATURAL JOIN (B NATURAL JOIN C NATURAL JOIN D);
> >
> >
> >
> > Where:
> >
> > A = 50,000 rows.
> >
> > B = 2 rows.
> >
> > C = 10,000 rows
> >
> > D = 250,000 rows
> >
> >
> >
> >
> >
> > (B + C + D) have two columns that are present in A, of which only one is 
> > indexed. Could that be the culprit?
> >
> >
> >
> > Though, if I put A last, the query is blistering fast. Clearly, SQLite does 
> > something wrong in this case.
> >
> >
> >
> >
> >
> >
> >
> >> From: paiva...@gmail.com
> >> Date: Fri, 30 Oct 2009 12:16:17 -0400
> >> To: sqlite-users@sqlite.org
> >> Subject: Re: [sqlite] How to decide which table is the outer table and 
> >> which table is the inner table?
> >>
> >> Of course SQLite wasn't changed much in this part since November 2008
> >> but the citation you gave is either wrong or the key words in it are
> >> "something like" in phrase "SQlite does something like this". Because
> >> SQLite is smart enough to choose smaller table as an outer one and
> >> bigger table as an inner one. Although it can choose other way round
> >> if you have index on Id in smaller table and don't have index on Id in
> >> bigger table. And in this case there's no performance hit in such
> >> decision, only benefit.
> >>
> >> Pavel
> >>
> >> On Fri, Oct 30, 2009 at 12:07 PM, Kristoffer Danielsson
> >>  wrote:
> >> >
> >> > Quote from: http://sqlite.phxsoftware.com/forums/p/1495/6629.aspx
> >> >
> >> >
> >> >
> >> > SQLite uses only nested loops to implement joins. Given a query like the 
> >> > following:
> >> >
> >> > SELECT ...
> >> > FROM OuterTable O INNER JOIN InnerTable I ON O.Id = I.Id
> >> >
> >> >
> >> >
> >> > SQlite does something like this:
> >> >
> >> >
> >> >
> >> > for each row in OuterTable
> >> >  Seek all rows in InnerTable where InnerTable.Id = OuterTable.Id
> >> > end for each
> >> >
> >> >
> >> >
> >> > I assume SQLite has not improved on JOIN precedence since then, which 
> >> > means that if OuterTable is HUGE, there will be an huge performance hit!
> >> >
> >> >
> >> >
> >> > "Some database engines like SQL Server decide which table is the outer 
> >> > table and which table is the inner table"
> >> >
> >> >
> >> > How do I simulate that behavior in SQLite? A misformed SQL statement 
> >> > from the user results in unacceptable lockups...
> >> >
> >> > _
> >> > Nya Windows 7 - Hitta en dator som passar dig! Mer information.
> >> > http://windows.microsoft.com/shop
> >> > ___
> >> > 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
> >
> >
> > _
> > Nya Windows 7 gör allt lite enklare. Hitta en dator som passar dig!
> > http://windows.microsoft.com/shop
> > ___

Re: [sqlite] How to decide which table is the outer table and which table is the inner table?

[Pavel Ivanov]

> (B + C + D) have two columns that are present in A, of which only one is 
> indexed. Could that be the culprit?

There's an easy way to check that: just re-write your query so that it
contains joins with explicitly pointed condition which columns join
should be made on.

BTW, why are you putting parenthesis there? Maybe it makes less
freedom to choose for SQLite's optimizer?

Pavel

On Sat, Oct 31, 2009 at 7:27 PM, Kristoffer Danielsson
 wrote:
>
> All I know is that SQLite chooses the wrong order when I give it my query at 
> this form:
>
>
>
> SELECT COUNT(A_ID) FROM (A) NATURAL JOIN (B NATURAL JOIN C NATURAL JOIN D);
>
>
>
> Where:
>
> A = 50,000 rows.
>
> B = 2 rows.
>
> C = 10,000 rows
>
> D = 250,000 rows
>
>
>
>
>
> (B + C + D) have two columns that are present in A, of which only one is 
> indexed. Could that be the culprit?
>
>
>
> Though, if I put A last, the query is blistering fast. Clearly, SQLite does 
> something wrong in this case.
>
>
>
>
>
>
>
>> From: paiva...@gmail.com
>> Date: Fri, 30 Oct 2009 12:16:17 -0400
>> To: sqlite-users@sqlite.org
>> Subject: Re: [sqlite] How to decide which table is the outer table and which 
>> table is the inner table?
>>
>> Of course SQLite wasn't changed much in this part since November 2008
>> but the citation you gave is either wrong or the key words in it are
>> "something like" in phrase "SQlite does something like this". Because
>> SQLite is smart enough to choose smaller table as an outer one and
>> bigger table as an inner one. Although it can choose other way round
>> if you have index on Id in smaller table and don't have index on Id in
>> bigger table. And in this case there's no performance hit in such
>> decision, only benefit.
>>
>> Pavel
>>
>> On Fri, Oct 30, 2009 at 12:07 PM, Kristoffer Danielsson
>>  wrote:
>> >
>> > Quote from: http://sqlite.phxsoftware.com/forums/p/1495/6629.aspx
>> >
>> >
>> >
>> > SQLite uses only nested loops to implement joins. Given a query like the 
>> > following:
>> >
>> > SELECT ...
>> > FROM OuterTable O INNER JOIN InnerTable I ON O.Id = I.Id
>> >
>> >
>> >
>> > SQlite does something like this:
>> >
>> >
>> >
>> > for each row in OuterTable
>> >  Seek all rows in InnerTable where InnerTable.Id = OuterTable.Id
>> > end for each
>> >
>> >
>> >
>> > I assume SQLite has not improved on JOIN precedence since then, which 
>> > means that if OuterTable is HUGE, there will be an huge performance hit!
>> >
>> >
>> >
>> > "Some database engines like SQL Server decide which table is the outer 
>> > table and which table is the inner table"
>> >
>> >
>> > How do I simulate that behavior in SQLite? A misformed SQL statement from 
>> > the user results in unacceptable lockups...
>> >
>> > _
>> > Nya Windows 7 - Hitta en dator som passar dig! Mer information.
>> > http://windows.microsoft.com/shop
>> > ___
>> > 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
>
>
> _
> Nya Windows 7 gör allt lite enklare. Hitta en dator som passar dig!
> http://windows.microsoft.com/shop
> ___
> 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] How to decide which table is the outer table and which table is the inner table?

[Kristoffer Danielsson]

All I know is that SQLite chooses the wrong order when I give it my query at 
this form:

 

SELECT COUNT(A_ID) FROM (A) NATURAL JOIN (B NATURAL JOIN C NATURAL JOIN D);

 

Where:

A = 50,000 rows.

B = 2 rows.

C = 10,000 rows

D = 250,000 rows

 

 

(B + C + D) have two columns that are present in A, of which only one is 
indexed. Could that be the culprit?

 

Though, if I put A last, the query is blistering fast. Clearly, SQLite does 
something wrong in this case.

 

 

 

> From: paiva...@gmail.com
> Date: Fri, 30 Oct 2009 12:16:17 -0400
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] How to decide which table is the outer table and which 
> table is the inner table?
> 
> Of course SQLite wasn't changed much in this part since November 2008
> but the citation you gave is either wrong or the key words in it are
> "something like" in phrase "SQlite does something like this". Because
> SQLite is smart enough to choose smaller table as an outer one and
> bigger table as an inner one. Although it can choose other way round
> if you have index on Id in smaller table and don't have index on Id in
> bigger table. And in this case there's no performance hit in such
> decision, only benefit.
> 
> Pavel
> 
> On Fri, Oct 30, 2009 at 12:07 PM, Kristoffer Danielsson
>  wrote:
> >
> > Quote from: http://sqlite.phxsoftware.com/forums/p/1495/6629.aspx
> >
> >
> >
> > SQLite uses only nested loops to implement joins. Given a query like the 
> > following:
> >
> > SELECT ...
> > FROM OuterTable O INNER JOIN InnerTable I ON O.Id = I.Id
> >
> >
> >
> > SQlite does something like this:
> >
> >
> >
> > for each row in OuterTable
> >  Seek all rows in InnerTable where InnerTable.Id = OuterTable.Id
> > end for each
> >
> >
> >
> > I assume SQLite has not improved on JOIN precedence since then, which means 
> > that if OuterTable is HUGE, there will be an huge performance hit!
> >
> >
> >
> > "Some database engines like SQL Server decide which table is the outer 
> > table and which table is the inner table"
> >
> >
> > How do I simulate that behavior in SQLite? A misformed SQL statement from 
> > the user results in unacceptable lockups...
> >
> > _
> > Nya Windows 7 - Hitta en dator som passar dig! Mer information.
> > http://windows.microsoft.com/shop
> > ___
> > 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

  
_
Nya Windows 7 gör allt lite enklare. Hitta en dator som passar dig!
http://windows.microsoft.com/shop
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to decide which table is the outer table and which table is the inner table?

[Simon Slavin]

On 30 Oct 2009, at 4:07pm, Kristoffer Danielsson wrote:

> "Some database engines like SQL Server decide which table is the  
> outer table and which table is the inner table"
>
>
> How do I simulate that behavior in SQLite? A misformed SQL statement  
> from the user results in unacceptable lockups...

SQLite already makes that decision.  If you want to enforce which way  
it decides, you specify INNER JOIN or OUTER JOIN just as you do in  
most SQL engines:



Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to decide which table is the outer table and which table is the inner table?

[Pavel Ivanov]

Of course SQLite wasn't changed much in this part since November 2008
but the citation you gave is either wrong or the key words in it are
"something like" in phrase "SQlite does something like this". Because
SQLite is smart enough to choose smaller table as an outer one and
bigger table as an inner one. Although it can choose other way round
if you have index on Id in smaller table and don't have index on Id in
bigger table. And in this case there's no performance hit in such
decision, only benefit.

Pavel

On Fri, Oct 30, 2009 at 12:07 PM, Kristoffer Danielsson
 wrote:
>
> Quote from: http://sqlite.phxsoftware.com/forums/p/1495/6629.aspx
>
>
>
> SQLite uses only nested loops to implement joins. Given a query like the 
> following:
>
> SELECT ...
> FROM OuterTable O INNER JOIN InnerTable I ON O.Id = I.Id
>
>
>
> SQlite does something like this:
>
>
>
> for each row in OuterTable
>  Seek all rows in InnerTable where InnerTable.Id = OuterTable.Id
> end for each
>
>
>
> I assume SQLite has not improved on JOIN precedence since then, which means 
> that if OuterTable is HUGE, there will be an huge performance hit!
>
>
>
> "Some database engines like SQL Server decide which table is the outer table 
> and which table is the inner table"
>
>
> How do I simulate that behavior in SQLite? A misformed SQL statement from the 
> user results in unacceptable lockups...
>
> _
> Nya Windows 7 - Hitta en dator som passar dig! Mer information.
> http://windows.microsoft.com/shop
> ___
> 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] How to decide which table is the outer table and which table is the inner table?

[Kristoffer Danielsson]

Quote from: http://sqlite.phxsoftware.com/forums/p/1495/6629.aspx

 

SQLite uses only nested loops to implement joins. Given a query like the 
following:

SELECT ...
FROM OuterTable O INNER JOIN InnerTable I ON O.Id = I.Id

 

SQlite does something like this:

 

for each row in OuterTable
 Seek all rows in InnerTable where InnerTable.Id = OuterTable.Id
end for each

 

I assume SQLite has not improved on JOIN precedence since then, which means 
that if OuterTable is HUGE, there will be an huge performance hit!

 

"Some database engines like SQL Server decide which table is the outer table 
and which table is the inner table"


How do I simulate that behavior in SQLite? A misformed SQL statement from the 
user results in unacceptable lockups...
  
_
Nya Windows 7 - Hitta en dator som passar dig! Mer information.
http://windows.microsoft.com/shop
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users