Thursday, August 16, 2012

The evil of ISNULL()

So, this has happened to me a couple of times now, so I figured I'd share. The scenario is this: ETL developer comes to me and says "my ETL is running slow". When I look at what they're doing, they have something like this:

select *
from dbo.myTable a
inner join dbo.myOtherTable b
   on a.key = b.key
where isnull(a.col, '|') != isnull(b.col, '|')
Pardoning the "select *" (that's just me being lazy in typing the example) they're trying to find where the data has changed in a nullable column. Unforutnately, ISNULL isn't magic, so this leads to a SARGability problem. Said another way, it has to table scan both tables to figure out how to satisfy the query! While it's a little more verbose to write, the query optimizer is better able to deal with this:

select *
from dbo.myTable a
inner join dbo.myOtherTable b
   on a.key = b.key
where ( (a.col != b.col) or 
        (a.col is null and b.col is not null) or
        (a.col is not null and b.col is null) )
Now we've given the optimizer a better chance. The lastest time I've pulled this trick out of my bag, I was able to take a process that took ~40 seconds to one that took about 2. Twenty-fold increases in speed aren't anything to sneeze at. So remember this trick when you're trying to compare nullable columns and it will serve you well.

No comments:

Post a Comment