Re: [sqlite] Query optimizer bug?

[James K. Lowden]

On Tue, 30 Apr 2013 12:59:17 -0400
Richard Hipp  wrote:

> http://www.sqlite.org/draft/queryplanner-ng.html

Feel free to use this version of the diagram.  

http://www.sqlite.org/draft/queryplanner-ng.html

The directory contains the source file and PDF, too.  

Commands are:

$ groff -p -ms -mpdf -Tpdf tcp-h-q8.ms  > tcp-h-q8.pdf
$ convert -trim -border 12x12 -bordercolor white  \
tcp-h-q8.pdf  tcp-h-q8.png

HTH.  

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

Re: [sqlite] Incorrect documentation for PRAGMA table_info (pk column)

[Richard Hipp]

On Tue, Apr 30, 2013 at 10:13 PM, Joey Adams wrote:

> The documentation for PRAGMA table_info says: The 'pk' column in the result
> set is zero for columns that are not part of the primary key, and is the
> index of the column in the primary key for columns that are part of the
> primary key."  But in reality, pk = 1 for all the primary key columns:
>

For SQLite 3.7.15 and earlier, the "pk" columns meaning was undocumented
and hence undefined.  As it happened it was always 1.  Beginning with
SQLite 3.7.16 we defined the meaning of the "pk" column to be the 1-based
index of the column in the primary key.


>
> SQLite version 3.7.15.2 2013-01-09 11:53:05
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> .mode column
> sqlite> .header on
> sqlite> CREATE TABLE foo (a INT, b INT, PRIMARY KEY(a,b));
> sqlite> PRAGMA table_info(foo);
> cid nametypenotnull dflt_value  pk
> --  --  --  --  --  --
> 0   a   INT 0   1
> 1   b   INT 0   1
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Incorrect documentation for PRAGMA table_info (pk column)

[Joey Adams]

The documentation for PRAGMA table_info says: The 'pk' column in the result
set is zero for columns that are not part of the primary key, and is the
index of the column in the primary key for columns that are part of the
primary key."  But in reality, pk = 1 for all the primary key columns:

SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .mode column
sqlite> .header on
sqlite> CREATE TABLE foo (a INT, b INT, PRIMARY KEY(a,b));
sqlite> PRAGMA table_info(foo);
cid nametypenotnull dflt_value  pk
--  --  --  --  --  --
0   a   INT 0   1
1   b   INT 0   1
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query optimizer bug?

[Richard Hipp]

On Tue, Apr 30, 2013 at 6:20 PM, Richard Hipp  wrote:

>
>
> On Tue, Apr 30, 2013 at 5:46 PM, Marc L. Allen <
> mlal...@outsitenetworks.com> wrote:
>
>> In looking at the draft plan... am I right in assuming that at any 'stop'
>> you can eliminate paths which have consumed the identical set of nodes but
>> are more expensive?
>>
>
> Yes.  Good idea.  I have updated the document accordingly.
>

Upon further reflection, the answer is really "maybe".  If one path is a
candidate for optimizing out the ORDER BY clause but the other is not, then
you still one to keep around the path that is the ORDER BY candidate even
if it is not the lowest cost.  In the example below, if there had been an
ORDER BY clause that was such that N1-R might be able to satisfy the ORDER
BY clause without sorting, then we would want to keep N1-R around even
though R-N1 has a lower cost at this step.

But that sort of thing happens rarely, and pruning by merging equivalent
paths will normally be a big win.


>
> Note that by merging equivalent plans, the optimal plan for TPC-H Q8 is
> found with N==10 instead of N==21.
>
>
>>
>> For instance, at stop 2, the draft shows:
>>
>> R-N1 (cost: 7.03)
>> N1-R (cost: 7.31)
>> R-N2 (cost: 9.08)
>> N2-R (cost: 9.08)
>>
>> Since the first two consume both R and N1, will N1-R ever be a better
>> path than R-N1?  If not, then it can be removed from the set, reducing the
>> amount of work later.  Similarly with R-N2 and N2-R.  Since they consume
>> the same nodes and have identical costs, does it matter which one is used?
>>  Won't any path R-N2-x-y-z.. be identical to N2-R-x-y-z?
>>
>> Maybe I'm over simplifying?
>>
>> Marc
>>
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org [mailto:
>> sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
>> Sent: Tuesday, April 30, 2013 5:34 PM
>> To: sqlite-users@sqlite.org
>> Subject: Re: [sqlite] Query optimizer bug?
>>
>> On 4/30/2013 5:26 PM, Ryan Johnson wrote:
>> > Being able to force certain access methods (use *this* index, not that
>> > one) would be helpful, though (does "+" do that or just suggest it?).
>>
>> Unary plus turns a simple column reference (for which an index can be
>> used) into an expression (which cannot be indexed). This may be used to
>> suppress the choice of a particular index, at which point the query planner
>> is likely to choose a different index, often rearranging the joins. So the
>> unary plus does often influence the query plan, but in a rather roundabout
>> way.
>> --
>> Igor Tandetnik
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>> This email and any attachments are only for use by the intended
>> recipient(s) and may contain legally privileged, confidential, proprietary
>> or otherwise private information. Any unauthorized use, reproduction,
>> dissemination, distribution or other disclosure of the contents of this
>> e-mail or its attachments is strictly prohibited. If you have received this
>> email in error, please notify the sender immediately and delete the
>> original.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query optimizer bug?

[Marc L. Allen]

Oops.. nevermind.  You already had that covered. ;)

That'll teach me to answer on my phone before reading the revised draft.


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of Richard Hipp [d...@sqlite.org]
Sent: Tuesday, April 30, 2013 6:20 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Query optimizer bug?

On Tue, Apr 30, 2013 at 5:46 PM, Marc L. Allen
wrote:

> In looking at the draft plan... am I right in assuming that at any 'stop'
> you can eliminate paths which have consumed the identical set of nodes but
> are more expensive?
>

Yes.  Good idea.  I have updated the document accordingly.

Note that by merging equivalent plans, the optimal plan for TPC-H Q8 is
found with N==10 instead of N==21.


>
> For instance, at stop 2, the draft shows:
>
> R-N1 (cost: 7.03)
> N1-R (cost: 7.31)
> R-N2 (cost: 9.08)
> N2-R (cost: 9.08)
>
> Since the first two consume both R and N1, will N1-R ever be a better path
> than R-N1?  If not, then it can be removed from the set, reducing the
> amount of work later.  Similarly with R-N2 and N2-R.  Since they consume
> the same nodes and have identical costs, does it matter which one is used?
>  Won't any path R-N2-x-y-z.. be identical to N2-R-x-y-z?
>
> Maybe I'm over simplifying?
>
> Marc
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
> Sent: Tuesday, April 30, 2013 5:34 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Query optimizer bug?
>
> On 4/30/2013 5:26 PM, Ryan Johnson wrote:
> > Being able to force certain access methods (use *this* index, not that
> > one) would be helpful, though (does "+" do that or just suggest it?).
>
> Unary plus turns a simple column reference (for which an index can be
> used) into an expression (which cannot be indexed). This may be used to
> suppress the choice of a particular index, at which point the query planner
> is likely to choose a different index, often rearranging the joins. So the
> unary plus does often influence the query plan, but in a rather roundabout
> way.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> This email and any attachments are only for use by the intended
> recipient(s) and may contain legally privileged, confidential, proprietary
> or otherwise private information. Any unauthorized use, reproduction,
> dissemination, distribution or other disclosure of the contents of this
> e-mail or its attachments is strictly prohibited. If you have received this
> email in error, please notify the sender immediately and delete the
> original.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query optimizer bug?

[Marc L. Allen]

Another thought... since you are limiting yourself to a maximum number of paths 
at any given time, if you're willing to take the full hit for maintaining the 
full N=30 (or whatever) paths, instead of simply eliminating worse paths with 
identical nodes, allow yourself to continue finding more paths to bring your 
total back up to 30.

For instance, in the example with N=4, and N1-R would be eliminated, you could 
take the '5th best' path to maintain your count of 4.  So, you'd be taking the 
best N paths that have a unique set of nodes.

I don't know if that will give enough bang for your buck, as the extra lower 
paths might rarely yield a final better result.

Marc


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of Richard Hipp [d...@sqlite.org]
Sent: Tuesday, April 30, 2013 6:20 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Query optimizer bug?

On Tue, Apr 30, 2013 at 5:46 PM, Marc L. Allen
wrote:

> In looking at the draft plan... am I right in assuming that at any 'stop'
> you can eliminate paths which have consumed the identical set of nodes but
> are more expensive?
>

Yes.  Good idea.  I have updated the document accordingly.

Note that by merging equivalent plans, the optimal plan for TPC-H Q8 is
found with N==10 instead of N==21.


>
> For instance, at stop 2, the draft shows:
>
> R-N1 (cost: 7.03)
> N1-R (cost: 7.31)
> R-N2 (cost: 9.08)
> N2-R (cost: 9.08)
>
> Since the first two consume both R and N1, will N1-R ever be a better path
> than R-N1?  If not, then it can be removed from the set, reducing the
> amount of work later.  Similarly with R-N2 and N2-R.  Since they consume
> the same nodes and have identical costs, does it matter which one is used?
>  Won't any path R-N2-x-y-z.. be identical to N2-R-x-y-z?
>
> Maybe I'm over simplifying?
>
> Marc
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
> Sent: Tuesday, April 30, 2013 5:34 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Query optimizer bug?
>
> On 4/30/2013 5:26 PM, Ryan Johnson wrote:
> > Being able to force certain access methods (use *this* index, not that
> > one) would be helpful, though (does "+" do that or just suggest it?).
>
> Unary plus turns a simple column reference (for which an index can be
> used) into an expression (which cannot be indexed). This may be used to
> suppress the choice of a particular index, at which point the query planner
> is likely to choose a different index, often rearranging the joins. So the
> unary plus does often influence the query plan, but in a rather roundabout
> way.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> This email and any attachments are only for use by the intended
> recipient(s) and may contain legally privileged, confidential, proprietary
> or otherwise private information. Any unauthorized use, reproduction,
> dissemination, distribution or other disclosure of the contents of this
> e-mail or its attachments is strictly prohibited. If you have received this
> email in error, please notify the sender immediately and delete the
> original.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query optimizer bug?

[Richard Hipp]

On Tue, Apr 30, 2013 at 5:46 PM, Marc L. Allen
wrote:

> In looking at the draft plan... am I right in assuming that at any 'stop'
> you can eliminate paths which have consumed the identical set of nodes but
> are more expensive?
>

Yes.  Good idea.  I have updated the document accordingly.

Note that by merging equivalent plans, the optimal plan for TPC-H Q8 is
found with N==10 instead of N==21.


>
> For instance, at stop 2, the draft shows:
>
> R-N1 (cost: 7.03)
> N1-R (cost: 7.31)
> R-N2 (cost: 9.08)
> N2-R (cost: 9.08)
>
> Since the first two consume both R and N1, will N1-R ever be a better path
> than R-N1?  If not, then it can be removed from the set, reducing the
> amount of work later.  Similarly with R-N2 and N2-R.  Since they consume
> the same nodes and have identical costs, does it matter which one is used?
>  Won't any path R-N2-x-y-z.. be identical to N2-R-x-y-z?
>
> Maybe I'm over simplifying?
>
> Marc
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
> Sent: Tuesday, April 30, 2013 5:34 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Query optimizer bug?
>
> On 4/30/2013 5:26 PM, Ryan Johnson wrote:
> > Being able to force certain access methods (use *this* index, not that
> > one) would be helpful, though (does "+" do that or just suggest it?).
>
> Unary plus turns a simple column reference (for which an index can be
> used) into an expression (which cannot be indexed). This may be used to
> suppress the choice of a particular index, at which point the query planner
> is likely to choose a different index, often rearranging the joins. So the
> unary plus does often influence the query plan, but in a rather roundabout
> way.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> This email and any attachments are only for use by the intended
> recipient(s) and may contain legally privileged, confidential, proprietary
> or otherwise private information. Any unauthorized use, reproduction,
> dissemination, distribution or other disclosure of the contents of this
> e-mail or its attachments is strictly prohibited. If you have received this
> email in error, please notify the sender immediately and delete the
> original.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query optimizer bug?

[Marc L. Allen]

In looking at the draft plan... am I right in assuming that at any 'stop' you 
can eliminate paths which have consumed the identical set of nodes but are more 
expensive?

For instance, at stop 2, the draft shows:

R-N1 (cost: 7.03)
N1-R (cost: 7.31)
R-N2 (cost: 9.08)
N2-R (cost: 9.08)

Since the first two consume both R and N1, will N1-R ever be a better path than 
R-N1?  If not, then it can be removed from the set, reducing the amount of work 
later.  Similarly with R-N2 and N2-R.  Since they consume the same nodes and 
have identical costs, does it matter which one is used?  Won't any path 
R-N2-x-y-z.. be identical to N2-R-x-y-z?

Maybe I'm over simplifying? 

Marc

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Igor Tandetnik
Sent: Tuesday, April 30, 2013 5:34 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Query optimizer bug?

On 4/30/2013 5:26 PM, Ryan Johnson wrote:
> Being able to force certain access methods (use *this* index, not that 
> one) would be helpful, though (does "+" do that or just suggest it?).

Unary plus turns a simple column reference (for which an index can be
used) into an expression (which cannot be indexed). This may be used to 
suppress the choice of a particular index, at which point the query planner is 
likely to choose a different index, often rearranging the joins. So the unary 
plus does often influence the query plan, but in a rather roundabout way.
--
Igor Tandetnik

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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query optimizer bug?

[Igor Tandetnik]

On 4/30/2013 5:26 PM, Ryan Johnson wrote:

Being able to force certain access methods (use *this* index, not that one) 
would be helpful, though
(does "+" do that or just suggest it?).


Unary plus turns a simple column reference (for which an index can be 
used) into an expression (which cannot be indexed). This may be used to 
suppress the choice of a particular index, at which point the query 
planner is likely to choose a different index, often rearranging the 
joins. So the unary plus does often influence the query plan, but in a 
rather roundabout way.

--
Igor Tandetnik

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

Re: [sqlite] Query optimizer bug?

[Ryan Johnson]

On 30/04/2013 5:20 PM, Nico Williams wrote:

On Tue, Apr 30, 2013 at 11:59 AM, Richard Hipp  wrote:

http://www.sqlite.org/draft/queryplanner-ng.html

That's quite interesting.

Should the user have a way to influence the query planner?  Perhaps by
indicating a cost for each table source?  SQL is supposed to let the
RDBMS pick the best query plan, but the RDBMS can't always pick the
best plan... in such cases users like to have a way to dictate the
plan.  And/or the RDBMS could keep state about different plans tried
in the past and learn to pick better plans, but this sounds very
complicated.
My experience with cross joins and TPCH Q8 suggests that in cases like 
this the user's not necessarily going to pick a good join ordering, 
either. At least I had bad luck with it. Being able to force certain 
access methods (use *this* index, not that one) would be helpful, though 
(does "+" do that or just suggest it?).


Ryan

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

Re: [sqlite] Query optimizer bug?

[Nico Williams]

On Tue, Apr 30, 2013 at 11:59 AM, Richard Hipp  wrote:
> http://www.sqlite.org/draft/queryplanner-ng.html

That's quite interesting.

Should the user have a way to influence the query planner?  Perhaps by
indicating a cost for each table source?  SQL is supposed to let the
RDBMS pick the best query plan, but the RDBMS can't always pick the
best plan... in such cases users like to have a way to dictate the
plan.  And/or the RDBMS could keep state about different plans tried
in the past and learn to pick better plans, but this sounds very
complicated.

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

Re: [sqlite] Query optimizer bug?

[Richard Hipp]

On Tue, Apr 30, 2013 at 4:35 PM, Ryan Johnson
wrote:

> On 30/04/2013 12:59 PM, Richard Hipp wrote:
>
>> On Thu, Mar 14, 2013 at 2:07 PM, Ryan Johnson
>> **wrote:
>>
>>  Hi all,
>>>
>>> I'm running sqlite-3.7.13 on cygwin. Playing around with various TPC-H
>>> queries with my class recently, I hit a strangely slow query and don't
>>> understand why it's so slow.
>>>
>>>  
>>> http://www.sqlite.org/draft/**queryplanner-ng.html
>>
> Nice. If you're willing to do quadratic work anyway, would it make sense
> to try and establish upper/lower bounds on the optimal solution to decide
> how much harder to work? I believe it's possible to find lower bounds for
> TSP using minimum spanning trees, and that computing the MST should be
> log-linear in the number of joins for most queries; if the "easy" NN=1
> solution isn't too much higher than the lowest of a random selection of
> upper bounds, it's probably safe to stop looking; if the best upper bound
> is a *lot* lower (like the NN=1 result for Q8 being 14,000 times slower
> than the optimal), it probably justifies investing more time with an NNN
> search for that particular query, in hopes of bringing down the predicted
> runtime; the expected time difference could even be used to set the NNN
> factor...
>


Yes - there are countless tricks like this you can use to make it go
faster.  Be assured that I will be trying them all out.


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query optimizer bug?

[Ryan Johnson]

On 30/04/2013 12:59 PM, Richard Hipp wrote:

On Thu, Mar 14, 2013 at 2:07 PM, Ryan Johnson
wrote:


Hi all,

I'm running sqlite-3.7.13 on cygwin. Playing around with various TPC-H
queries with my class recently, I hit a strangely slow query and don't
understand why it's so slow.


http://www.sqlite.org/draft/queryplanner-ng.html
Nice. If you're willing to do quadratic work anyway, would it make sense 
to try and establish upper/lower bounds on the optimal solution to 
decide how much harder to work? I believe it's possible to find lower 
bounds for TSP using minimum spanning trees, and that computing the MST 
should be log-linear in the number of joins for most queries; if the 
"easy" NN=1 solution isn't too much higher than the lowest of a random 
selection of upper bounds, it's probably safe to stop looking; if the 
best upper bound is a *lot* lower (like the NN=1 result for Q8 being 
14,000 times slower than the optimal), it probably justifies investing 
more time with an NNN search for that particular query, in hopes of 
bringing down the predicted runtime; the expected time difference could 
even be used to set the NNN factor...


Thoughts?
Ryan

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

Re: [sqlite] Support SQLite in México

[Jeff Archer]

>Francisco Puente Moreno fpm.mty74 at hotmail.com
>Mon Apr 29 11:46:05 EDT 2013
>
>Hello, I'm working for a Mexican company and we are thinking use SQLite
for some develops, but >we need to know if there is any company in México
that give direct support to SQLite if we have a >contingency.
>Thanks in advance!
>Regards!
>Francisco Puente, Eng.

I would recommend that you follow this list for a few weeks while building
a sample app using SQLite and using this list for support.  You will find
the support to be top-notch.  I have paid for support from companies and
gotten far less.  I have been using SQLite3 in my product since 2009.
 Highly recommended.


Jeff Archer
Vice President of Software Engineering
Nanotronics Imaging
jsarc...@nanotronicsimaging.com
<330>819.4615
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query optimizer bug?

[Richard Hipp]

On Thu, Mar 14, 2013 at 2:07 PM, Ryan Johnson
wrote:

> Hi all,
>
> I'm running sqlite-3.7.13 on cygwin. Playing around with various TPC-H
> queries with my class recently, I hit a strangely slow query and don't
> understand why it's so slow.
>

http://www.sqlite.org/draft/queryplanner-ng.html

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE and GLOB questions

[Richard Hipp]

On Tue, Apr 30, 2013 at 12:52 PM, Staffan Tylen wrote:

> I have the following two questions to share:
>
> First, assume two tables t1 and t2 where t1 has a text column a with data
> and t2 has a text column p with patterns in LIKE format. For each a in t1 I
> want to find all matching patterns p in t2. Is this possible using a single
> SELECT clause? I've been unable to come up with the expression on the
> right-hand side of LIKE to make this work.
>

SELECT p FROM t1, t2 WHERE t1.a LIKE t2.p;


>
> Second, not having much *NIX knowledge, what's the difference between LIKE
> and GLOB apart from the masking characters and case-sensitivity? The
> documentation doesn't provide much information and Wikipedia shows that
> there are many 'standards' out there. How does GLOB work in SQLite? One or
> two examples of how LIKE and GLOB operate differently would be very
> helpful.
>

LIKE is case insensitive and uses wildcards '%' and '_'

GLOB is case sensitive and uses wildcards '*' and '?'.

GLOB also allows you to say '[abcd]' to mean any character in the set of
"abcd".

LIKE can have an option ESCAPE character for escaping wildcards.  GLOB
cannot.  But with GLOB if you want to match a wildcard character you can
use '[*]' or '[?]'.

Other than that they are the same.  In fact, GLOB and LIKE are implemented
using same subroutine, called with different parameters that determine the
wildcards and case sensitivity.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE and GLOB questions

[Clemens Ladisch]

Staffan Tylen wrote:
> First, assume two tables t1 and t2 where t1 has a text column a with data
> and t2 has a text column p with patterns in LIKE format. For each a in t1 I
> want to find all matching patterns p in t2. Is this possible using a single
> SELECT clause?

SELECT * FROM t1 JOIN t2 ON t1.a LIKE t2.p

> Second, not having much *NIX knowledge, what's the difference between LIKE
> and GLOB apart from the masking characters and case-sensitivity?

Character classes.

A comment hidden in the source code explains:

** Globbing rules:
**
**  '*'   Matches any sequence of zero or more characters.
**
**  '?'   Matches exactly one character.
**
** [...]  Matches one character from the enclosed list of
**characters.
**
** [^...] Matches one character not in the enclosed list.
**
** With the [...] and [^...] matching, a ']' character can be included
** in the list by making it the first character after '[' or '^'.  A
** range of characters can be specified using '-'.  Example:
** "[a-z]" matches any single lower-case letter.  To match a '-', make
** it the last character in the list.
**
** This routine is usually quick, but can be N**2 in the worst case.
**
** Hints: to match '*' or '?', put them in "[]".  Like this:
**
** abc[*]xyzMatches "abc*xyz" only


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

[sqlite] LIKE and GLOB questions

[Staffan Tylen]

I have the following two questions to share:

First, assume two tables t1 and t2 where t1 has a text column a with data
and t2 has a text column p with patterns in LIKE format. For each a in t1 I
want to find all matching patterns p in t2. Is this possible using a single
SELECT clause? I've been unable to come up with the expression on the
right-hand side of LIKE to make this work.

Second, not having much *NIX knowledge, what's the difference between LIKE
and GLOB apart from the masking characters and case-sensitivity? The
documentation doesn't provide much information and Wikipedia shows that
there are many 'standards' out there. How does GLOB work in SQLite? One or
two examples of how LIKE and GLOB operate differently would be very helpful.

Many thanks in advance,
Staffan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Support SQLite in México

[ajm]

Hi Francisco:

I can tell you that this list, is plenty of very clever people who sure can 
help you if in trouble -supposing you know how to ask-.  Usually here a an 
incredible technical level and service intention for free.  But as a last 
resource, you can get professional support from the guy who invented the 
library.

Take a loot to http://www.sqlite.org/support.html

HTH

--
Adolfo
Zator Systems.

>
>  Mensaje original 
> De: Francisco Puente Moreno 
> Para:  "sqlite-users@sqlite.org" 
> Fecha:  Tue, 30 Apr 2013 14:30:22 +0200
> Asunto:  [sqlite] Support SQLite in México
>
> 
>Hello, I'm working for a Mexican company and we are thinking use SQLite for 
>some develops, but we need to know if there is any company in México that give 
>direct support to SQLite if we have a contingency.
>Thanks in advance!
>Regards!
>Francisco Puente, Eng.   


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

Re: [sqlite] Performance HELP

[Eduardo Morras]

On Mon, 29 Apr 2013 11:08:48 -0400
"peter korinis"  wrote:

> This question regards SQLite (3.6) performance. (Lengthy because I want to
> describe the environment.)
> 
> . Win-7 (64-bit, though I don't know if SQLite uses 64-bit). 
> 
> . 3 year old HP laptop with Intel Core Duo CPU P8400 @ 2.27GHz with
> only 4GB memory
> 
> . 286GB HD (50% full) + 1TB 7200rpm external eSata HD (90% free) -
> contains target SQLite DB.
> 
> . Target DB is single user, read-only, static . as follows
> 
> o   DB size = 24GB: in 8 tables. 2 most used tables contain 43million rows
> and 86 million rows - others are small look-up tables with 50-10,000 rows.
> 1-3 indices per table.
> 
> o   imported from 44GB CSV file with 45million rows of 600 columns
> 
> o   Used for: SQL query only (CLI or GUI), no updates, no deletes, no
> inserts, or no transactions, no apps.
> 
> . Future: Double the size . intend to clone existing DB and populate
> with another year's data. Additional years will likely be added at later
> time.
> 
> Now to the questions:
> 
> 1.   Is my current DB too large for SQLite to handle efficiently? I just
> read in O'Reilly, Using SQLite, book, "If you need to store and process
> several gigabytes or more of data, it might be wise to consider a more
> performance-oriented product."

Nowadays this decission is taken (in my opinion) if the database needs to feed 
a lot of users. The data size is not a priority.

> 
> 2.   Adding which resources would most improve performance ???  (Many
> queries, like a SELECT COUNT (and GROUP) of indexed column on a JOIN of the
> 2 large tables may take 30-60 or more minutes.)

Split data in 2 or more databases and attach them. They can reside on different 
disks. You can partition tables too, by date for example and use UNION on the 
queries.

You can build a temporal table with queries and its results or calculated 
counts. Do a select in this temp table searching for previous queries. You say 
that data is read only, it should work.

> 
> a.   add 4GB or 12GB more memory ?
>
> b.  get faster HD for target DB . ext. eSATA SSD (say 256GB) ?
> 
> c.   get faster ext. eSATA 10,000rpm HD for DB ?
> 
> d.  make performance changes to current DB settings ? (or learn to write
> better SQL ??)

Write better sql has two sides. It's about write better selects and find a 
better schema.
 
> e.  convert from SQLite to MySQL or something else? I like the
> simplicity and low admin or SQLite - so I prefer not to switch DBMS

If i need to upgrade dbms, i use Postgres. But i don't think you need it.

> f.Something else ?

Reread Simon's email and sail the documentation and test.

> Thank you so much for your help.
> 
> peterK

HTH

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

Re: [sqlite] Support SQLite in México

[Stephen Chrzanowski]

SQLite is something you add into an application that you're building.  It
isn't something like MySQL, MSSQL, Oracle, or anything like a client/server
architecture.  It is a library that your code links to either via DLL or
other type of library.  Consider it for light use web services, desktop
applications, tablet/phones, etc.

Support basically is world wide, free, but, pretty much only in this
mailing list.


On Mon, Apr 29, 2013 at 11:46 AM, Francisco Puente Moreno <
fpm.mt...@hotmail.com> wrote:

> Hello, I'm working for a Mexican company and we are thinking use SQLite
> for some develops, but we need to know if there is any company in México
> that give direct support to SQLite if we have a contingency.
> Thanks in advance!
> Regards!
> Francisco Puente, Eng.
> ___
> 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] Support SQLite in México

[Simon Slavin]

On 29 Apr 2013, at 4:46pm, Francisco Puente Moreno  
wrote:

> Hello, I'm working for a Mexican company and we are thinking use SQLite for 
> some develops, but we need to know if there is any company in México that 
> give direct support to SQLite if we have a contingency.



SQLite is a very simple thing.  It's freeware.  It's a tool for programming 
rather than a compiled app.  It involves no server/client setup.  So 'support 
for SQLite' is normally in the form of advice to a programmer who wants to use 
it.  There's never any reason to send an engineer out to a site.  And with your 
good English I don't think you need support specifically in Mexican Spanish.

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

Re: [sqlite] Performance HELP

[Simon Slavin]

On 29 Apr 2013, at 4:08pm, peter korinis  wrote:

Thanks for your detailed description of your use of SQLite which saved a lot of 
questions.

> 1.   Is my current DB too large for SQLite to handle efficiently? I just
> read in O'Reilly, Using SQLite, book, "If you need to store and process
> several gigabytes or more of data, it might be wise to consider a more
> performance-oriented product."

I don't think so.  We have never arrived at any rule-of-thumb like "If your 
database is more than 20Gig, you should probably think about Progres instead.".

> 2.   Adding which resources would most improve performance ???  (Many
> queries, like a SELECT COUNT (and GROUP) of indexed column on a JOIN of the
> 2 large tables may take 30-60 or more minutes.)

Think carefully about what indexes you have.  Indexes on a single column are 
often a waste of time and space.  An index should satisfy the demands of a 
SELECT (WHERE, ORDER BY, and JOIN CLAUSES) or the WHERE clause of an UPDATE.  
People who create databases tend to just index all 'important' columns without 
thinking through when each index would be used and how it could be improved.

I really need to write my 'SQL indexes for fun and profit' web pages, don't I ?

> a.   add 4GB or 12GB more memory ?
> 
> b.  get faster HD for target DB . ext. eSATA SSD (say 256GB) ?

A lot of these depend on how the database is consulted.  If your user tends to 
concentrate on a small cluster of data for a long time then move on, caching is 
good.  If your user tends to span almost all of the database, or to hop 
frequently from one part to another, caching is not important.

> c.   get faster ext. eSATA 10,000rpm HD for DB ?

Many desktop computers have really terrible external bus speeds these days.  
Without something like Thunderbolt you might lose a lot of that speed just by 
trying to funnel so much data through the connection.  A faster internal hard 
disk is a different matter.

> d.  make performance changes to current DB settings ? (or learn to write
> better SQL ??)

See my comments on 2.  Open the database in read-only mode.  Since your 
database is never edited, you can try some PRAGMAs which kill the ACID 
abilities of SQLite (turn synchrony off ?) though I have no idea if these will 
actually speed things up for your particular solution.

There are a few optimizations which have no effect on other SQL engines but are 
great for SQLite.  For instance, COUNT(*) and COUNT(rowid) do identical things 
but in SQLite the former is far faster.  I'm not sure how you'd find these 
things out: I don't know of anyone who has collected them.

> f.Something else ?

Fewer but bigger transactions are good.  Even when you're just doing a bunch of 
SELECTs, combining them in one transaction speeds things up.

And lastly, something that involves spending money, but you can get free expert 
opinion on whether it will help before you buy.  Your description seems to suit 
the Compressed and Encrypted Read-Only  extension



down to the ground.  Having the database compressed means that all the raw 
data-handling parts of your application are reading less data and moving less 
data around.  And the Hwaci company is run by the main author of SQLite.  You 
couldn't wish to get a more expert opinion.  If Dr Hipp or another of the team 
doesn't comment on this thread you could email the address on that site and ask.

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

[sqlite] Segmentation fault in SQLite parser

[Eelco Dolstra]

Hi,

I ran into the following regression after upgrading from SQLite 3.7.14.1 to
3.7.16.2: the SQLite parser crashes when it encounters a subquery enclosed in
double parentheses.

For example:

$ sqlite3
SQLite version 3.7.16.2 2013-04-12 11:52:43
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from ((select * from x)) y;
Segmentation fault

(It doesn't matter whether table "x" exists or not.)  On SQLite 3.7.14.1, this
worked correctly.  The problem can be fixed by removing the redundant
parentheses, but the parser shouldn't segfault when encountering this.

The stack trace is as follows:

> #0  0x77b7b0b3 in selectExpander (pWalker=0x7fffb4a0, p=0x620278) 
> at sqlite3.c:98052
> #1  0x77b4f69b in sqlite3WalkSelect (pWalker=0x7fffb4a0, 
> p=0x620278) at sqlite3.c:72631
> #2  0x77b7baf1 in sqlite3SelectExpand (pParse=0x620808, 
> pSelect=0x620278) at sqlite3.c:98298
> #3  0x77b7bc8d in sqlite3SelectPrep (pParse=0x620808, p=0x620278, 
> pOuterNC=0x0) at sqlite3.c:98383
> #4  0x77b7c44a in sqlite3Select (pParse=0x620808, p=0x620278, 
> pDest=0x7fffb7f0) at sqlite3.c:98648
> #5  0x77b8f719 in yy_reduce (yypParser=0x620ab8, yyruleno=112) at 
> sqlite3.c:111032
> #6  0x77b926b2 in sqlite3Parser (yyp=0x620ab8, yymajor=1, 
> yyminor=..., pParse=0x620808) at sqlite3.c:112034
> #7  0x77b933ef in sqlite3RunParser (pParse=0x620808, zSql=0x60f530 
> "select * from ((select * from x)) y;", pzErrMsg=0x7fffbaa0) at 
> sqlite3.c:112859
> #8  0x77b74436 in sqlite3Prepare (db=0x60f568, zSql=0x60f530 "select 
> * from ((select * from x)) y;", nBytes=-1, saveSqlFlag=1, pReprepare=0x0, 
> ppStmt=0x7fffbc08, 
> pzTail=0x7fffbc00) at sqlite3.c:94461
> #9  0x77b74772 in sqlite3LockAndPrepare (db=0x60f568, zSql=0x60f530 
> "select * from ((select * from x)) y;", nBytes=-1, saveSqlFlag=1, pOld=0x0, 
> ppStmt=0x7fffbc08, 
> pzTail=0x7fffbc00) at sqlite3.c:94553
> #10 0x77b74929 in sqlite3_prepare_v2 (db=0x60f568, zSql=0x60f530 
> "select * from ((select * from x)) y;", nBytes=-1, ppStmt=0x7fffbc08, 
> pzTail=0x7fffbc00) at sqlite3.c:94629
> #11 0x00404b51 in shell_exec (db=0x60f568, zSql=0x60f530 "select * 
> from ((select * from x)) y;", xCallback=0x403038 , 
> pArg=0x7fffbd40, pzErrMsg=0x7fffbce8)
> at shell.c:1144
> #12 0x00409dd9 in process_input (p=0x7fffbd40, in=0x0) at 
> shell.c:2728
> #13 0x0040b06b in main (argc=1, argv=0x7fffd3d8) at shell.c:3189

This is on NixOS Linux, 64-bit, GCC 4.6.3 and Glibc 2.17. Also confirmed with
the 2013-04-26 SQLite snapshot.

-- 
Eelco Dolstra | LogicBlox, Inc. | http://nixos.org/~eelco/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Support SQLite in México

[Francisco Puente Moreno]

Hello, I'm working for a Mexican company and we are thinking use SQLite for 
some develops, but we need to know if there is any company in México that give 
direct support to SQLite if we have a contingency.
Thanks in advance!
Regards!
Francisco Puente, Eng.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Performance HELP

[peter korinis]

This question regards SQLite (3.6) performance. (Lengthy because I want to
describe the environment.)

. Win-7 (64-bit, though I don't know if SQLite uses 64-bit). 

. 3 year old HP laptop with Intel Core Duo CPU P8400 @ 2.27GHz with
only 4GB memory

. 286GB HD (50% full) + 1TB 7200rpm external eSata HD (90% free) -
contains target SQLite DB.

. Target DB is single user, read-only, static . as follows

o   DB size = 24GB: in 8 tables. 2 most used tables contain 43million rows
and 86 million rows - others are small look-up tables with 50-10,000 rows.
1-3 indices per table.

o   imported from 44GB CSV file with 45million rows of 600 columns

o   Used for: SQL query only (CLI or GUI), no updates, no deletes, no
inserts, or no transactions, no apps.

. Future: Double the size . intend to clone existing DB and populate
with another year's data. Additional years will likely be added at later
time.

Now to the questions:

1.   Is my current DB too large for SQLite to handle efficiently? I just
read in O'Reilly, Using SQLite, book, "If you need to store and process
several gigabytes or more of data, it might be wise to consider a more
performance-oriented product."

2.   Adding which resources would most improve performance ???  (Many
queries, like a SELECT COUNT (and GROUP) of indexed column on a JOIN of the
2 large tables may take 30-60 or more minutes.)

a.   add 4GB or 12GB more memory ?

b.  get faster HD for target DB . ext. eSATA SSD (say 256GB) ?

c.   get faster ext. eSATA 10,000rpm HD for DB ?

d.  make performance changes to current DB settings ? (or learn to write
better SQL ??)

e.  convert from SQLite to MySQL or something else? I like the
simplicity and low admin or SQLite - so I prefer not to switch DBMS

f.Something else ?

Thank you so much for your help.

peterK

 

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

Re: [sqlite] Potential problem in SQLite 3.7.16.2

[Richard Hipp]

On Tue, Apr 30, 2013 at 3:34 AM, Mario M. Westphal  wrote:

> Hi,
>
> I just tried this (sorry dor the delay) but apparently I'm not having the
> right toolset installed.
>

Please download the latest amalgamations from
http://www.sqlite.org/draft/download.html


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Potential problem in SQLite 3.7.16.2

[Mario M. Westphal]

Hi,

I just tried this (sorry dor the delay) but apparently I'm not having the
right toolset installed.
The make file requires gawk.exe (which I downloaded from sourceforge), but
now it's complaing about a missing tclsh85...

Since the previous SQLite version works fine I think I'll skip this and wait
for the next official release. If the error still persists in that release,
I'll post again.

Thanks for your support.

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