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 |
|
Hommer
Aged Yak Warrior
808 Posts |
 Posted - 2007-05-15 : 10:22:00
|
| Hi, every body,Please confirm my following thought. I have a client that has a lot of user defined stored proc that are named with the sp_ prefix, which I understand is normally/conventionally for system sp, such as sp_hlep.These sps are located in user databases instead of master. Other than bad naming, my concern is that the server will alway look into master first then current db for these sps, which will always have overhead accosiated with them.Is my concern legitimate? Will renaming them give me some boost on the performance? The ways these sps being called are, for instance from a DTS task with this syntax: exec sp_mystoredProc.Thanks! |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-05-15 : 10:25:26
|
| Yes. You are right.There definitely is an additional overhead of lookup in master db for the SP and it is not recommended to prefix proc with sp prefix.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
1fred
Posting Yak Master
158 Posts |
Posted - 2007-05-15 : 10:29:20
|
| If you call your SP with this syntax : database.user.sp_nameThe server will not go check in the master database first as you explicitly specify the right path to use. |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2007-05-15 : 10:41:37
|
| Great! Thanks for both your your replies.! |
|
|
|
|
|
|
user stored proc named with sp_