How to optimize sql server query by using datediff

Question

SELECT DISTINCT dbo.ufn_GetuserEmailId(hrb.UserID)
        ,''
        ,''
        ,0
        ,hrb.RID
        ,0
        ,'Photon Registration'
        ,(
            CASE 
                WHEN hrb.SourceID = 51
                    THEN 10655 /*10517*/
                WHEN hrb.SourceGroupID = 1
                    THEN 10518
                WHEN hrb.SourceID = 4
                    THEN  10656  /*10519*/
                WHEN hrb.SourceID = 1
                    THEN 10657 /*10520*/
                END
            )
        ,GETUTCDATE()
        ,hrb.UserID
        ,0
    FROM HC_RESUME_BANK hrb WITH (NOLOCK)
    INNER JOIN HC_USER_MAIN hum WITH (NOLOCK) ON hrb.UserID = hum.RID
    WHERE (
            hrb.SourceID IN (
                51
                ,1
                )
            OR (hrb.SourceGroupID = 1)
            OR (
                hrb.SourceID = 4
                AND isnull(hrb.SourceEmailId, '') <> ''
                AND hrb.SourceEmailId NOT LIKE '%integra%'
                )
            )
        AND hrb.SourceID <> 10647
        AND DATEDIFF(MI, hrb.CreatedDate, GETUTCDATE()) <= 5 

Answer 1

When filtering by a time difference compared to now you can pre-compute the lower bound date and use a simple greater than comparison which is much more efficient than evaluating a function for each record. We move the function evaluation outside of the query so that SQL so we only evaluate it once.

This trick changes the comparison such that you could do a direct lookup in an index, a function evaluations cannot do this.

Instead of:

WHERE ... DATEDIFF(MI, hrb.CreatedDate, GETUTCDATE()) <= 5 

Which translates to "Created in the last 5 minutes", we can calculate the timestamp 5 minutes ago and use that:

DECLARE @minDate DateTime = (SELECT DATEADD(MI, -5, GETUTCDATE()))
...
WHERE ... hrb.CreatedDate >= @minDate

Putting that all together:

DECLARE @minDate DateTime = (SELECT DATEADD(MI, -5, GETUTCDATE()));
SELECT DISTINCT dbo.ufn_GetuserEmailId(hrb.UserID)
    ,''
    ,''
    ,0
    ,hrb.RID
    ,0
    ,'Photon Registration'
    ,(
        CASE 
            WHEN hrb.SourceID = 51
                THEN 10655 /*10517*/
            WHEN hrb.SourceGroupID = 1
                THEN 10518
            WHEN hrb.SourceID = 4
                THEN  10656  /*10519*/
            WHEN hrb.SourceID = 1
                THEN 10657 /*10520*/
            END
        )
    ,GETUTCDATE()
    ,hrb.UserID
    ,0
FROM HC_RESUME_BANK hrb WITH (NOLOCK)
INNER JOIN HC_USER_MAIN hum WITH (NOLOCK) ON hrb.UserID = hum.RID
WHERE (
        hrb.SourceID IN (
            51
            ,1
            )
        OR (hrb.SourceGroupID = 1)
        OR (
            hrb.SourceID = 4
            AND isnull(hrb.SourceEmailId, '') <> ''
            AND hrb.SourceEmailId NOT LIKE '%integra%'
            )
        )
    AND hrb.SourceID <> 10647
    AND hrb.CreatedDate >= @minDate 

Performance should be increased instantly, but if you create an index on hrb.CreatedDate you might see even better performance gains.

---

Update

Please note that the use of DISTINCT will also have a significant impact on performance. If there really is a possibility of duplication then we usually advise to re-write the query so that there is no longer any duplication.

In this case you are joining on HC_USER_MAIN but are not querying any fields from that table, so we have to assume you are doing this for filtering purposes only. If the relationship with HC_USER_MAIN is 1:1 then I do not see any reason for the DISTINCT at all. If removing the DISTINCT clause does result in duplications then try to refactor out the join and change that to a lookup or WHERE IN correlation so that it doesn't affect the results.

---

Regarding NOLOCK

In response to comments NOLOCK here will only improve performance, at the risk of returning inconsistent results, but removing NOLOCK is only going to make this query slower. Yes the use of this hint should be challenged, but in many high frequency queries that poll a changing table, so very much like this one, it is common and usually appropriate to use the NOLOCK hint.

There is a good discussion here Is the NOLOCK (Sql Server hint) bad practice? but from a pure performance point of view, OP should leave it in.