tim_cs
asked on
TOP Query Performance Improvement
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.
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.
SELECT *
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
You can increase the performance of query by applying individual index on each item used in Group clause.
ASKER
I've already tried adding additional indexes. I am noticing some differences in the execution plan now whenever I run with and without the TOP. Also, putting the sub query into a CTE doesn't make any difference but using a temp table does.
I'm just trying to understand what's happening and why some methods work and other don't even though they are returning the same exact rows.
I'm just trying to understand what's happening and why some methods work and other don't even though they are returning the same exact rows.
is there any input variable, or is the statement static?
ASKER
Nope, no input variables. The entire query is static.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi You can also use some condition to shorten your sub query result.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You need to look at the execution plans. Look at the details on the parts that are a high % of the task, or that have large arrows (more data being transferred). Check also for Lazy/eager spools.
ASKER
I figured I would just have to change the query around I was just more interested in understanding why is was working that way. Thanks for the feedback.