asked
Nested loops in query execution plan
when I generate the execution plan for the query which is similar to the below one, I see that 97% of the query cost is for Nested Loop (Left semi Join) ![alt text][1] How can I optimize this query to bring down the cost for nested loops? Indexes are already present for the columns used in the tables being referenced in Join conditions. SELECT A,B,.....Z FROM dbo.Table (NOLOCK) USR LEFT JOIN CTETable1on 1= 1 LEFT JOIN CTETable2 ON USR.ColA = CTETable2.ColA LEFT JOIN CTETable3 ON USR.ColB = CTETable3.ColB LEFT JOIN CTETable4 ON USR.ColC = CTETable4.ColC WHERE Exists ( Select 1 from Table2 where ColZ = 3) [1]: /storage/temp/1258-sqlplan.jpg
Without seeing the whole execution plan and detailed information, it's very hard to say. I'd want to see an actual plan in order to compare the estimated rows to actual. I'd also want to see what the bottom input looks like. You have 400K rows going through this thing now. That seems a little big for a Loops join. A merge would be better and a hash might also work better. Could be your stats are out of date. AGain, just guessing since I can't see everything. Also, you're getting a parallel plan. With 400k rows working through it, you may need it, but, a lot of the time, the cost of setting up parallel execution offsets any benefits it confers. I'd be curious what the full cost of the plan was. When working with plans for tuning, just remember that the highest operator costs are just sign posts. They're not necessarily problems, just the most likely place where problems might be occurring. It's not a measure of load though. It's an estimation based on the statistics available to the optimizer.
15 People are following this question.
