CODEWITHSUNDEEP
sqlsql-servert-sqloptimization
Frozenskys
Frozenskys

Reputation: 4410

T-SQL ISNULL() Optimization

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?

Upvotes: 6

Views: 8301

Answers (7)

johntrepreneur
johntrepreneur

Reputation: 4694

Since @due_date_limit is a stored procedure variable, you can just check it for NULL before the query and set it to a default value if need be, thus eliminating the ISNULL check in the WHERE clause.

IF (@due_date_limit IS NULL)
BEGIN
    SET @due_date_limit = '09/01/2009';
END

and then your WHERE clause would simply look like this:

WHERE a.is_active = 1
AND b.due_date <= @due_date_limit

Upvotes: 0

AnthonyWJones
AnthonyWJones

Reputation: 189457

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'))

Upvotes: 3

Charles Bretana
Charles Bretana

Reputation: 146449

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.

Upvotes: 2

manji
manji

Reputation: 47978

COALESCE(@due_date_limit, b.due_date)

may help

Upvotes: 1

topchef
topchef

Reputation: 19783

@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>

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166376

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

Upvotes: 7

Jonas Elfstr&#246;m
Jonas Elfstr&#246;m

Reputation: 31428

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.

Upvotes: 3

Related Questions