SQL COUNT(*) takes too long to return

Question

I am working in SQL Server 2012. I have the following query:

SELECT
    COUNT(*), a.col3
FROM 
    a
INNER JOIN
    (SELECT
         d.x, c.col6
     FROM 
         c
     INNER JOIN 
         d ON d.x = c.col2
     WHERE
         c.col15 = 20
         AND c.col4 IN ('something', 'more')
    ) AS b ON b.x = a.col3
           AND b.col6 = a.col2
WHERE
    a.col10 = 20
    AND a.col2 IS NOT NULL
    AND a.col3 IS NOT NULL
GROUP BY
    a.col3

The tables are small. a and c are about 100k rows. d is about 100 rows.

However, the query is not returning anything even after 45+ minutes.

Unfortunately, I don't have access to view the estimated query plan. What do I need to change so that the query returns much more quickly?

Answer 1

Maybe your subquery if firing for each row in the outside query. You can rewrite it without a subquery:

SELECT
    COUNT(*), a.col3
FROM  
    a
INNER JOIN 
    c ON c.col6 = a.col2
INNER JOIN 
    d ON d.x = c.col2
      AND d.x = a.col3
WHERE
    a.col10 = 20
    --AND a.col2 IS NOT NULL
    --AND a.col3 IS NOT NULL
    AND c.col15 = 20
    AND c.col4 IN ('something', 'more')
GROUP BY
    a.col3

I commented out the IS NOT NULL parts in the WHERE clause b/c they will never be NULL since they are used in the join.