August 27, 2004 at 9:46 am
I am new to ADO.Net programming so just a question.
We have an application that connects to SQL server using ADO.Net with CommandType.StoredProcedure. The developers do not qualify the commandtext with dbo.objectname. I remember reading that SQL server processes all SP calls against dbo first even if not specified (is this correct) ? Also, if i run a simple insert and loop in the C# code 100,000 time to simulate loading, if the dbo is specified in the comandtext this seems to give a 5-10 second performance increase. If dbo is not specified will this impact on performance and do I need to bring this up with the developers. I have already told them to specify in all SP code as this was also missing.
regards
Andrew Barton
August 30, 2004 at 3:40 am
http://www.sql-server-performance.com/stored_procedures.asp
For best performance, all objects that are called within the same stored procedure should all be owned by the same object owner, preferably dbo, and should also be referred to in the format of object_owner.object_name.
If the object owner's are not specified for objects, then SQL Server must perform name resolution on the objects, which causes a performance hit.
And if objects referred to in the stored procedure have different owners, SQL Server must check object permissions before it can access any object in the database, which adds unnecessary overhead. Ideally, the owner of the stored procedure should own all of the objects referred to in the stored procedure.
In addition, SQL Server cannot reuse a stored procedure "in-memory plan" over if the object owner is not used consistently. If a stored procedure is sometime referred to with its object owner's name, and sometimes it is not, then SQL Server must re-execute the stored procedure, which also hinders performance. [7.0, 2000] Updated 10-18-2003
Thanks,
Ganesh
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply