I'm having performance issues with this query. If I remove the status column, it runs very fast; but adding the subquery in the column section delays the query way too much  (1.02 min execution). How can I modify this query so it runs faster while still getting the desired data?The reason I put that subquery there is because I needed the status for the latest activity; some activities have null status so I have to ignore them.Establishments: 6.5k rows -EstablishmentActivities: 70k rows - Status: 2 (Active, Inactive)SELECT DISTINCT    est.id,    est.trackingNumber,    est.NAME AS 'establishment',    actTypes.NAME AS 'activity',    (        SELECT stat3.NAME        FROM SACPAN_EstablishmentActivities eact3        INNER JOIN SACPAN_ActivityTypes at3            ON eact3.activityType_FK = at3.code        INNER JOIN SACPAN_Status stat3            ON stat3.id = at3.status_FK        WHERE eact3.establishment_FK = est.id          AND eact3.rowCreatedDT = (            SELECT MAX(est4.rowCreatedDT)            FROM SACPAN_EstablishmentActivities est4            INNER JOIN SACPAN_ActivityTypes at4                ON est4.establishment_fk = est.id                AND est4.activityType_FK = at4.code            WHERE est4.establishment_fk = est.id              AND at4.status_FK IS NOT NULL          )          AND at3.status_FK IS NOT NULL    ) AS 'status',    est.authorizationNumber,    reg.NAME AS 'region',    mun.NAME AS 'municipality',    ISNULL(usr.NAME,  '') + ISNULL(+ ' ' + usr.lastName,  '')        AS 'created',    ISNULL(usr2.NAME, '') + ISNULL(+ ' ' + usr2.lastName, '')        AS 'updated',    est.rowCreatedDT,    est.rowUpdatedDT,    CASE WHEN est.rowUpdatedDT >= est.rowCreatedDT         THEN est.rowUpdatedDT         ELSE est.rowCreatedDT    END AS 'LatestCreatedOrModified'FROM SACPAN_Establishments estINNER JOIN SACPAN_EstablishmentActivities eact    ON est.id = eact.establishment_FKINNER JOIN SACPAN_ActivityTypes actTypes    ON eact.activityType_FK = actTypes.codeINNER JOIN SACPAN_Regions reg    ON est.region_FK = reg.code --INNER JOIN SACPAN_Municipalities mun    ON est.municipality_FK = mun.codeINNER JOIN SACPAN_ContactEstablishments ce    ON ce.establishment_FK = est.idINNER JOIN SACPAN_Contacts con    ON ce.contact_FK = con.id--JOIN SACPAN_Status stat ON stat.id = actTypes.status_FKINNER JOIN SACPAN_Users usr    ON usr.id = est.rowCreatedBy_FKLEFT JOIN SACPAN_Users usr2    ON usr2.id = est.rowUpdatedBy_FKWHERE (con.ssn = @ssn OR @ssn = '*')  AND eact.rowCreatedDT = (    SELECT MAX(eact2.rowCreatedDT)    FROM SACPAN_EstablishmentActivities eact2    WHERE eact2.establishment_FK = est.id)--AND est.id = 6266ORDER BY 'LatestCreatedOrModified' DESCI can't upload the execution plan because of rep points, but there's a 21% on one of the establishment activity tables.
Subquery performance too slow
performance;sql;sql server