Which AND condition executes first? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Which AND condition executes first?

Author

Topic

gsrinivas.
Yak Posting Veteran

56 Posts

Posted - 2010-02-15 : 06:06:03

Hi SQL Team,
This is Srinivas.

I have a big doubt on "Which AND condition executes first?
in a SQL Statement"

If my query has a lot of AND conditions with some sub
queries also.
So if there is any one AND condition falis
then is the processor goes to next condition.??

If no, which is first AND and which is NEXT .
( for eg:
=======================================================
SELECT EMPLID,EFFDT,JOBCODE,DEPTID
FROM PS_JOB
WHERE EMPLID='K0G001'
AND EFFDT = (SELECT MAX(EFFDT) FROM PS_JOB B
WHERE A.EMPLID=B.EMPLID AND
A.EMPL_RCD=B.EMPL_RCD AND
B.EFFDT <= GETDATE())
AND DEPTID='DOOO1'

=======================================================

hear which Where executes first??
please help.

Thanks
-Srinivas.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-15 : 06:25:33

Hi Gsrinivas,

In SQL SERVER the order of evaluation isn't guaranteed.

This means that certain features you may be used to from software development (i.e short circuiting etc ) aren't available.

Have a look here:
http://weblogs.sqlteam.com/mladenp/archive/2008/02/25/How-SQL-Server-short-circuits-WHERE-condition-evaluation.aspx

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2010-02-15 : 06:37:21

You also CANNOT guarantee that an OR condition will not be exercised

FALSE1
AND (FALSE2 OR Condition3)

may cause Condition3 to be evaluated.

if any of your AND conditions are FALSE then the WHERE will be False, or course. However, the Query Optimiser may not pick the most efficient route ...

... you can force the "route" by using nested sub-queries, but it isn;t worth it unless you have a serious performance problem that can only be solved that way.

I presume you are using SQL 2000 (as you have posted in SQL 2000 forum

) but the thing you are trying to do is more efficiently solved in SQL2005 using "ROW_NUMBER() OVER" syntax, or a CROSS APPLY.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-02-15 : 07:05:48

Does anyone tested whether SQL Server 2005/2008 does short-circuit evaluation?

Harsh Athalye
http://www.letsgeek.net/

Kristen
Test

22859 Posts

Posted - 2010-02-15 : 07:31:35

No! But the Query Planner can't know which logic tests will Pass / Fail when making the plan, can it? Thus it may build a plan that evaluates out-of-order, so to speak, I think??

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-02-15 : 07:41:25

Yes, that is true, but at the time of execution it can implement short-circuit evaluation, isn't it? If it is out-of-order evaluation, then it's certainly difficult.

Harsh Athalye
http://www.letsgeek.net/

Kristen
Test

22859 Posts

Posted - 2010-02-15 : 07:42:58

"but at the time of execution it can implement short-circuit evaluation, isn't it?"

Good point. Yes, that would be possible. No idea how to test though!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-02-15 : 07:54:35

At least it does at the time of purely mathematical evaluation:

select top 10 * from table where 1/0=0 or 1+1=2

This query doesn't give error although it is divide by 0 error.

Harsh Athalye
http://www.letsgeek.net/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-15 : 07:58:32

would be interesting if you changed that 1 to a column in the table. (the 1/0 part).

I think with an OR condition you may be about to perform a table scan anyway.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2010-02-15 : 08:57:40

select top 10 * from MyTable where MyColumn/0=0 OR MyColumn+1<>-1

gives Divide by Zero error

select top 10 * from MyTable where MyColumn+1<>-1 OR MyColumn/0=0

does not. (MyColumn only has values > 0)

... but I don't think that proves anything ... optimiser may choose to do a similar, but more complex, query differently

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-02-15 : 09:03:07

Changing 1 to column name doesn't change it, but if you add any other condition which involves table column on the right hand side of OR clause, it promptly gives Divide by zero error, which is expected.

Like,
where fooCol2/0=0 or 1+1 = 2 (doesn't give error)
where fooCol2/0=0 or fooCol1 = 1 (gives error)

but interesting this doesn't give error:

where 1/3+4-7=0 or fooCol1 in (1,55)

Harsh Athalye
http://www.letsgeek.net/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-02-15 : 09:08:04

quote:
Originally posted by Kristen

select top 10 * from MyTable where MyColumn/0=0 OR MyColumn+1<>-1

gives Divide by Zero error

select top 10 * from MyTable where MyColumn+1<>-1 OR MyColumn/0=0

does not. (MyColumn only has values > 0)

... but I don't think that proves anything ... optimiser may choose to do a similar, but more complex, query differently

That proves only thing that SQL Server evaluates left to right. If you just change the order of condition, you will get error in 2nd case also.

select top 10 * from MyTable where MyColumn/0=0 or MyColumn+1<>-1

Harsh Athalye
http://www.letsgeek.net/

Kristen
Test

22859 Posts

Posted - 2010-02-15 : 09:08:22

Anyway, should not have Divide operation without adopting Safe Hex

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-02-15 : 09:18:29

hehe..true

Harsh Athalye
http://www.letsgeek.net/

gsrinivas.
Yak Posting Veteran

56 Posts

Posted - 2010-02-15 : 10:47:37

Thanks for you replies...

My question is good. but unfortunately
I don't think some of you are understand properly.

hear,
in query , there is no bother about OR clause.

if there are 50 sub queries in my main query at random positions.
and those 50's returns corresponding a single value for their AND clause variable only.(sorry if any inconsistent sentence made)

hear what my question is, if 20th AND statement is FALSE
then why the query analyzer goes to rest of ALL ANDs.

got it?

so the query analyzer should not go to first to subqueries.
because it may contains lot of joins also.
really I dont know what the short circuit is...
but i would like to know about very best process
according to my requeirement.

so can i direct the query analyzer to choose these ANDs part is
first.and these ANDs are next .

ok ok

other wise can i make the query as
"the First part of Where is some 30 ANDs , and
the rest of that is 20 ANDs as a second part "
by using any pl-sql logic.

please consider if the where clause contains all ANDs only.

I am waiting for your replies.
-Thaks a lot
GSrinivas.

and reducing the
process of sql server

Kristen
Test

22859 Posts

Posted - 2010-02-15 : 11:05:59

"if 20th AND statement is FALSE then why the query analyzer goes to rest of ALL ANDs."

That's what we've been telling you.

There is no guaranteed order of execution.

SQL makes a Query Plan without regard to the actual parameters. Subsequent queries may reuse the Query plan (with different / new parameters), thus the query plan has to work in all cases.

In general you should not bother about how the Query Planner chooses to make a query plan for your query, because most of the time it will be fine.

Only when you have performance problems should be worry about it - and then after exhausting several other, different, avenues to speed up the query.

Kristen
Test

22859 Posts

Posted - 2010-02-15 : 11:08:08

" so can i direct the query analyzer to choose these ANDs part is first.and these ANDs are next ."

I already answered this earlier:

"you can force the "route" by using nested sub-queries, but it isn;t worth it unless you have a serious performance problem that can only be solved that way."

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-15 : 11:28:13

Also it sounds like your query is probably badly designed.

If you have a lot of sub-queries inside your WHERE clause it's a general indication that you could probably have rewritten it using derived tables or some other method.

I'm guessing that the query just got more and more "bits" added to it.

Feel free to post the whole query if you like.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2010-02-15 : 11:40:18

no, there is no short circuiting of conditions based on columns in any version of sql server.
doing that would defeat the cost based optimizers purpose.

the 1/0, etc... can be short circuited because it's not column based.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.7 out!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources