Slow performing T-SQL query with two joins to the same table

Question

I am struggling with figuring out what is happening with the T-SQL query shown below.

You will see two inner joins to the same table, although with different join criteria. The first join by itself runs in approximately 21 seconds and if I run the second join by itself it completes in approximately 27 seconds.

If I leave both joins in place, the query runs and runs and runs, until I finally stop the query. The appropriate indices appear to be in place and I know this query runs in a different environment with less horsepower, the only difference being the other server is running SQL Server 2012 and I am running SQL Server 2016, although the database is in 2012 compatibility mode:

This join runs in ~21 seconds.

SELECT
    COUNT(*)
FROM 
    dbo.SPONSORSHIP as s
INNER JOIN 
    dbo.SPONSORSHIPTRANSACTION AS st 
        ON st.SPONSORSHIPCOMMITMENTID = s.SPONSORSHIPCOMMITMENTID
        AND st.TRANSACTIONSEQUENCE = (SELECT MIN(TRANSACTIONSEQUENCE)
                                      FROM dbo.SPONSORSHIPTRANSACTION AS ms
                                      WHERE ms.SPONSORSHIPCOMMITMENTID = s.SPONSORSHIPCOMMITMENTID
                                        AND ms.TARGETSPONSORSHIPID = s.ID)

This join runs in ~27 seconds.

SELECT
    COUNT(*) 
FROM
    dbo.SPONSORSHIP AS s
INNER JOIN 
    dbo.SPONSORSHIPTRANSACTION AS lt ON lt.SPONSORSHIPCOMMITMENTID = s.SPONSORSHIPCOMMITMENTID 
        AND lt.TRANSACTIONSEQUENCE = (SELECT MAX(TRANSACTIONSEQUENCE) 
                                      FROM dbo.SPONSORSHIPTRANSACTION AS ms 
                                      WHERE ms.SPONSORSHIPCOMMITMENTID = s.SPONSORSHIPCOMMITMENTID 
                                        AND s.ID IN (ms.CONTEXTSPONSORSHIPID, 
                                                     ms.TARGETSPONSORSHIPID, 
                                                     ms.DECLINEDSPONSORSHIPID) 
                                        AND ms.ACTIONCODE <> 9)

Answer 1

These are both considered correlated subqueries. You should typically avoid this pattern, as it causes what is known as "RBAR"... which is "Row by Agonizing Row". Before you focus on troubleshooting this particular query, I'd suggest revisiting the query itself and see if you can solve this in a more set based approach. You'll find that in most cases you have other ways to accomplish this and cut cost down dramatically.

As one example:

select
    total_count
    ,row_sequence
from 
    ( 
    SELECT
    total_count = COUNT(*)
    ,row_sequence = row_number() over(order by st.TRANSACTIONSEQUENCE asc)
    FROM 
        dbo.SPONSORSHIP as s
    INNER JOIN dbo.SPONSORSHIPTRANSACTION AS st
    ON st.SPONSORSHIPCOMMITMENTID = s.SPONSORSHIPCOMMITMENTID
) as x
where
    x.row_sequence = 1

This was a quick example that is not tested. For future reference, if you want the best answer, it's a great idea to generate a temp table or test data set that's able to be used so someone can provide a full working example.

The example I gave shows what is called a windowing function. Take a look more into them for helping with selecting results when you see the word sequence, need the the first/last in a group and more.

Hope this gives you some ideas! Welcome to Stack Overflow! 👋