Posted by Greg D. Moore \(Strider\) on 02/19/07 16:44

<teddysnips@hotmail.com> wrote in message
news:1171898428.745133.216550@v33g2000cwv.googlegroups.com...
> In a system I'm maintaining there is a Stored Procedure called
> dbo.MyStoredProcedure. I didn't create this - it was created by a
> developer who has now left. I don't know how the object came by its
> "dbo." prefix, but I think he created it in QA.
>

dbo = database owner.

This is actually fairly common.

So not really sure what you're finding unusual here.

However, that said, you should call all stored procs with the owner
qualifier included.

Example:

stored proc FOO

Created by the sa so it's qualifed as:

dbo.FOO

Now user BAR comes along and calls:

exec FOO

First SQL Server will check to see if there is a stored proc BAR.FOO and try
to execute that. If not, THEN it'll look up dbo.FOO and try to execute
that.

Sounds like what's happening here. (Or something similar.) (note it's even
worse if it's named sp_xxxx).

Not sure why it would take 4-10 seconds, but I suspect that's part of the
issue.


> Anyway, there were some performance issues (it was taking between 4
> and 10 seconds to complete) so I copied the SQL into a QA window and
> it consistently ran in under 1 second. So I created a new SPROC with
> SQL exactly identical to the old one, but without the "dbo." prefix,
> and that too runs in <1 second.
>
> Any thoughts?
>
> Edward
>


--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

[Back to original message]