T-SQL ISNULL() Optimization

Question

I have this clause on a join in a stored procedure I inherited:

WHERE a.is_active = 1
AND b.due_date <= ISNULL(@due_date_limit, b.due_date)

How would I re-write this to remove the ISNULL as it's causing severe performance problems?

Answer 1

AND @due_date_limit IS NULL OR b.due_date <= @due_date_limit

but I'm not so sure it would be that much faster.

Answer 2

in this instance, i would have an if statement, checking @due_date_limit

IF (@due_date_limit IS NULL)
BEGIN
    ...
    WHERE a.is_active = 1 --not required to compare b.due_date <= b.due_date
END
ELSE
BEGIN
    ...
    WHERE a.is_active = 1
    AND  b.due_date <= @due_date_limit
END

Answer 3

@due_date_limit is a stored procedure variable so it can be factored out from this query all together:

if (@due_date_limit is NULL) 
   <run query that works when @due_date_limit is NULL>
else 
   <run query that works when @due_date_limit is NOT NULL>

Answer 4

COALESCE(@due_date_limit, b.due_date)

may help

Answer 5

is there an index on due_Date? If not add one and then check the performance. If there already is, then Change to two separate statements

  If @due_date_limit is null
    Select [stuff]
    From Table
  Else
    Select [stuff]
    From Table  
    Where b.due_date <= @due_date_limit

But realize that not filtering (when @due_date_limit is null), or filtering using a <= can potentially return a substantial number of records that will eliminate any opportunity to use an index and will require a complete table scan. This may be what you are experiencing.

Answer 6

Index already on b.due_date ?

Answer 7

The most common cause for poor performance with this type of query is because the optimizer can't be sure what the typically value of @due_date_limit will be for most queries. Often the plan generated and re-used by subsequent executions favor the case where the value is null.

As of SQL 2005 you can guide the optimiser using the "OPTIMIZE FOR" option:-

WHERE a.is_active = 1
AND b.due_date <= ISNULL(@due_date_limit, b.due_date)
OPTION (OPTIMIZE FOR (@due_date_limit = '09/01/2009'))