Posted by Erland Sommarskog on 02/19/07 23:14

(teddysnips@hotmail.com) writes:
> 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.
>
> 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?

All stored procedures in a database (and all tables, all views etc)
belongs to a schema, and the full name within the database is
schema.procedure. If you leave out the schema when you create your
procedure, the procedure is created in your default schema. If you are
the database owner the default is "dbo". On SQL 2000, your default
schema is always the same as your user name. But in SQL 2005, owners
and schema are separeate, and all users can have dbo as their default
schema.

In many databases, all objects are in the dbo schema.

It follows from this, that whatever the performance problems with
your procedure due to, it was not the dbo prefix. (Unless you recreated
the procedure in your default schema which have tables that are
namesakes with those in the dbo schema - but are much smaller.)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]