Re: [sqlite] How does SQLite treat repeated expressions?

[P Kishor]

On Sun, Oct 25, 2009 at 10:56 AM, Kristoffer Danielsson
<kristoffer.daniels...@live.se> wrote:
>
> Sure I can test this specific case, but my question is still relevant. Does 
> SQLite or does it not optimize repeated expressions?
>
>

You are correct. Stated as above, your question is very valid as it is
trying to ascertain the way SQLite works internally, which may be
known to a few of the developers.


>
> Am I supposed to brute-force all possible expression repetitions to find it 
> out, when a simple "yes" or "no" from anybody - who knows about the inner 
> workings of SQLite - would suffice?
>
>> Date: Sun, 25 Oct 2009 10:08:01 -0500
>> From: punk.k...@gmail.com
>> To: sqlite-users@sqlite.org
>> Subject: Re: [sqlite] How does SQLite treat repeated expressions?
>>
>> On Sun, Oct 25, 2009 at 9:49 AM, Igor Tandetnik <itandet...@mvps.org> wrote:
>> > Kristoffer Danielsson wrote:
>> >> A: SELECT COUNT(DataID) FROM Data GROUP BY DataID ORDER BY COUNT(DataID);
>> >>
>> >> B: SELECT COUNT(DataID) AS X FROM Data GROUP BY DataID ORDER BY X;
>> >>
>> >> Is statement B faster than A? Why?
>> >
>> > Last time I checked, SQLite didn't perform any kind of common 
>> > subexpression elimitation. Its expression evaluator was very 
>> > simple-minded, closely following the syntax tree.
>> >
>> > Things might have improved since then, but I wouldn't be surprised if 
>> > statement A calls count() twice per row while statement B only once. I 
>> > also think that any resulting difference in performance is likely to be 
>> > immeasurably small. Why don't you test it and find out, if you are really 
>> > curious?
>> >
>>
>>
>> +1 re "Why don't you test it and find out, if you are really curious?"
>>
>> While I can certainly understand a question of the sort,
>>
>> "I did A and I did B, and I found B to be faster than A; why?"
>>
>> I really can't understand the point of questions such as,
>>
>> "Here is A and B; which one will be faster?"
>>
>> since the best person to answer the latter question is the person
>> posing the question -- just test it and find out.
>>
>> Now, it could be that implementing A or B just for testing purposes
>> might be so difficult that the poster might want to get a sense
>> *before* diving into the test. That is understandable, but,
>> implementing something based solely on the unmeasured advice of others
>> is itself questionable.
>>
>>
>>
>>
>> --
>> Puneet Kishor http://www.punkish.org
>> Carbon Model http://carbonmodel.org
>> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
>> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
>> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
>> ---
>> Assertions are politics; backing up assertions with evidence is science
>> ===
>> Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How does SQLite treat repeated expressions?

[Kristoffer Danielsson]

Sure I can test this specific case, but my question is still relevant. Does 
SQLite or does it not optimize repeated expressions?

 

Am I supposed to brute-force all possible expression repetitions to find it 
out, when a simple "yes" or "no" from anybody - who knows about the inner 
workings of SQLite - would suffice?
 
> Date: Sun, 25 Oct 2009 10:08:01 -0500
> From: punk.k...@gmail.com
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] How does SQLite treat repeated expressions?
> 
> On Sun, Oct 25, 2009 at 9:49 AM, Igor Tandetnik <itandet...@mvps.org> wrote:
> > Kristoffer Danielsson wrote:
> >> A: SELECT COUNT(DataID) FROM Data GROUP BY DataID ORDER BY COUNT(DataID);
> >>
> >> B: SELECT COUNT(DataID) AS X FROM Data GROUP BY DataID ORDER BY X;
> >>
> >> Is statement B faster than A? Why?
> >
> > Last time I checked, SQLite didn't perform any kind of common subexpression 
> > elimitation. Its expression evaluator was very simple-minded, closely 
> > following the syntax tree.
> >
> > Things might have improved since then, but I wouldn't be surprised if 
> > statement A calls count() twice per row while statement B only once. I also 
> > think that any resulting difference in performance is likely to be 
> > immeasurably small. Why don't you test it and find out, if you are really 
> > curious?
> >
> 
> 
> +1 re "Why don't you test it and find out, if you are really curious?"
> 
> While I can certainly understand a question of the sort,
> 
> "I did A and I did B, and I found B to be faster than A; why?"
> 
> I really can't understand the point of questions such as,
> 
> "Here is A and B; which one will be faster?"
> 
> since the best person to answer the latter question is the person
> posing the question -- just test it and find out.
> 
> Now, it could be that implementing A or B just for testing purposes
> might be so difficult that the poster might want to get a sense
> *before* diving into the test. That is understandable, but,
> implementing something based solely on the unmeasured advice of others
> is itself questionable.
> 
> 
> 
> 
> -- 
> Puneet Kishor http://www.punkish.org
> Carbon Model http://carbonmodel.org
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> ---
> Assertions are politics; backing up assertions with evidence is science
> ===
> Sent from Madison, WI, United States
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
_
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

Re: [sqlite] How does SQLite treat repeated expressions?

[P Kishor]

On Sun, Oct 25, 2009 at 9:49 AM, Igor Tandetnik  wrote:
> Kristoffer Danielsson wrote:
>> A: SELECT COUNT(DataID) FROM Data GROUP BY DataID ORDER BY COUNT(DataID);
>>
>> B: SELECT COUNT(DataID) AS X FROM Data GROUP BY DataID ORDER BY X;
>>
>> Is statement B faster than A? Why?
>
> Last time I checked, SQLite didn't perform any kind of common subexpression 
> elimitation. Its expression evaluator was very simple-minded, closely 
> following the syntax tree.
>
> Things might have improved since then, but I wouldn't be surprised if 
> statement A calls count() twice per row while statement B only once. I also 
> think that any resulting difference in performance is likely to be 
> immeasurably small. Why don't you test it and find out, if you are really 
> curious?
>


+1 re "Why don't you test it and find out, if you are really curious?"

While I can certainly understand a question of the sort,

"I did A and I did B, and I found B to be faster than A; why?"

I really can't understand the point of questions such as,

"Here is A and B; which one will be faster?"

since the best person to answer the latter question is the person
posing the question -- just test it and find out.

Now, it could be that implementing A or B just for testing purposes
might be so difficult that the poster might want to get a sense
*before* diving into the test. That is understandable, but,
implementing something based solely on the unmeasured advice of others
is itself questionable.




-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How does SQLite treat repeated expressions?

[Igor Tandetnik]

Kristoffer Danielsson wrote:
> A: SELECT COUNT(DataID) FROM Data GROUP BY DataID ORDER BY COUNT(DataID);
> 
> B: SELECT COUNT(DataID) AS X FROM Data GROUP BY DataID ORDER BY X;
> 
> Is statement B faster than A? Why?

Last time I checked, SQLite didn't perform any kind of common subexpression 
elimitation. Its expression evaluator was very simple-minded, closely following 
the syntax tree.

Things might have improved since then, but I wouldn't be surprised if statement 
A calls count() twice per row while statement B only once. I also think that 
any resulting difference in performance is likely to be immeasurably small. Why 
don't you test it and find out, if you are really curious?

Igor Tandetnik

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

[sqlite] How does SQLite treat repeated expressions?

[Kristoffer Danielsson]

Consider the two statements below.

 

A: SELECT COUNT(DataID) FROM Data GROUP BY DataID ORDER BY COUNT(DataID);

B: SELECT COUNT(DataID) AS X FROM Data GROUP BY DataID ORDER BY X;

 

Is statement B faster than A? Why?
  
_
Hitta hetaste singlarna på MSN Dejting!
http://dejting.se.msn.com/channel/index.aspx?trackingid=1002952
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users