Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2008-10-23 : 14:33:55
|
| Hi,I'm working with a fellow developer on a project, and he's using alot of templates for SPROCS. I just came across a situation where we had needless params for paging (page size + page index), sorting via column, and sort direction for a SPROC.In this particular query we know none of these params will be used, so we end up creating a temp table and inserting into for nothing.I think this is a very big deal, as performance is a major issue, but I am not the best at reading and analyzing traces. I have pasted both execution plans below, and to me it seems quite obvious what the better choice is.Is there a way I can come up with a number so I can say, execution plan "A" is 50% faster than execution plan "B" ? Whats your opinion ? |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2008-10-23 : 14:36:28
|
| No sense wasting CPU and possibly IO to populate a temp table and then simple select everything back out of it. So, yes, that is a poor pattern/design to use when you don't need to. |
 |
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2008-10-23 : 14:43:45
|
quote:
Originally posted by Lamprey
No sense wasting CPU and possibly IO to populate a temp table and then simple select everything back out of it. So, yes, that is a poor pattern/design to use when you don't need to.
100% agree.... do you know an effective way I can I benchmark the 2 different versions ? |
|
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2008-10-23 : 17:27:34
|
Not sure if I am following, Why not include Cliebnt statistics on it and just compare the two?
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.\_/ _/ _/\_/ _/\_/ _/ _/- 881 |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2008-10-23 : 19:12:43
|
| you could run them in the same batch and check the numbers.Also, you could check some other statistics (like IO) using:SET STATISTICS PROFILE ONGOSET STATISTICS IO ONGO |
|
|
|
|
|
|
|
|
SPROC general templates for RAD