-2

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
2
  • 1
    Why? What is wrong with your current query? What should be better? Please add some details to your question. Hit edit and describe what your intentions are. You might want to have a look at the Help Centre article How to Ask. Welcome to DBA.SE. – John K. N. Apr 15 '20 at 8:25
  • Also, try to explain in plain English what the query is about to do. – vonPryz Apr 15 '20 at 8:27
1

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

0

Conditions look strange. For example:

AND ((SELECT COUNT(*) FROM EmailHashBase AS h WHERE h.ActivityId = c.ActivityId AND h.HashType = 1) = 1)

but c.ActivityId comes from CTE Candidates where you have such filter:

AND HashType = 1
        AND Hash IN (1169472434)
    GROUP BY ActivityId
    HAVING   COUNT(ActivityId) >= 1

It seems you can remove the first condition if you change the last line of CTE to "HAVING COUNT(ActivityId) = 1".

The same thought about this part:

WHERE(
                    (
                        SELECT COUNT(*)
                        FROM EmailHashBase AS h
                        WHERE h.ActivityId = c.ActivityId
                            AND h.HashType   = 0
                    ) <= 2
                ) 

but you already filtered your CTE this way:

WHERE HashType = 0
                AND Hash IN (3901304,293296907)
            GROUP BY ActivityId
            HAVING COUNT(ActivityId) >= 2

So you could use "HAVING COUNT(ActivityId) = 2" and rewrite your original query this way:

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)
    JOIN Candidates AS c ON a.ActivityId = c.ActivityId
     )b
WHERE Rownumber = 1

Not the answer you're looking for? Browse other questions tagged or ask your own question.