Which plan is better?

Question

Which plan is better?

Jared

SSC Guru

Points: 61793
More actions
February 18, 2015 at 1:39 pm

#315485

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183686 More actions · Answer 1

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.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 2

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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Jared SSC Guru Points: 61793 More actions · Answer 3

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

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 4

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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Jared SSC Guru Points: 61793 More actions · Answer 5

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