I’ve been working on performance tuning a stored procedure that has many optional parameters and is dynamicized by using the always optimizer confusing COALESCE in the WHERE clause (along with other SARG hiding constructs that cause indexes to be ignored).
The stored procedure is called with a date range defined in the parameters @StartDate and @EndDate which are optional.
, @StartDate DATETIME = NULL
, @EndDate DATETIME = NULL
The parameters are used in the WHERE clause with a COALESCE.
WHERE StartDate >= COALESE(@StartDate, StartDate)
AND EndDate <= COALESE(@EndDate, EndDate)
Using this technique causes the query optimizer to ignore the indexes that have been created on the StartDate and EndDate columns.
One method to fix this problem is initialize the @StartDate and @EndDate with some minimum and maximum dates that make sense for the data being queried as the parameter defaults.
, @StartDate DATETIME = '01/01/2009'
, @EndDate DATETIME = '01/01/2010'
Another way is to initialize the parameters in the procedure body for the case where the default dates need to be calculated.
-- If the date parameters are not supplied,
-- make them 1 month in the past and today
-- to make the query optimizer happy
IF @StartDate IS NULL
SET @StartDate = DATEADD(MONTH, -1, GETDATE())
IF @EndDate IS NULL
SET @EndDate = GETDATE()
The WHERE clause for either of these cases would change.
WHERE StartDate >= @StartDate
AND EndDate <= @EndDate
Now the query optimizer has good SARGs and will do a much better job utilizing those indexes that are using all that space in the database.
Using this technique resulted in a 400% performance improvement for the stored procedure that was being tuned.