November 9, 2011 at 8:02 am
Is there any difference between using IsNull or an OR statement as in the following:
In a stored proc, this parameter is passed in defaulting to NULL:
@parm2 int = NULL
Update MyTable Set MyField=@parm1
Where (ReferenceID is null or ReferenceId=@parm2);
OR
Update MyTable Set MyField=@parm1
Where ReferenceID = IsNull(@parm2, ReferenceID);
November 9, 2011 at 8:03 am
Edit: Sorry, I thought the param was the one with the IS NULL check
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
November 9, 2011 at 8:11 am
Actually, those aren't even equivalent queries.
Update MyTable Set MyField=@parm1
Where (ReferenceID is null or ReferenceId=@parm2);
OR
Update MyTable Set MyField=@parm1
Where ReferenceID = IsNull(@parm2, ReferenceID);
The first one will update all rows where ReferenceID is null, regardless of the value of @parm2, and will update any rows where ReferenceID = @parm2.
The second one will update all rows where ReferenceID is not null, if @parm2 is null, or all rows where ReferenceID = @parm2 if the variable is not null.
Not the same results at all.
However, if the queries were written to get the same results as each other, IsNull will have the same performance as OR, since they do the same thing in the query optimizer. They should end up with exactly the same very poor performance, in other words.
Edit: Should be noted, if ANSI NULLS is set to OFF for the queries, that will change how ReferenceID = IsNull() will behave, and thus make these two queries even more unpredictable.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 9, 2011 at 8:18 am
I should've written the complete update statement:
Parameters:
@StoreID int,
@CategoryID int,
@ReferenceID int = NULL
UPDATE MyTable
Set MyStatus = 'Closed'
WHERE StoreID = @StoreID
AND MyCategoryID = @CategoryID
AND MyStatusID IN ('Open', 'Acknow')
AND (@ReferenceID IS NULL OR ReferenceID = @ReferenceID)
I guess the same thing applies. If I pass in a ReferenceID, then I am updating those that are NULL and the passed in values for the Store and Category. I guess I need to make sure that is the intended usage.
November 9, 2011 at 8:23 am
In that case, this link does apply. 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
November 9, 2011 at 8:27 am
GSquared (11/9/2011)
However, if the queries were written to get the same results as each other, IsNull will have the same performance as OR, since they do the same thing in the query optimizer. They should end up with exactly the same very poor performance, in other words.
Hmm, not sure if that's true. ISNULL is a scalar function and is therefore not SARGable (except using indexed computed columns) whereas a NULL comparison is, so in theory, you could end up with a seek vs scan situation in using ISNULL() vs IS NULL. Unless I'm misunderstanding your point...
Edit: posted before I realised this is meant to be a catch-all query, in which case they will be (virtually) the same as it's only using the scalar function on a static variable 🙂
November 9, 2011 at 8:31 am
HowardW (11/9/2011)
GSquared (11/9/2011)
However, if the queries were written to get the same results as each other, IsNull will have the same performance as OR, since they do the same thing in the query optimizer. They should end up with exactly the same very poor performance, in other words.Hmm, not sure if that's true. ISNULL is a scalar function and is therefore not SARGable (except using indexed computed columns) whereas a NULL comparison is, so in theory, you could end up with a seek vs scan situation in using ISNULL() vs IS NULL. Unless I'm misunderstanding your point...
SQL can't seek that particular predicate form because the plan gets reused and a seek would produce incorrect results if rerun with the parameter null. You get scans (and odd ones usually)
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
November 9, 2011 at 8:45 am
GilaMonster (11/9/2011)
HowardW (11/9/2011)
SQL can't seek that particular predicate form because the plan gets reused and a seek would produce incorrect results if rerun with the parameter null. You get scans (and odd ones usually)
Yep, missed the clarification just before my post that this is a catch-all query.
Just for fun, for the original OR statement I get a seek on a range with this example in 2008:
USE tempdb
GO
CREATE TABLE test (col1 INT NULL)
CREATE INDEX idx_test ON test(col1 ASC)
INSERT INTO dbo.test
( col1 )
SELECT number FROM master..spt_values
UNION ALL
SELECT NULL
GO
DECLARE @param INT=8
SELECT col1 FROM test
WHERE (col1=@param OR col1 IS NULL)
--seek
GO
DECLARE @param INT=8
SELECT col1 FROM test
WHERE @param=ISNULL(col1,@param)
--scan
GO
November 9, 2011 at 9:17 am
HowardW (11/9/2011)
Just for fun, for the original OR statement I get a seek on a range with this example in 2008:
Yes, the original OR you will, because in that it's the column being checked for NULL that that's a perfectly safe seek. In the revised it's the parameter being checked for NULL and that can't seek because it wouldn't be safe (can produce incorrect results if the plan gets reused)
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 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply