Estimated Execution Plan - interpreting the results
-
Hi all,
Having used SQ server daily for many years, I have finally found the need/time to use execution plans in some more complex code.
The below is my estimated execution plan for a view that contains 4 left outer joins and multiple currency conversions based on the results of those joins.
It appears I have a sort that is costing 78% whilst most other steps don't get over 4%.
I am wanting to tune the query to relieve this issue but unsure as to what will make a difference.
Can anyone offer any advice please?
-
A reduction in Cost for Sort Operations would be to bring the columns being sorted in the right order by an appropriate index so the sort actually doesn't have to happen.
-
Hi Dino, thanks for the reply.
I actually ended up simplifying the left outer joins as there were some casts and conversions on one of the sides. As I control both tables in the join, I was able to add in a computed column to the reference table that removed the need for the converts/casts in the join.
The nett result is that the that view was searching circa 280,000 records in about 30 minutes is now running in under 3 minutes. I may add the index you suggested as well and see if I can improve further.
Best wishes,
Dave
-
Sounds like you're progressing well. I can't see the execution plan to make any suggestions. Also, an execution plan isn't a picture. It's all the data in the properties behind the picture. If you really want some concrete suggestions, post the full plan here.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Hi Grant, that's really kind thanks. What format would you prefer the plan in? Text in an Excel file?
Cheers,
Dave
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply