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 |
|
cwfontan
Yak Posting Veteran
87 Posts |
 Posted - 2010-12-01 : 12:16:42
|
| Is it bad practice to have two procedures and do something like the following..?select @i = count(id) from dbo.tblHist if @@rowcount < 100000 exec sp_TunedForSmallAmountOfDataelse exec sp_TunedForLargeAmountOfDataDifferences in the procs are the temp tables mainly... sp_TunedForSmallAmountOfData - Declares Table Variables and is very quick for small jobs sp_TunedForLargeAmountOfData - Creates Temp Tables and is quicker for large jobsThanks! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-12-01 : 12:41:29
|
| I think you will find that the performance difference (if any) between table variables and temp tables to be negligible. If someone can make a case that the table variable version of the stored procedure is so much faster that it makes sense, then I'd say it's reasonable. Without knowing the specifics, I doubt that the performance difference between the two would warrant a special case. So, ultimately, I would think you would want to go with just one version.Out of curiosity, do you know the performance difference between the two? |
 |
|
|
cwfontan
Yak Posting Veteran
87 Posts |
Posted - 2010-12-01 : 12:48:28
|
performace difference is very noticable.00:10(@tbl) vs 00:45(#tbl) seconds with small queries04:45(@tbl) vs 01:15(#tbl) seconds with large queriesThere is several temp tables and a very large procedure. So i would say it is possible to need two ways of handeling..?Thanksquote:
Originally posted by Lamprey
I think you will find that the performance difference (if any) between table variables and temp tables to be negligible. If someone can make a case that the table variable version of the stored procedure is so much faster that it makes sense, then I'd say it's reasonable. Without knowing the specifics, I doubt that the performance difference between the two would warrant a special case. So, ultimately, I would think you would want to go with just one version.Out of curiosity, do you know the performance difference between the two?
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
cwfontan
Yak Posting Veteran
87 Posts |
Posted - 2010-12-01 : 13:03:54
|
| yes.. thanks, |
|
|
|
|
|
|
|
|
bad practice or necessary evil? Performance tuning