September 7, 2015 at 7:05 pm
Hello all,
I have a question on what the major differences for performance between using
WHERE field IS NULL
vs.
WHERE ISNULL(field,0) =0
I have see the bottom one in many queries and I am curious if it is because of performance?
Any insight would be appreciated
Thanks
***SQL born on date Spring 2013:-)
September 7, 2015 at 9:00 pm
I've just done some benchmarks of my own, and they appear to be the same when there are no indexes that could have been used.
Using a function on a column in the WHERE clause will prevent SQL from being able to do an index seek on that column (the query will be non-SARGable, check this out for more information, particularly that first response). As a rule, I suggest not using functions in the WHERE clause when there's a decent alternative on performance grounds. My strong preference in that case would be field IS NULL OR FIELD = 0.
People don't use WHERE ISNULL(field, 0) = 0 over WHERE field IS NULL OR field = 0 because of performance benefits. I hope.
For more information, check out StackOverflow: isnull vs is null.
September 7, 2015 at 10:11 pm
The WHERE field IS NULL is a predicate is a simple predicate in the WHERE clause. SQL can use an index to perform a seek if one exists. Just as Andrew pointed out, the inclusion of a function in the WHERE clause such as ISNULL causes that predicate to be non-SARGable. Even if an index exists, it cannot be used. SQL must evaluate that predicate, but not on all rows matching the result set, because the result set is not yet built. Rather, it must evaluate the predicate on every row in the table and then filter for the matching rows.
If, for example, you have a 2-million row table and 150 rows are null, SQL has to perform the 2 million ISNULL functions and then filter for the 150 rows that match. The rows have to be read into memory to be evaluated and the 2 million functions take time to execute. By all means, use WHERE field IS NULL.
September 7, 2015 at 10:38 pm
Piling on Andrew's and Ed's good answers: avoid using functions on the columns in the where clause if possible and all the alternatives forms of that, such as function in a CTE which then is used in the outer where statement. The risk is that this will perform fine on smaller (test) data sets but when the data will grow in size, then it will potentially become a serious problem.
😎
For demonstration, here is a simple test harness to play
/* Safe place */
USE tempdb;
GO
SET NOCOUNT ON;
GO
/* Number of samples in the test data */
DECLARE @SAMPLE_SIZE INT = 1000000;
/* Test data set */
IF OBJECT_ID(N'dbo.TBL_SAMPLE_WITH_NULL') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_WITH_NULL;
CREATE TABLE dbo.TBL_SAMPLE_WITH_NULL
(
RowID INT NOT NULL CONSTRAINT PK_TBL_SAMPLE_WITH_NULL_RowID PRIMARY KEY CLUSTERED
,FLAG VARCHAR(10) NULL
);
;WITH CORE_DATA AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowID
,SUBSTRING(CONVERT(VARCHAR(36),NEWID(),1),1,10) AS FLAG
FROM sys.all_columns S1
CROSS JOIN sys.all_columns S2
CROSS JOIN sys.all_columns S3
)
INSERT INTO dbo.TBL_SAMPLE_WITH_NULL(RowID,FLAG)
SELECT TOP (@SAMPLE_SIZE)
CD.RowID
/* Add some random null values */
,SUBSTRING('',1,NULLIF(ABS(CHECKSUM(NEWID())) % 2801,0)) +
CD.FLAG
FROM CORE_DATA CD
CREATE NONCLUSTERED INDEX NCLIDX_DBO_TBL_SAMPLE_WITH_NULL_FLAG
ON dbo.TBL_SAMPLE_WITH_NULL (FLAG ASC) INCLUDE (RowID);
GO
--SET SHOWPLAN_TEXT ON;
GO
/* Bucket variables to suppress the output */
DECLARE @INT_BUCKET INT = 0;
DECLARE @CHR_BUCKET VARCHAR(10) = '';
RAISERROR('---DRY RUN-------------------',0,0) WITH NOWAIT;
SET STATISTICS IO,TIME ON;
SELECT
@INT_BUCKET = SWN.RowID
,@CHR_BUCKET = SWN.FLAG
FROM dbo.TBL_SAMPLE_WITH_NULL SWN;
SET STATISTICS TIME,IO OFF;
RAISERROR('---IS NULL-------------------',0,0) WITH NOWAIT;
SET STATISTICS IO,TIME ON;
SELECT
@INT_BUCKET = SWN.RowID
,@CHR_BUCKET = SWN.FLAG
FROM dbo.TBL_SAMPLE_WITH_NULL SWN
WHERE SWN.FLAG IS NULL;
SET STATISTICS TIME,IO OFF;
RAISERROR('---ISNULL-------------------',0,0) WITH NOWAIT;
SET STATISTICS IO,TIME ON;
SELECT
@INT_BUCKET = SWN.RowID
,@CHR_BUCKET = SWN.FLAG
FROM dbo.TBL_SAMPLE_WITH_NULL SWN
WHERE ISNULL(SWN.FLAG,'') = '';
SET STATISTICS TIME,IO OFF;
GO
--SET SHOWPLAN_TEXT OFF;
GO
Sample output
---DRY RUN-------------------
Table 'TBL_SAMPLE_WITH_NULL'. Scan count 1, logical reads 2982, physical reads 0, read-ahead reads 16, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 187 ms, elapsed time = 192 ms.
---IS NULL-------------------
Table 'TBL_SAMPLE_WITH_NULL'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
---ISNULL-------------------
Table 'TBL_SAMPLE_WITH_NULL'. Scan count 1, logical reads 2982, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 140 ms, elapsed time = 141 ms.
SHOWPLAN_TEXT
DRY RUN
----------------------------------------------------------------------------------------------------------------
|--Index Scan(OBJECT: ([tempdb].[dbo].[TBL_SAMPLE_WITH_NULL].[NCLIDX_DBO_TBL_SAMPLE_WITH_NULL_FLAG] AS [SWN]))
IS NULL
----------------------------------------------------------------------------------------------------------------------------------------------------------
|--Index Seek(OBJECT: ([tempdb].[dbo].[TBL_SAMPLE_WITH_NULL].[NCLIDX_DBO_TBL_SAMPLE_WITH_NULL_FLAG] AS [SWN]), SEEK: ([SWN].[FLAG]=NULL) ORDERED FORWARD)
ISNULL
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Index Scan(OBJECT: ([tempdb].[dbo].[TBL_SAMPLE_WITH_NULL].[NCLIDX_DBO_TBL_SAMPLE_WITH_NULL_FLAG] AS [SWN]), WHERE: (isnull([tempdb].[dbo].[TBL_SAMPLE_WITH_NULL].[FLAG] as [SWN].[FLAG],'')=''))
September 7, 2015 at 11:03 pm
"It Depends". By themselves, everything above is true. However, if you have sufficient other predicates against the table, you CAN still get index seeks from those predicates even though some of the other columns on that same table are encapsulated in a formula. Of course, the seek may have to do a bit more of a range scan behind the scenes for things like ISNULL(somecolumn,0)=0. And, of course, that also indicates a design problem in the table itself. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
September 8, 2015 at 8:19 am
Thank you all for the wonderful information. I should have also relayed more information than I did. It does seem that Jeff touched some on my situation with this table.
Unfortunately it is bad design and a vendor table. The column in question is a Void column. However instead of being set as a bit ON/OFF flag it was setup as a INT and NULLABLE. A voided record = 1 a non voided record = NULL. no other values are used I verified this with the schema documentation. I have no idea why this was built this way.
It does not have a INDEX on it but I do use the DATE column which does have a INDEX for limiting the range. The odd thing is after changing it and running the query a few times I am about 20 seconds slower than when it was ISNULL(VOID,0)=0. Trying to really understand this.
Thanks
***SQL born on date Spring 2013:-)
September 8, 2015 at 8:29 am
Have you looked at the execution plans for both varieties at all?
Wild speculation, but could be that the use of the date index adds key lookups/bitmap probes which might be slower than a clustered index scan.
Kinda what Jeff was getting at.
Not applicable in your situation, but it's worth mentioning that WHERE field IS NULL and WHERE ISNULL(field,x) =x are not the same query if there's rows in the table with field = x.
September 8, 2015 at 8:31 am
Maybe somebody said this already and I missed it, but don't forget that "field IS NULL" and "ISNULL(field,0) =0" are not the same thing.
"field IS NULL" will not return records where field = 0; "ISNULL(field,0) =0" will.
This may not matter in your situation, but it's something to be aware of.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 8, 2015 at 8:35 am
Going to take a look at the execution plans now.
Only two allowable choices 1 or NULL nothing else.
***SQL born on date Spring 2013:-)
September 8, 2015 at 10:48 am
There's a simple rule for this:
NEVER use ISNULL() in a WHERE or JOIN clause.
You can easily code around it, and it could cause performance issues. Even if there's no index today that's excluded, there could be one tomorrow or next week.
WHERE (Column1 IS NULL OR Column1 = 0)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply