February 18, 2015 at 1:39 pm
I have 2 plans generated by a proc that I altered to get the second plan. The only difference is that I changed the IF...ELSE IF...ELSE IF... to IF... IF... IF...
This broke the plan up a bit and I "think" that the second plan is better. I am just starting to get into understanding plans better, so any advice would be great.
Plans attached. Thanks in advance!
Jared
CE - Microsoft
February 18, 2015 at 2:27 pm
I wouldn't say that a plan is better than the other one, just different. The only difference is that in one plan it's a single statement and in the other one there are multiple statements.
The validations are not significant so you shouldn't see any differences on performance.
February 19, 2015 at 5:17 am
I wouldn't say either is better either. From what I can tell I think I'd have the same issue with both. I think you're going to get a single plan regardless of parameters passed the way these are set up. As long as that seek operation works better than a scan, you'll be fine, but if you need a scan, the seek could be problematic.
How's the tan?
"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
February 19, 2015 at 6:52 am
Thanks! I guess I Was looking too much into the last subtree cost and seeing the separate plans made me think that the separate would be better, but I suppose it is really just the graphical representation that was misleading. Darn ASPState! 🙂
Grant Fritchey (2/19/2015)
How's the tan?
Its not a tan, its a peeling burn lol
Jared
CE - Microsoft
February 19, 2015 at 8:04 am
It's usually very dangerous to compare cost to cost between different queries. Since the costs are just estimates, when the query changes, so do the calculations. Costs are good within a plan, but not between plans.
"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
February 19, 2015 at 8:41 am
Grant Fritchey (2/19/2015)
It's usually very dangerous to compare cost to cost between different queries. Since the costs are just estimates, when the query changes, so do the calculations. Costs are good within a plan, but not between plans.
Good to know... Then no changes 🙂 Trying to squeeze as much as possible out of ASPState.
Jared
CE - Microsoft
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply