facility9

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.

1. -- turn off printing row counts to speed up processing
2. SET NOCOUNT ON;
3.  
4. -- Create test tables
5. IF OBJECT_ID('Benchmarking.dbo.TestNullDatetime', 'U') IS NULL
6. BEGIN
7. CREATE TABLE TestNullDatetime
8. (
9. CreatedAt DATETIME NOT NULL,
10. DeletedAt DATETIME
11. );
12. END;
13.  
14. IF OBJECT_ID('Benchmarking.dbo.TestNotNulLDatetime', 'U') IS NULL
15. BEGIN
16. CREATE TABLE TestNotNullDateTime
17. (
18. CreatedAt DATETIME NOT NULL,
19. DeletedAt DATETIME NOT NULL
20. );
21. END;
22.  
23. DECLARE @counter INT;
24. DECLARE @random FLOAT;
25. DECLARE @threshold FLOAT;
26. DECLARE @current DATETIME;
27. DECLARE @deleted DATETIME;
28.  
29. SET @threshold = 0.5;
30. SET @counter = 0;
31.  
32. WHILE @counter < 1000000
33. BEGIN
34. SET @random = RAND();
35. SET @current = GETDATE();
36. SET @deleted = @current + (@random * 9.5);
37.  
38.  
39. IF @random > @threshold
40. BEGIN
41. INSERT INTO TestNullDatetime VALUES (@current, NULL);
42. INSERT INTO TestNotNullDatetime VALUES (@current, @current);
43. END
44. ELSE
45. BEGIN
46. INSERT INTO TestNullDatetime VALUES (@current, @deleted);
47. INSERT INTO TestNotNullDatetime VALUES (@current, @deleted);
48. END
49.  
50. SET @counter = @counter + 1;
51. END
52.  
53.  
54.  
55. DECLARE @start DATETIME;
56. DECLARE @end DATETIME
57.  
58. DECLARE @null_isnullcompare INTEGER;
59. DECLARE @null_notequalcompare INTEGER;
60. DECLARE @notnull_notequalcompare INTEGER;
61.  
62. SET @start = GETDATE();
63. SELECT * FROM TestNullDatetime WHERE DeletedAt IS NOT NULL;
64. SET @end = GETDATE();
65.  
66. SET @null_isnullcompare = DATEDIFF(ms, @start, @end);
67.  
68. SET @start = GETDATE();
69. SELECT * FROM TestNullDatetime WHERE DeletedAt <> CreatedAt;
70. SET @end = GETDATE();
71.  
72. SET @null_notequalcompare = DATEDIFF(ms, @start, @end);
73.  
74. SET @start = GETDATE();
75. SELECT * FROM TestNotNullDatetime WHERE DeletedAt <> CreatedAt;
76. SET @end = GETDATE();
77.  
78. SET @notnull_notequalcompare = DATEDIFF(ms, @start, @end);
79.  
80. SELECT @null_isnullcompare AS [NULL - IS NULL comparison],
81. @null_notequalcompare AS [NULL - NOT EQUAL comparison],
82. @notnull_notequalcompare AS [NOT NULL - NOT EQUAL comparison];
83.  
84.  
85. -- restore original state
86. SET NOCOUNT OFF;
87. Download this code: benchmarking.sql

The results are as follows:

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.