Re: [sqlite] use of parentheses when not arithmetically or logically required

[John Machin]

On 19/08/2009 11:26 AM, Simon Slavin wrote:

> DRH's post trumps mine, of course.  I'm surprised to find that  
> brackets are optimised out of WHERE evaluations. 

Why? In the OP's example (all AND operators) the parentheses are 
redundant. In SQL, AND and OR are not guaranteed to be short-circuited; 
both operands may be evaluated. In a case like (a+b)*(c+d) the 
parentheses are necessary otherwise a+b*c+d would be interpreted as 
a+(b*c)+d. In parsing any expression in any language, one would expect 
unless documented otherwise that there was no other guarantee of order 
of evaluation -- if your expression has side-effects you are on your 
own. Generally parentheses are ignored immediately their purpose 
(overriding operator precedence) has been fulfilled.


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

Re: [sqlite] use of parentheses when not arithmetically or logically required

[Simon Slavin]

On 19 Aug 2009, at 12:01am, yaconsult wrote:

> Simon,
>
> Thank you for the optimization.  I'll switch to using between.  This
> particular database is used for generating some statistics and  
> generating
> reports, so performance is not that big an issue.

DRH's post trumps mine, of course.  I'm surprised to find that  
brackets are optimised out of WHERE evaluations.  In my defence I did  
mention EXPLAIN, which is the way to find this stuff out for yourself.

> When you talk about "clever use of an index", are you referring to a
> combined index or merely indices on each column?

SQLite is good at working out the minimum amount of work to do to find  
your results.  I thought that your second form would give it more  
leeway to be clever than the one with brackets.  But it turns out I  
was wrong.

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

Re: [sqlite] use of parentheses when not arithmetically or logically required

[D. Richard Hipp]

On Aug 18, 2009, at 5:26 PM, yaconsult wrote:
>
> My question is if it makes a difference to use parentheses when not
> logically required, as in the following example where the only  
> logical used
> is "and":
>
>select * from log
>where
>  (response >= 200 and response < 300)
>  and
>  (port >= 8444 and port <= 8459)
>
> Is there any difference in execution or performance if the  
> parentheses are
> present or not?  I'm guessing not, but would like confirmation.
>
>select * from log
>where
>  response >= 200
>  and response < 300
>  and port >= 8444
>  and port <= 8459
>

No.  Both queries are identical.  You can see this by prefixing them  
both with EXPLAIN and looking at the content of the generated prepared  
statement, and observing that the prepared statements are identical.

Using "x BETWEEN y AND z" instead of "x>=y AND x<=z" is unlikely to  
make any measurable performance difference in the case where x is a  
simple column value.  If x is an expensive subquery of some kind, it  
could make a difference since with BETWEEN x is only evaluated once,  
whereas it would be evaluated twice for "x>=y AND x<=z".  Other than  
the number of times that x is evaluated, there is no difference in  
BETWEEN and "x>=y AND x<=z".  If an index is used to evaluate the x  
expression then the two forms really are completely identical.

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] use of parentheses when not arithmetically or logically required

[yaconsult]

Simon,

Thank you for the optimization.  I'll switch to using between.  This
particular database is used for generating some statistics and generating
reports, so performance is not that big an issue.

When you talk about "clever use of an index", are you referring to a
combined index or merely indices on each column?

Thanks for your input!
-- 
View this message in context: 
http://www.nabble.com/use-of-parentheses-when-not-arithmetically-or-logically-required-tp25033803p25035025.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] use of parentheses when not arithmetically or logically required

[Simon Slavin]

On 18 Aug 2009, at 10:26pm, yaconsult wrote:

> My question is if it makes a difference to use parentheses when not
> logically required, as in the following example where the only  
> logical used
> is "and":
>
>select * from log
>where
>  (response >= 200 and response < 300)
>  and
>  (port >= 8444 and port <= 8459)
>
> Is there any difference in execution or performance if the  
> parentheses are
> present or not?  I'm guessing not, but would like confirmation.
>
>select * from log
>where
>  response >= 200
>  and response < 300
>  and port >= 8444
>  and port <= 8459

These would, as you suspect, have different results when seen as  
SQLite queries.  One of them tests every record against two pieces of  
logic, the other uses four constraints each one of which might be  
implemented by clever use of an index.  You might want to use EXPLAIN  
on them to see what each one does.

But I mostly wanted to note that neither of these are the best way to  
do it.  You would probably be best using the BETWEEN operator:



SELECT * FROM log
WHERE response BETWEEN 200 AND 299
  AND port BETWEEN 8444 AND 8458

Note that I had to change two of the integers because it tests for  
'<=' not '='.  (I keep forgetting to do this in my own programming.)   
This format allows the query optimiser to make the best possible use  
of the constraints:



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

[sqlite] use of parentheses when not arithmetically or logically required

[yaconsult]

I have searched the list and understand the need for parentheses when
required by logical or arithmetic operations or subselects.

My question is if it makes a difference to use parentheses when not
logically required, as in the following example where the only logical used
is "and":

select * from log
where
  (response >= 200 and response < 300)
  and
  (port >= 8444 and port <= 8459)

Is there any difference in execution or performance if the parentheses are
present or not?  I'm guessing not, but would like confirmation.

select * from log
where
  response >= 200
  and response < 300
  and port >= 8444
  and port <= 8459

Thanks!
-- 
View this message in context: 
http://www.nabble.com/use-of-parentheses-when-not-arithmetically-or-logically-required-tp25033803p25033803.html
Sent from the SQLite mailing list archive at Nabble.com.

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