May 20, 2009 at 2:54 am
nice one.
I've done some testing with clustered and non-clustered indexes and combinations of both and the results are the same.
I was also thinking this might be a target for a nice article.
How do you know the constant eval only happens one?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 20, 2009 at 3:00 am
The reason I ask the above and I guess it's related to the OP.
for me with a table of 1mil rows I get very much the same results for the below two queries.
SELECT RowNum,SomeInt
FROM CTSTest_OR
WHERE @temp = 0
OR (SomeInt) = @temp
SELECT RowNum,SomeInt
FROM CTSTest_OR
WHERE (SomeInt) = @temp
OR @temp = 0
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 20, 2009 at 3:09 am
Test code:
CREATE TABLE #tmpTab (
column1 int,
column2 int
)
INSERT INTO #tmpTab
SELECT A.colorder, B.status
FROM syscolumns A
INNER JOIN syscolumns B
ON A.colorder = B.colorder
This is what I tried:
SET STATISTICS IO ON
SELECT 1
WHERE EXISTS (
select 1
from #tmpTab
where column1 = column2
)
SELECT 1
WHERE EXISTS (
select 1
from #tmpTab
where 1 = 1
)
SELECT 1
WHERE EXISTS (
select 1
from #tmpTab
where column1 = column1
)
And these are the results:
Table '#tmpTab'. Scan count 1, logical reads 575, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#tmpTab'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#tmpTab'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Somehow the engine recognises "tautological" expressions and evaluates them only once.
-- Gianluca Sartori
May 20, 2009 at 3:23 am
I'm not 100% that that code is indicating what we thinking.
I tested it on my table:
where RowNum = SomeInt -- 1678
where 1 = 1 -- 5
where RowNum = RowNum -- 5
where RowNum = 1 -- 3
So even though I did static verse a static it seems that the last one had the least.
I wonder however if that is maybe not because there are statistics provided for that column because it in a key in an index so it knows the distribution hence need less reads?!? what do you think?
OK just did some reading and yeah it seems to be Pages.
So that doesn't necessarily mean that it's only evaluated once I don't think...
Wow this is fun he he he
I'm gonna check it out in profiler and see if there is more light shed on this
OK I just check it again on a non indexed column and the reads jump up VERY VERY high.
To be honest I'm not 100% clear if the "Logic Reads" means pages or if it means nodes or rows?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 20, 2009 at 3:46 am
I can't answer any of your questions, I can only confirm that short circuit is one of the features implemented in sqlserver:
http://technet.microsoft.com/it-it/cc678236(en-us).aspx
Search in this page for shor circuit and you'll find this answer by Nigel Ellis, development manager for the SQL Server Query Processor team:
A: There wasn't any change made in this area; the semantics of SQL does allow short-circuit evaluation, and we take advantage of this in our optimization engine.
Google returns lots of results for "sql server short circuit". Lots of forums debate this point, with different conclusions.
-- Gianluca Sartori
May 20, 2009 at 5:40 am
Gianluca Sartori (5/20/2009)
Other cases with the same test data:Divide by zero error:
select *
from @tmpTab
where (column1/column2 = 1)
OR column1 = column1
No error thrown:
select *
from @tmpTab
where (column1/column2 = 1)
OR 1 = 1
Conclusions:
1) Order matters in OR predicates
2) When constants are involved, the expression is evaluated only once
Really really interesting! I'll take the time to write an article on this...:-)
Not always!!, I've found the following piece of code on http://beingmarkcohen.com/?p=62 that does not short circuit.
CREATE TABLE ShortCircuit (
ID int )
GO
INSERT INTO ShortCircuit
SELECT 1 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 5 UNION
SELECT 6
GO
DECLARE @ID int
--SET @ID = NULL
SELECT ID
FROM ShortCircuit
WHERE @ID IS NULL OR ID = 1/0
DROP TABLE ShortCircuit
--Ramesh
May 20, 2009 at 6:25 am
This is because you're working with constant values: 1/0 is evaluated before the query is executed, I think during the execution plan elaboration.
In my test code this works:
DECLARE @id int
SELECT 1
WHERE EXISTS (
select 1
from #tmpTab
where @id is null
OR column1 / column2 = 1
)
and I have lots of column2 = 0.
-- Gianluca Sartori
May 20, 2009 at 7:10 am
too_sweeeeeeeet (5/12/2009)
I have a function that contains the following query which is always timimig out.select distinct @Result = sum( column1 )
from table (nolock)
where ((column2 = @param1) or (@param1 = 0))
What you have there is what I call a 'catch-all' query. When the optimiser generates a plan for that, it has to find a safe plan, one that can be reused and will work regardless of what parameters are passed. This tends to result in very poor exec plans.
Think about it, the optimal exec plan will be completely different in the case where @param1 is 0 from cases where it's something other than 0.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2009 at 7:19 am
Thanks for your opinion, Gail: I longly wished you came across this thread to shed some light.
Can you help us with some ideas on the short circuit side?
Thanks
Gianluca
-- Gianluca Sartori
May 20, 2009 at 7:32 am
I tried the same test code on a SQLServer 2000 box and I get the same results (original tests had been made on a 2005 x64 box).
No news, just a side note...
-- Gianluca Sartori
May 20, 2009 at 7:47 am
awesome Gail found us he he he
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 20, 2009 at 8:04 am
She didn't actually find us, but she was linked here from this http://qa.sqlservercentral.com/Forums/FindPost720460.aspx thread, that apparently has become a sort of "bulletin board" because all major experts are participating...
Hope you don't mind, Gail.
-- Gianluca Sartori
May 20, 2009 at 8:17 am
That one is called "The Thread".
By the way, while you guys are testing, maybe you should also check out whether short-circuiting also can happen with logical ANDs, i.e. when the first expression evaluates to FALSE?
May 20, 2009 at 8:59 am
Gianluca Sartori (5/20/2009)
Thanks for your opinion, Gail: I longly wished you came across this thread to shed some light.Can you help us with some ideas on the short circuit side?
Thanks
Gianluca
Can the optimizer use short-circuiting optimizations? Yes.
Will it always? No (this forum is full of examples of folks tripping over it). Sometimes it's more efficient not to. Also, this gets into just exactly what "short-circuiting" means in complex/compound set processing queries. It's not as clear as you might think.
If it does short-circuit, will it always test the first clause first? No.
Can I control the order of short-circuiting? Not if the optimizer does it, only if you do explicit short-circuiting yourself(usually involves nested CASE tests).
This may seem trivial or obvious, but it is by far the most common mistaken assumption (usually implicit) about SQL and short-circuiting. For instance the following code has been seen many times on the boards and forums:
SELECT *
From syscolumns
Where IsNumeric(name) = 1
And Convert(Float, name) > 1.5
Invariably, this query will fail with the error "Error converting data type nvarchar to float." Not because IsNumeric doesn't work right (as is often assumed) but because 1) you can't rely on short-circuiting, and 2) the code order does not determine the execution order.
Now this, usually does work:
SELECT *
From syscolumns
Where IsNumeric(name) = 0
OR Convert(Float, name) > 1.5
but it is completely unreliable.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 20, 2009 at 9:28 am
Gianluca Sartori (5/20/2009)
Can you help us with some ideas on the short circuit side?
Maybe.
I've seen SQL short circuit some expressions, emphasis on some. Mostly in cases of expressions based on constants and 'trivial' expressions (1=1, col1 = col1).
Normally, once the expressions become non-trivial, it's the indexes which affect which operations run in which order.
ORs are slightly different, because they're almost the same as the union of two resultsets and, depending on how the optimiser decides to behave, order may or may not matter. If it decides to scan an index then the order may matter (especially if one of the expressions is trivial or always true). If it decides to break up the query, seek on two indexes and concatenate the two afterwards, order won't matter.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 16 through 30 (of 57 total)
You must be logged in to reply to this topic. Login to reply