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.
[viewcode] src=’http://facility9.com/wp-content/uploads/2008/07/benchmarking.sql’ showsyntax=”no” link=”yes” [/viewcode]
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.
