SQL Server Performance Forum – Threads Archive
And Or Logic
Hi in one of your articles you statedthat when using the AND operator that SQL read the statement
from left to right and termenated the read when it hit a false condition.
This being the case the following should produce a divide by zero error and fail. select *
from sysobjects
where 1/parent_obj = 1
and parent_obj > 0 however that is not the case. Can someone help me and explain why this statement does not generate an error. Thanks
Mike
I can’t follow you. If you where include parent_obj > 0, 1/parent_obj never could be 0, so why do you expect an error? Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS†with no warranties for accuracy.
Perhaps this will help.
IF Object_ID(‘TempDB..#Test’) > 0
DROP TABLE #Test CREATE TABLE #Test
(
PK INT IDENTITY (1,1), –int Primary Key,
Divisor int,
[Name] Char(20)
) INSERT INTO #Test (Divisor,[Name])VALUES(0,’ZERO’)
INSERT INTO #Test (Divisor,[Name])VALUES(1,’ONE’)
INSERT INTO #Test (Divisor,[Name])VALUES(2,’TWO’) GO DECLARE @Div int
Set @Div = 10 SELECT @Div/#Test.Divisor AS "Quotient",#Test.[Name] AS "Number"
FROM #Test
WHERE @Div/#Test.Divisor > 1
AND #Test.Divisor > 0 If you comment out the AND #Test.Divisor >0 you will recieve a divide by
Zero error. If SQL reads left to right this statement should never be
reached. [Added during Edit. I changed the WHERE statement to > 1 so that the output would show that the divisor was never set to zero. Since this select statement does not produce a divide by Zero error the SQL the compiler must be rearrainging the order of the conditoins between the AND operand. That is #Test.Divisor > 0 is read before @Div/#Test.Divisor > 1. Which is not the expected behaviour.]
This came up while in a discussion of speeding up SQL. C and other some other programming languages read AND logic from left to right and
Terminate the read when they encounter a false condition. This being
the case always putting the most likely to fail operand on the left
should speed up processing. But this only works if the compiler
does not change the order of the reads.
If you reverse the AND statements you would have an error trap to prevent divide by zero errors. SELECT @Div/#Test.Divisor AS "Quotient",#Test.[Name] AS "Number"
FROM #Test
WHERE #Test.Divisor > 0
@Div/#Test.Divisor > 5
Luis let me restart in re reading this it is not even clear to me. Some high level languages such as C when reading the AND operand read the statement from left to right and terminate when they encounter a false condition. I have suggested as does an article on this site that
taking advantage of this behaviour can speed up processing. If you always put the most likely to fail condition on the left side of the AND operator processing time should be reduced since it did not have to read any of the statements to the right of the AND. Someone pointed out that the SQL compiler must not follow this left to right read strategy. If the compiler reads left to right then the select *
from sysobjects
where Parent_obj > 0
AND 1/parent_obj = 1 should not produce a divide by zero error while reversing the order of
the conditions would produce an error. Even though the second condition parent_obj >0 should never be reached without processing being halted by a divide by zero error including it in the logic prevents the divide by zero error. Which seem to indicate that the SQL compiler does not follow the left to right read pattern. select *
from sysobjects
where 1/parent_obj = 1
and parent_obj > 0
Okay, have a look at the execution plans for both statements. You’ll find that they are identical. The SQL Server optimizer is a very sophisticated piece of software. It can and will rearrange WHERE clauses to optimise query execution. In your example the most important driving clause is parent_obj >0. The optimiser is using this SARG to find the matching rows first, since sysobjects has an index on parent_obj. And actually it makes no sense to evaluate 1/parent_obj *before* you have any rows to try this on. So, SQL Server first does an index seek to find the qualifying rows and once it has found these rows, 1/parent_obj becomes a valid expression. As for your example:
This is a bit different to what you tried on sysobjects. If you rewrite this to
DECLARE @Div int
Set @Div = 10 SELECT @Div/#Test.Divisor AS "Quotient",#Test.[Name] AS "Number"
FROM #Test
WHERE @Div/#Test.Divisor = 1
AND #Test.Divisor > 0 it produces no error. I would not try to outsmart the optimiser and write my WHERE clauses in a rather natural way. The rest I leave to the optimiser.
—
Frank Kalis
SQL Server MVP
http://www.insidesql.de
I agree with Frank. First off 1/parent_obj = 1 is essentially a function so all rows will have to be tested – definitely not possible to use an index for this search condition. The parent_obj > 0 on the other hand is clearly SARGable – the optimiser can find data with an index on this column very quickly. It’s also my understanding, though it’s difficult to find any clear documentation anywhere about the internal workings of the optimiser, that if you had two search parts: parent_obj > 0
and
parent_obj < 20 Then the query optimiser will analyse the expected row counts for each individual query part and perform the part that results in fewer rows first. e.g if most rows have parent_obj > 20 then the query part parent_obj <20 will be executed first. This is all based on the statistics maintained in the DB though so it’s important that these are kept up to date. Best bet for all situations is to build your queries as best you can, then analyse the execution plans to improve them. Regards,
Dave [email protected]
www.matiogi.com
]]>