June 10, 2014 at 11:14 am
Hi,
I want to post my execution plan but I don't want to display my table names. How to edit and post it here.
I do have a query which involved sub query. I changed to join and executed but it is also taking same time. These below ones are execution plans I got from one with sub query and one with join.
How can I say based on execution plan one is better than another when both are taking same time.
1) Select Nested Loops Clustered Index Scan
Cost: 0% (Left Semi Join) (Cost: 16%)
Cost: 0%
Table Spool Nested Loop Index Seek (Non Clustered)
(Lazy Spool) Inner Join Cost: 16%
Cost :53% Cost: 0%
Key Lookup
Cost: 16%
2) Select HashMatch Nested Loop Index Seek (Non Clustered)
0% Inner Join Inner Join Cost: 12%
Cost:64% Cost: 0%
Key Lookup
Cost: 12%
Clustered Index Scan
Cost:12%
June 10, 2014 at 11:41 am
http://www.sqlsentry.com/products/plan-explorer/sql-server-query-view
this is a free tool and you can "anonymise|"
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 10, 2014 at 12:01 pm
There's not a real way to say Plan A is better than Plan B. Instead, Plan A results in behavior 1,2,3 and Plan B results in behavior 4,5,6. It's the comparison of the behaviors that determine which plan is better. You just look to the plans to understand where you might see opportunities to change the query or the table structures in order to eliminate what might not be "good" in the plan, such as the key lookup or the scan you have in both plans right now.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply