The Gerasus Software Blog

When creating a table with a column that will hold the name of a stored procedure that feeds an EXEC command, make sure the data type of this column is sysname. The data type of this column should not be VARCHAR(50) or some other arbitrary data type that should just do the job. This will also be true for columns that hold table and column names. Any column in a user table that holds meta data from a Sql Server system table should match the data type of the source system table column.

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.

<code snippet>
, @StartDate DATETIME = NULL
, @EndDate DATETIME = NULL
</code snippet>

The parameters are used in the WHERE clause with a COALESCE.

<code snippet>
WHERE StartDate >= COALESE(@StartDate, StartDate)
AND EndDate <= COALESE(@EndDate, EndDate)
</code snippet>

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.

<code snippet>
, @StartDate DATETIME = '01/01/2009'
, @EndDate DATETIME = '01/01/2010'
</code snippet>

Another way is to initialize the parameters in the procedure body for the case where the default dates need to be calculated.

<code snippet>
-- 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()
</code snippet>

The WHERE clause for either of these cases would change.

<code snippet>
WHERE StartDate >= @StartDate
AND EndDate <= @EndDate
</code snippet>

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.