Sql-server – query tuning help

sql server 2014sql-server-2008sql-server-2012

All,

Would need some tips / tricks to tune this query please:

WITH Candidates(ActivityId) AS(
    SELECT ActivityId
    FROM EmailHashBase WITH (NOLOCK)
    WHERE ActivityId IN
        (
            SELECT ActivityId
            FROM EmailHashBase WITH (NOLOCK)
            WHERE HashType = 0
                AND Hash IN (3901304,293296907)
            GROUP BY ActivityId
            HAVING COUNT(ActivityId) >= 2
        )
        AND HashType = 1
        AND Hash IN (1169472434)
    GROUP BY ActivityId
    HAVING   COUNT(ActivityId) >= 1
)
SELECT b.ActivityId
FROM (
    SELECT 
        a.ActivityId, 
        ROW_NUMBER() over (ORDER BY a.ModifiedOn DESC) as Rownumber
    FROM ActivityPointerBase AS a WITH (NOLOCK)
    WHERE a.ActivityId IN
        (
            SELECT ActivityId
            FROM Candidates AS c
            WHERE(
                    (
                        SELECT COUNT(*)
                        FROM EmailHashBase AS h
                        WHERE h.ActivityId = c.ActivityId
                            AND h.HashType   = 0
                    ) <= 2
                ) 
            AND ((SELECT COUNT(*) FROM EmailHashBase AS h WHERE h.ActivityId = c.ActivityId AND h.HashType = 1) = 1)
        )
    )b
WHERE Rownumber = 1

Best Answer

Too many subqueries you have in a single query (...where in (select...))

This usually is an anti-performance pattern

Try breaking down your query on smaller queries so you gather data step by step, and by using #temp tables - you can put intermediary results into #temp tables, and query them in following queries

Approach with breaking down complex query on smaller parts and using #temp tables to hold intermediary results - also reduces blocking, so you won't have to use NOLOCK hint

Beware that NOLOCK hint can be sometimes used in Development environment, but strongly NOT recommended in Production environment