| Author |
Topic |
|
DestinyJack
Starting Member
22 Posts |
 Posted - 2005-08-10 : 00:12:38
|
| Will it affect the performance if I use stored procedure name like "sp_Get_PartnerData" in a database other than the master database? |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-08-10 : 00:52:20
|
| Not there wont be any performance issueTo avoid confusion with system sps use something different like mysp_Get_PartnerDataMadhivananFailing to plan is Planning to fail |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-10 : 01:02:47
|
Sorry MAD, I think they will be a performance issue!BoL:quote:
It is strongly recommended that you do not create any stored procedures using sp_ as a prefix. SQL Server always looks for a stored procedure beginning with sp_ in this order: The stored procedure in the master database.The stored procedure based on any qualifiers provided (database name or owner).The stored procedure using dbo as the owner, if one is not specified. Therefore, although the user-created stored procedure prefixed with sp_ may exist in the current database, the master database is always checked first, even if the stored procedure is qualified with the database name.Important If any user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.
So I reckon "look in master first" has got to have a performance costKristen |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-08-10 : 01:09:01
|
| Thanks Kris. I was not aware on thatMadhivananFailing to plan is Planning to fail |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-08-10 : 01:13:44
|
| Yes there will be a performance issue. Procedures starting with "sp_" cause a procedure cache miss every time, thus annoying SQL Server so much it will seek revenge on the "DBA" who named it.DavidMA front-end is something that tries to violate a back-end. |
|
|
|
DestinyJack
Starting Member
22 Posts |
Posted - 2005-08-10 : 01:20:51
|
| Thanks for the info, now I got another idea to improve the performance. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-10 : 01:34:27
|
| Putting the "dbo." in front of the name whenever you EXEC it will help performance too.Kristen |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-08-10 : 07:52:42
|
| And on a related note, qualify table names in stored procedures with the owner name. ie: dbo.myTable Sql server will look first for the table name owned by the connected user before looking for dbo owned tables. And on another related note, for those of you who use sp_executesql and expect plans to be cached, fully qualify the table names (db.owner.tbname). Otherwise sql won't cache the plans.Be One with the OptimizerTG |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-10 : 14:46:57
|
"fully qualify the table names (db.owner.tbname)"I've always wondered about that - have you got to go all the way back up to DB, or is owner.MyTable enough?I know BoL says "Fully qualified" - but where do you stop? server.db.owner.table?  Kristen |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-08-10 : 14:54:18
|
| I had always assumed they meant qualified to the db level. Mostly because that's what's in their example. But it also makes sense because just like 2 users can own tables with the same name, different databases can also contain the same table name so the plan needs to know that as well. I wouldn't think the server qualification would be necessary, but honestly I don't really know how execution plans deals with remote calls.Be One with the OptimizerTG |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-10 : 15:10:42
|
| I was coming from the perspective that I don't qualify any of the code in my SProcs to the DB level, only to the owner, so I wonder if I need to on an sp_executeSQLIt would be impractical to code SProcs to the DB level (we'd have to rewrite them for every customer, and between the TEST and PRODUCTION DBs!)For sp_ExecuteSQL its probably not so bad becuase, ipso facto, the SQL is dynamic, so sticking a DB_Name in there is a minor inconveience. I'd just like to be sure I have to go to that much trouble though!Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-08-10 : 15:16:15
|
| The qualification should be OwnerName.ObjectName. You only need to qualify to the third and fourth level if you need to get to an object that is not in your current database/server.When SQL Server can't find the object in the current database, it doesn't look to another database, except of course sp_ which looks in master first then the current database.Tara |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-10 : 15:28:28
|
| Thanks Tara, most helpfulKristen |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-08-10 : 16:48:52
|
sp_executesql FYI:since this quote from bol on sp_executesql is a little ambivilant (seems to me)quote:
Note If object names in the statement string are not fully qualified, the execution plan is not reused.
(given that their example qualifies up to the database level)I tried testing to see if the execution plan is resused if only qualified to owner name. The plan seems to be cached and resused even without the owner name ???Did I test correctly? I simply queried master..syscacheobjects for my sql like <mySql> (I made sure to make the sql statement something no one else would use). I saw that for ExecutablePlan the usecount was incrementing after each call to sp_executesql (with different parameters passed to each call).Probably another case where improvements were made and the documentation not updated. So in the impmortal words of Emily Litella regarding my hint above about fully qualified objectnames when using sp_executesql.....nevermind.Be One with the OptimizerTG |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-10 : 17:05:33
|
| I've had the suspicion, when using sp_ExecuteSQL before, that the plan was being cached when I knew I had little or no qualified names - that's partly why I was interested in a definitive answer.Kristen |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-08-10 : 21:30:44
|
| I recall reading (somewhere, sometime) that the cache was case sensitive.dbo.spmyCamel - dbo.spmycamel !?rockmoose |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-11 : 01:31:21
|
| I'm OK on that one! We treat all our SQL as case sensitive so it will run on a case-sensitive server - if it has to!Kristen |
|
|
|
|
Using "sp_" at the begining of Stored Procedure