March 22, 2005 at 5:46 am
If a query has several OR statements will it check for all the OR statements or will it skip checking for remaining OR statements when it encounters one of the OR statements that satisfies the condition?
Thanks in advance.
March 22, 2005 at 6:02 am
Will check all.
**ASCII stupid question, get a stupid ANSI !!!**
March 22, 2005 at 6:56 am
Actually I pretty sure that the evaluation stops on the first true condition of an OR. But I am not sure of how to test or right off who in MS to pose the question to directly.
But assuming that SQL is written efficiently itself then the underlying code should kick out as soon as any required condition kicks out true based on the evaluation predicates. Otherwise SQL Server would waste huge amounts of unneccessary CPU cycles.
March 22, 2005 at 7:36 am
It will exit after the first hit.
Select * from dbo.SysObjects WHERE
1 > 0
OR
--this is shortcircuited
1/0 > 0--should throw an error
Complete article on WindowsItPro
March 22, 2005 at 7:52 am
Maybe I'm completely missing something here...I've been using OR operators to fetch me a resultset where either condition specified has been true - so if I specify 2 conditions like "where a_lastname like 'sm%' or a_lastname like 'an%' then I get a resultset that includes all last names that begin with "an" and "sm".
Did I misunderstand the post ?!?!
**ASCII stupid question, get a stupid ANSI !!!**
March 22, 2005 at 8:02 am
I don't know if you misunderstood, but the results you are getting are correct. The post is about shorcircuiting the or operator. Like in my previous post, when either of the or conditions are true, then the whole predicate MUST be true, there's just no way around that. So if the first condition is true then there's no point in checking the 2nd condition. That's the theory, the question was : "Is sqlserver using shortcircuiting?". And I proved that it is in my previous query.
March 22, 2005 at 9:52 am
Sushila,
Remi's answer is correct, and may make more sense if you look at when in the execution cycle the where clause is evaluated. For example, consider the simple query:
select * from someTable where column_1 = 'A' or column_1 = 'C'
In this query, the where clause will be evaluated for each row in someTable. For any specific row, the "OR" will be short-circuited as soon as a "true" response is found. The key is that for any specific row, an "OR" will exit as soon as the where clause evaluates to true. From the perspective of the overall select, you will receive rows that match either of the tests. This may give the appearance that the where clause is not short circuiting.
Does this make sense?
Hope this helps
Wayne
March 22, 2005 at 10:32 pm
See this for more info...
http://www.sql-server-performance.com/transact_sql.asp
Apparently it will evaluate AND from left to right but Or's are not indicated as exiting. But there appears to be performance issues with OR's and indexes.
If SQL is cost- based I would think it would exit? I found no documentation to say one way or another.
March 23, 2005 at 1:02 am
Why should it continue evaluating when it already found a matching condition?
I think that's one of the basic principles of designing efficient algorithms. Most people tend to place conditions in a certain alphabetic or whatever order when it actually makes more sense to order from most likely to least likely. That way the algorithm should complete faster. Consider an algorithm for leap years. Rules are pretty simple.
Obviously most years are non leap years. So placing this as a first check condition might make the most sense. Next every 100 year happens more frequently than every 400 years. So the order of checking matches the rules stated above. So an algorithm might look something like this:
declare @theyear int
set @theyear = 2005
select
case
when @theyear % 4 <> 0
then 'Kein Schaltjahr'
else
case
when @theyear % 100 = 0
then
case
when @theyear % 400 = 0
then 'Schaltjahr'
else 'Kein Schaltjahr' end
else 'Schaltjahr' end
end as Schaltjahr_Ja_Nein
go
I like to think that this is also the case when evaluating OR's.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
April 11, 2005 at 2:44 am
how do i start programming in PLsql
i would like to restart programming in PLsql
April 11, 2005 at 2:47 am
how do start programming in plsql
pls let know where to start from rest I will do it
April 11, 2005 at 3:10 am
Maybe you'd have a better chance of getting a good answer if you started a new thread instead of a finished one.
April 11, 2005 at 3:21 am
Not only this, but I also think this here is SQL Server country. Probably there are better places to ask this question.
Maybe http://www.orafaq.com
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply