May 12, 2009 at 1:16 am
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))
If alter the function to be the following the query takes less than 1 second to return.
select distinct @Result = sum( column1 )
from table (nolock)
where ((column2 = @param1))
Can anyone tell me why this would be and how I can have protection for @param1 = 0 but still have the fast execution time?
May 12, 2009 at 2:41 am
I can't tell you exactly why the test for @param1 = 0 takes so long, maybe someone with stronger skills can help you digging into this. This is something I have noticed too in the past.
A simple way to avoid it is to reverse the condition:
select distinct @Result = sum( column1 )
from table (nolock)
where @param1 IN (column2, 0)
As a side note, I wouldn't use NOLOCK, beacause it could lead to page splits and duplicate results, as Gail Shaw pointed out in an interesting post that I will try to find and link from here later.
Regards
Gianluca
-- Gianluca Sartori
May 12, 2009 at 2:42 am
This is the test code:
declare @tmpTab TABLE (
column1 int,
column2 int
)
declare @param1 int
declare @result int
set @param1 = 3
insert into @tmpTab VALUES(1,1)
insert into @tmpTab VALUES(1,1)
insert into @tmpTab VALUES(1,2)
insert into @tmpTab VALUES(1,2)
insert into @tmpTab VALUES(1,3)
insert into @tmpTab VALUES(1,3)
select distinct @Result = sum( column1 )
from @tmpTab
where @param1 IN (column2, 0)
select @result
-- Gianluca Sartori
May 12, 2009 at 2:55 am
And this is the post I was talking about:
http://www.sqlservercentral.com/Forums/FindPost673544.aspx
-- Gianluca Sartori
May 12, 2009 at 4:52 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))
If alter the function to be the following the query takes less than 1 second to return.
select distinct @Result = sum( column1 )
from table (nolock)
where ((column2 = @param1))
Can anyone tell me why this would be and how I can have protection for @param1 = 0 but still have the fast execution time?
If the table has an index on "column2" then the first query will probably generate an execution plan with an "Index Scan" while the second query uses "Index Seek" which is faster than an "Index Scan".
To overcome this, you can use the method posted by "Gianluca Sartori".
It would be better if you can provide us with the table structure, indexes and generated execution plans for the queries.
--Ramesh
May 17, 2009 at 10:30 pm
Gianluca Sartori (5/12/2009)
I can't tell you exactly why the test for @param1 = 0 takes so long, maybe someone with stronger skills can help you digging into this. This is something I have noticed too in the past.A simple way to avoid it is to reverse the condition:
select distinct @Result = sum( column1 )
from table (nolock)
where @param1 IN (column2, 0)
As a side note, I wouldn't use NOLOCK, beacause it could lead to page splits and duplicate results, as Gail Shaw pointed out in an interesting post that I will try to find and link from here later.
Regards
Gianluca
Thanks Gianluca, this has solved my problem.
May 19, 2009 at 9:10 am
I'd be very interested in seeing the execution plans if that is still possible.
I have done some testing on my demo table of a mill rows and the results are the same for all...
I seem to be getting table scans and not table seeks.
as far as I know
@something = something or @something = 0
is exactly the same as
@something IN (something,0)
SQL to my knowledge will turn an IN clause into an OR clause ...
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 19, 2009 at 9:22 am
If you look at the execution plan for the test code I posted earlier, you will see that IN is turned to OR in the table scan predicate. I can only suppose that the shortcircuit OR is evaluated only once for constants in the IN clause. It sounds very strange, but it's something I found several times, but I wasn't able to investigate deeply.
Please note that @param1 = 0 OR @param1 = column2 is not exaclty the same as @param1 = column2 OR @param1 = 0 , since shortcircuit OR evaluates the expressions from left to right.
-- Gianluca Sartori
May 19, 2009 at 9:33 am
This all sounds very very interesting, could you explain a bit more about what you mean by Shortcircut OR ?
I've also noticed that
@Param IN (0,Column2)
produced a different order of the or predicate to
@Param IN (Columns2,0)
I honestly didn't realise that the order of the OR clause made a difference.
To be honest I'd love to find a real example of where the performance is actually different.
I do agree that the predicate looks different by does it really have an impact on the data retrieval?
Thanking you in advance for any further information
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 19, 2009 at 10:37 am
With Shortcircuit OR I mean that if the first expression is True, all the other expressions in the OR clause are not even evaluated and True is returned. Maybe SQLServer is smart enough to evaluate expression in OR in advance when constant values are involved. I also think that the optimizer should evaluate expressions in OR in an order such to perform first the comparison with the lowest possible cost, even when constants are not involved. The only possible criteria to determine the lowest evaluation cost is based on data type.
Does it sound possibile?
I think we need a SQLServer internals expert to be sure...
Gail, are you listening?
-- Gianluca Sartori
May 20, 2009 at 2:07 am
I totally see what you mean.
But you right we need some advice here, I'll try and get some data together and see if I can work it out from a performance point of view, but I would have thought that SQL would have used stats to work out which one to do first as it does with Table joins etc.
But like I say that is Guess.
Yeah hopefully Grant or Gail or someone could point us in the right direction...
Perhaps you have some examples yourself where you have seen this happen?
----------------------------------------------
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 2:34 am
Now I'm sure that SQLServer uses shortcircuit OR. Try this:
DECLARE @tmpTab TABLE (
column1 int,
column2 int
)
declare @param1 int
declare @result int
set @param1 = 3
insert into @tmpTab VALUES(1,1)
insert into @tmpTab VALUES(1,1)
insert into @tmpTab VALUES(1,2)
insert into @tmpTab VALUES(1,2)
insert into @tmpTab VALUES(1,3)
insert into @tmpTab VALUES(1,3)
insert into @tmpTab VALUES(1,0) -- This should generate a divide-by-zero error
select *
from @tmpTab
where column1 = column1
OR (column1/column2 = 1)
If the engine evaluated the second expression, this would cause a divide by zero error, which is not thrown.
Interesting, isn't it?
-- Gianluca Sartori
May 20, 2009 at 2:37 am
That is very very interesting I must say, I wonder if this happens everytime?
----------------------------------------------
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 2:38 am
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...:-)
-- Gianluca Sartori
May 20, 2009 at 2:40 am
It would be nice to investingate what happens when indexes are involved: does this happen only beacuse my example works on a heap?
I'll be back with news...
-- Gianluca Sartori
Viewing 15 posts - 1 through 15 (of 57 total)
You must be logged in to reply to this topic. Login to reply