Lets Clarify Operator Precedence!
-
BOL says,
NOT
AND
BETWEEN
Can someone tell me wich one of the following is correct given the fact that both queries work and return the same results?
WHERE end_date BETWEEN @StartDate AND @EndDate
AND end_date IS NOT NULL
AND cabs IS NOT NULL
AND cat_code NOT IN ('INSURANCE', 'SALCONT')
WHERE cat_code NOT IN ('INSURANCE', 'SALCONT')
AND end_date IS NOT NULL
AND cabs IS NOT NULL
AND end_date BETWEEN @StartDate AND @EndDate
-
The second option will work faster.
It is first removing the records which doesn't have any date value.
Only the remaining records will be processed for the "between" condition. Again, in oracle (not sure in SQL Server), "between" will be converted as <= and >= internally by the system. In other words, Its the between condition which is going to take more time in your case & the second query passes less records for the between condition.
You can also confirm this point by checking the time taken by the query in both the cases.
-
It is still up to the Query Optimizer which will use statistics to determine what indees to use. If the between object has the best index choice available it may perform that action first and you can get very unpredictable actions based on guess. Suggest you have show execution plan on and watch what it actually does in both cases. And yes it does convert to <= and >= behind the scenes.
-
Thanks for correcting me.
But wont it happen the way I said if the table is not having any indexes ? I tried it on my machine a couple of times by creating a new table without indexes and all the times I observed the second query returning results faster !!!
-
>>I observed the second query returning results faster
In that case I suggest you send your table DDL and queries to Microsoft PSS, because the days when the order of conditions in the Where clause influenced performance were years ago.
The SQL is parsed and an optimal execution plan is determined not from the order in which you type conditions.
How many tests did you run ? Did you DBCC DROPCLEANBUFFERS between each test to simulate a cold start with no data pages cached ?
-
Trigger, do you feel that the WHERE's should give you two different results? If so, how?
-
>>I observed the second query returning results faster
SQL is a declarative Language you tell it what you want but you have little control on how you get it
It - The Plan - also depends on statistics and value distributions
* Noel -
I think I may steered everyone down the wrong path except for rhunt. I'm talking about opertaor precedence not perfromance!
BOL says that NOT comes first then AND followed by BETWEEN. If this is the case then why will i get the same result set if the first option is doing the BETWEEN first and the NOT IN last?
Have I misintepreted the way operator precednece works?
-
Not exactly. You asked a question about operator precedence, then proceeded to give 2 examples where precedence is irrelevant.
Consider the following:
Declare @EvalResult int
Set @EvalResult = 1 + 7 * 9 - 45 / 34 + 100
What is the result ? Answer is dependant on operator precedence in this case - because there is ambiguity in which operator is applied first.
In your example, there is no ambiguity, therefore precedence is irrelevant.
-
Please have a look in BOL under "WHERE clause, predicate". This is where I'm basing my info from.
My 1st option is doing BETWEEN, AND, NOT IN
My 2nd option is doing NOT IN, AND, BETWEEN
If I were to put an OR in here as well, would that make the opertaor precedence relevant? Example, BETWEEN, OR, AND, NOT IN
Surely the OR is now in th wrong place or perhaps you would have to enclose the OR in brackets() to evaluate the OR before the AND. See what I mean?
-
Simplifying both your WHERE clauses:
WHERE end_date (Some Boolean expression)
AND end_date (Some Boolean expression)
AND cabs (Some Boolean expression)
AND cat_code (Some Boolean expression)
and:
WHERE cat_code (Some Boolean expression)
AND end_date (Some Boolean expression)
AND cabs (Some Boolean expression)
AND end_date (Some Boolean expression)
The result of 4 boolean expressions AND'ed together with logical AND operators is the same regardless of order. That's why precedence is irrelevant.
If you introduce an OR condition, then indeed, precedence becomes an issue and you would either rely on operator precedence or use parentheses to force a certain evaluation order.
-
Just to add to the fun here ... Try this little code snippet in QA ... it's totally self contained ...
set nocount on
go
declare @gender char(1)
select @gender='F'
---
--- this looks like a valid if test, but is not
---
if @gender != 'M' OR @gender != 'F'
begin
print 'invalid gender'
print 'but it is correct ???'
select @gender
end
else
begin
print 'invalid gender'
end
---
--- this is the way it should be ... it does look odd though ...
--- the explanation has something to do with boolean truth tables
--- and the fact that NOT is the last thing applied
--- (kind of like the way the optimizer works I think)
---
if @gender != 'M' AND @gender != 'F'
begin
print 'invalid gender'
print 'this is correct'
select @gender
end
else
begin
print 'gender is valid'
select @gender
print 'yup ...'
end
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply