This is driving me crazy so I'm hoping somebody can tell me why this is happening. I have a select query with a sub query. The sub query returns 300 rows in under 1 second. Whenever I run the whole query though it's taking forever to run. I gave up after 10 minutes. But, if I put a TOP 10000000000 in the sub query the entire query finishes in under 1 second. Why is this happening? The sub query runs in under 1 second either way and returns the same number of rows.
Using the example query below TABLE1 has a composite PK index. I've rebuilt the index. The order of the fields I'm joining on match the order the PK clustered index was created on. I've created other indexes of each of the fields I'm joining on as well just to see if that worked. The estimated execution plan is the same with or without the TOP.
FROM TABLE1 A
INNER JOIN (
SELECT Val1,Val2, Val3, Val4, Val5, SUM(Val6)
FROM TABLE2 C INNER JOIN ServerlOtherTables D
ON C.ID = D.ID
WHERE Val6 IS NOT NULL AND Val7 = 3
GROUP BY Val1,Val2,Val3,Val4,Val5) B
ON A.Val1 = B.Val1
AND A.Val2 = B.Val2
AND A.Val3 = B.Val3
AND A.Val4 = B.Val4
AND A.Val5 = B.Val5