|
|
|
Old Hand
 
Group: General Forum Members
Last Login: Thursday, September 11, 2008 4:03 AM
Points: 325,
Visits: 36
|
|
Hi
I've noticed at our company stored procedures gets prefixed with "sp" and not "sp_". My question is does "spStoredProc" have the same penalties as "sp_StoredProc"
I know about "sp_" but what about "sp" ?
|
|
|
|
|
SSCommitted
Group: General Forum Members
Last Login: Today @ 9:07 AM
Points: 1,634,
Visits: 4,209
|
|
It is only procedures with 'SP_' prefix that cause the performance impact of scanning the system databases first.
'SP' should be fiine.
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Friday, March 12, 2010 12:42 AM
Points: 10,
Visits: 31
|
|
steveb (7/2/2008)
It is only procedures with 'SP_' prefix that cause the performance impact of scanning the system databases first.
'SP' should be fiine.
So, isn't some similar scanning occur when all the stored procedures start with the same prefix? ('SP' in this case). Not maybe for the system databases, but within the procedures in that specific context?
Although I have not tested it properly, it seems that one should name his/her stored procedures as unique as possible for the maximum performance, starting from the first character.
Were any tests done on that?
|
|
|
|
|
SSCommitted
Group: General Forum Members
Last Login: Today @ 9:07 AM
Points: 1,634,
Visits: 4,209
|
|
adnan.korkmaz (7/3/2008)
steveb (7/2/2008)
It is only procedures with 'SP_' prefix that cause the performance impact of scanning the system databases first.
'SP' should be fiine.
So, isn't some similar scanning occur when all the stored procedures start with the same prefix? ('SP' in this case). Not maybe for the system databases, but within the procedures in that specific context?
BOL only talks about not using 'sp_, another reason is that they may come up with the same name as your procedure on a future release.
Although I have not tested it properly, it seems that one should name his/her stored procedures as unique as possible for the maximum performance, starting from the first character.
I don't see how naming your stored procedures with the same prefix would affect performance at all. It has always been the norm in places I have worked to have a common procedure prefix as part of the naming standards.
Were any tests done on that?
I haven't done any tests my information comes from BOL.
|
|
|
|
|
SSCommitted
Group: General Forum Members
Last Login: Wednesday, February 10, 2010 5:03 PM
Points: 1,621,
Visits: 401
|
|
HI
Check out the below link
http://www.sqlservercentral.com/Forums/Topic8041-65-1.aspx
http://www.sqlmag.com/Article/ArticleID/23011/sql_server_23011.html
Thanks -- Vj
http://dotnetvj.blogspot.com
Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers
|
|
|
|
|
Mr or Mrs. 500
Group: General Forum Members
Last Login: Thursday, March 11, 2010 2:15 PM
Points: 596,
Visits: 556
|
|
Well i think everybody should start using the schema based Sepration Procedures for all objects. that will also be helpful in distinguishing the Objects and relate them to specific criterias.
Mani Singh
www.dbanation.com
|
|
|
|
|
SSC-Addicted
Group: General Forum Members
Last Login: Today @ 5:00 AM
Points: 402,
Visits: 467
|
|
I would recommend as a MINIMUM that all the stored procs you create be abbreviated usp_ for "user stored procedure." Not only does that avoid the sp_ issue but it keeps your stored procs seperate.
|
|
|
|
