SQL Server Performance Forum – Threads Archive

Fully qualified object names

Hello! According to this articlehttp://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/rdbmspft.mspx you can gain som performance if you use fully qualified object names in your queries. Has this been tested? What was the result? For example:
SELECT * FROM Shippers WHERE ShipperID = 3 Should instead be:
SELECT * FROM Northwind.dbo.Shippers WHERE ShipperID = 3 Why is there a performance gain? Since when you connect you already connect as a user and have a default database specified. Shouldnt the first place to look for the table be there? In the article they say it will make it easier for SQL Server to choose an already existing execution plan.

Set the execution plan and see. I think both will take same time Madhivanan Failing to plan is Planning to fail

There are performance benefits to using fully qualified names even though the execution plans are the same. For example: Say you login to sql server using the user name "fred" and you run the following. SELECT * FROM Shippers WHERE ShipperID = 3 SQL Server will look in the cache to see if this resultset can be found there. Now, initially SQL Server will look for the table fred.Shippers (because that is your username). This will generate a cache miss and then SQL Server will look for dbo.Shippers. The same applies to stored procedures. By not qualifying your object names when calling them you could be making SQL Server look in the cache for that object twice. This of course wouldn’t happen if you logged in using an sa account because such an account will be assuming the "dbo" user by default. Of course, you shouldn’t be letting people log in using an sa account so the chances are that you’ll gain something by fully qualifying the object names you call. Hope that helps,

quote:Originally posted by brimba Hello! According to this articlehttp://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/rdbmspft.mspx you can gain som performance if you use fully qualified object names in your queries. Has this been tested? What was the result? For example:
SELECT * FROM Shippers WHERE ShipperID = 3 Should instead be:
SELECT * FROM Northwind.dbo.Shippers WHERE ShipperID = 3 Why is there a performance gain? Since when you connect you already connect as a user and have a default database specified. Shouldnt the first place to look for the table be there? In the article they say it will make it easier for SQL Server to choose an already existing execution plan.

Karl Grambow www.sqldbcontrol.com

You might want to read this:http://www.sqlservercentral.com/columnists/bkelley/procedurecache.asp —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

You’re correct about specifying the database: that’s not really an issue if your T-SQL is running against a single database. There is an improvement in performance over time when you always mention the object owner, because when you don’t then SQL Server must determine which object you’re actually referring to. First, it will look for an object with that name owned by the current login, and if that cannot be found then it starts looking for an object owned by dbo. The underlying problem here is actually that SQL Server cannot reuse execution plans if it doesn’t know beforehand the owner of the object, because in that case it doesn’t know which statistics to trust, and it has to resort to recompilation.

Is this true for stored procedures as well? I mean, we always run our t-sql-statements in a stored procedure. What If we call our stored procedure with [DB].[Owner].[SPName], and then in our stored procedure do something like: select username from users where id = 1 What will happen then? Thanks for all the good information!

Both inside the SP code referencing objects, and on the outside calling the procedure, always include the owner prefix. Same issue.

]]>