There are many WOMs (Word of Mouth) within SQL Server, and one is not to use a scalar function (if possible) with a column name in a query.  Today we will examine a fairly simplistic query I found at a client site that initially was performing fairly well.  But what I didn’t know was the frequency of the calls to that query (and I’ll leave that to another discussion).

Consider the following query:

SELECT AlertID,AlertTypeID,UserID,[Subject],Body,SentFlag,DateSent,DateAdded,AddedBy
             ,DateUpdated,UpdatedBy,DelFlag,Archived
       FROM dbo.Alert
       WHERE DelFlag       = 'N'
         AND AlertTypeID   = @p_nAlertTypeID
         AND UserID        = @p_nUserID
         AND DATEDIFF(minute,DateUpdated,GETUTCDATE()) <= @p_nLimitMinutes;

where there are about 4.5m rows in the Alert table and DateUpdated is an embedded column.  This was within a small stored procedure and had average execution times about 0.7 sec. 

Using SET STATISTICS IO,TIME ON there were 2434 logical page reads, cpu time of 187ms, duration of 525ms, and a total query cost of 171.5.

By revising the last line of the query to:

  SELECT AlertID,AlertTypeID,UserID,[Subject],Body,SentFlag,DateSent,DateAdded,AddedBy
               ,DateUpdated,UpdatedBy,DelFlag,Archived
         FROM dbo.Alert
         WHERE DelFlag       = 'N'
           AND AlertTypeID   = @p_nAlertTypeID
           AND UserID = @p_nUserID
           AND DateUpdated > DATEADD(minute,-@p_nLimitMinutes,GETUTCDATE());

We now 2424 logical reads, cpu time of 78ms, duration of 397ms with a total query cost of 2.03 (about 1 percent of the first query cost).

If we look at the actual query plans themselves:

we see that while the query plans look the same, the cost values are vastly different. This is primarily because within the first query (where the DateDiff function had the DateUpdated column as a parameter) as opposed in the second query the Column DateUpdated essentially being compared directly to a scalar value.

In any case, the WOM still holds true: Try not to use a scalar function with a table column as a parameter!