Performance hit when stored proc name begins with SP ?

31 Aug 2006 2:19 PM

VMI

Is it true that there's a performance degradation if the stored procedure
name begins with SP? A colleague at work read that recently, but couldn't
find the article. I just wanted to verify it.

Thanks.

31 Aug 2006 2:22 PM

Immy

if a sp is named sp_ then SQL server will attempt to read it from the master
database first!
I'm not sure if this is still true in SQL2005. But your colleague is
correct.

and here's your article -
http://www.sqlmag.com/Article/ArticleID/23011/sql_server_23011.html
Immy

Show quote

"VMI" <V**@discussions.microsoft.com> wrote in message
news:E599558D-76BA-4108-904D-94BF907573EF@microsoft.com...
> Is it true that there's a performance degradation if the stored procedure
> name begins with SP? A colleague at work read that recently, but couldn't
> find the article. I just wanted to verify it.
>
> Thanks.

31 Aug 2006 2:23 PM

SQL Menace

http://www.sqlmag.com/Article/ArticleID/23011/sql_server_23011.html

Denis the SQL Menace
http://sqlservercode.blogspot.com/

VMI wrote:
Show quote

> Is it true that there's a performance degradation if the stored procedure
> name begins with SP? A colleague at work read that recently, but couldn't
> find the article. I just wanted to verify it.
>
> Thanks.

31 Aug 2006 2:55 PM

Arnie Rowland

NOT for just SP, but for SP_ there is a small performance 'wiggle'. The
query processor looks in the master database first for procedures starting
with SP_. If not found there, then it looks in the current database. That
causes an extremely 'tiny' waste in performance.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quote

"VMI" <V**@discussions.microsoft.com> wrote in message
news:E599558D-76BA-4108-904D-94BF907573EF@microsoft.com...
> Is it true that there's a performance degradation if the stored procedure
> name begins with SP? A colleague at work read that recently, but couldn't
> find the article. I just wanted to verify it.
>
> Thanks.

31 Aug 2006 3:01 PM

SQL Menace

There is another potential problem, if you name a proc sp_SomeName and
in the next version of SQL server that name is chosen by Microsoft for
one of their procs you will have a problem

Denis the SQL Menace
http://sqlservercode.blogspot.com/

Arnie Rowland wrote:
Show quote

> NOT for just SP, but for SP_ there is a small performance 'wiggle'. The
> query processor looks in the master database first for procedures starting
> with SP_. If not found there, then it looks in the current database. That
> causes an extremely 'tiny' waste in performance.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
>
> "VMI" <V**@discussions.microsoft.com> wrote in message
> news:E599558D-76BA-4108-904D-94BF907573EF@microsoft.com...
> > Is it true that there's a performance degradation if the stored procedure
> > name begins with SP? A colleague at work read that recently, but couldn't
> > find the article. I just wanted to verify it.
> >
> > Thanks.