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.

Wednesday, August 15, 2012

I'm speaking at SQL Saturday #149!

I got confirmation this morning that I'll be speaking at SQL Saturday #149 at the University of Minnesota (my alma mater). Hope to see you there!