Jul 05 2008
Evaluating IS NULL vs <> with T-SQL DATETIMEs
Many OR/Ms use an UpdatedAt column and some even use a DeletedAt column for soft deletes. This practice also carries over when using date ranges for controlling notification display (StartDate and EndDate).
I’ve often heard many DBAs make the claim that doing a date comparison is faster than checking if the value in a date column IS NULL, so I decided to run some primitive benchmarks to see what I would discover. Admittedly, I’ve also held this belief myself, but I’ve never witnessed any numbers to either prove or disprove the theory.
First, I created two tables with two columns each - CreatedAt and DeletedAt. One table allows NULLs in the DeletedAt column, the other does not.
Next I filled each table with 1,000,000 random DATETIME values. Each table recieved the exact same DATETIME values in each row and a random number seed was used to determine whether or not the NULL table would have a NULL value or a random DeletedAt value. In my test case, 500843 records had a NULL value. I then ran three separate queries to compare the performance of IS NULL against . Before and after each query, the current time was stored in a variable which was manipulated using a millisecond DATEDIFF to determine the total execution time.
-- turn off printing row counts to speed up processingSET NOCOUNT ON;-- Create test tablesIF OBJECT_ID('Benchmarking.dbo.TestNullDatetime', 'U') IS NULLBEGINCREATE TABLE TestNullDatetime(CreatedAt DATETIME NOT NULL,DeletedAt DATETIME);END;IF OBJECT_ID('Benchmarking.dbo.TestNotNulLDatetime', 'U') IS NULLBEGINCREATE TABLE TestNotNullDateTime(CreatedAt DATETIME NOT NULL,DeletedAt DATETIME NOT NULL);END;DECLARE @counter INT;DECLARE @random FLOAT;DECLARE @threshold FLOAT;DECLARE @current DATETIME;DECLARE @deleted DATETIME;SET @threshold = 0.5;SET @counter = 0;WHILE @counter < 1000000BEGINSET @random = RAND();SET @current = GETDATE();SET @deleted = @current + (@random * 9.5);IF @random > @thresholdBEGININSERT INTO TestNullDatetime VALUES (@current, NULL);INSERT INTO TestNotNullDatetime VALUES (@current, @current);ENDELSEBEGININSERT INTO TestNullDatetime VALUES (@current, @deleted);INSERT INTO TestNotNullDatetime VALUES (@current, @deleted);ENDSET @counter = @counter + 1;ENDDECLARE @start DATETIME;DECLARE @end DATETIMEDECLARE @null_isnullcompare INTEGER;DECLARE @null_notequalcompare INTEGER;DECLARE @notnull_notequalcompare INTEGER;SET @start = GETDATE();SELECT * FROM TestNullDatetime WHERE DeletedAt IS NOT NULL;SET @end = GETDATE();SET @null_isnullcompare = DATEDIFF(ms, @start, @end);SET @start = GETDATE();SELECT * FROM TestNullDatetime WHERE DeletedAt <> CreatedAt;SET @end = GETDATE();SET @null_notequalcompare = DATEDIFF(ms, @start, @end);SET @start = GETDATE();SELECT * FROM TestNotNullDatetime WHERE DeletedAt <> CreatedAt;SET @end = GETDATE();SET @notnull_notequalcompare = DATEDIFF(ms, @start, @end);SELECT @null_isnullcompare AS [NULL - IS NULL comparison],@null_notequalcompare AS [NULL - NOT EQUAL comparison],@notnull_notequalcompare AS [NOT NULL - NOT EQUAL comparison];-- restore original stateSET NOCOUNT OFF;- Download this code: benchmarking.sql
The results are as follows:
| Run | NULL table, IS NULL | NULL table, <> | NOT NULL table, <> |
|---|---|---|---|
| 1 | 5470 | 5436 | 5373 |
| 2 | 5563 | 5516 | 5546 |
| 3 | 5936 | 5750 | 5466 |
| 4 | 5500 | 5453 | 5390 |
| 5 | 5576 | 5640 | 5440 |
| AVG | 5609 | 5559 | 5443 |
Overall, the <> comparison is faster than an IS NULL comparison, and it is approximately 100 milliseconds faster to perform on a table with no NULLs than on a table with roughly 50% NULL values.
Of course, it’s important to take note these are all hot cache hits. I didn’t take the time to reboot my machine and determine the difference running this against a cold cache, however I suspect that the results would still be the same.
Testing was performed on a 2.0 GHz Core 2 Duo laptop running Windows XP SP2, SQL Server 2005 Developer Edition (patch level 9.0.3054), with 2 GB of RAM and 7200 RPM drives. It’s an HP Pavilion dv9000t, for those who are interested.
