Using fully qualified names
-
Hello Everyone,
I am trying to find out if it is necessary to use fully qualified names for adhoc sql statements to improve performance. Say for example I am running the query below on Query Analyzer on database dbname. Would it be necessary for me to use
SELECT * FROM dbname.dbo.table
Or will it be the same as using SELECT * FROM dbo.table
Could you point me to some Microsoft documentation either way please. Thanks in advnace for your advice.
-
The whole catch on names involves how SQL Server does the lookup. If no owner is specified, SQL Server looks for objects in the following manner:
1) user.object
2) dbo.object
Therefore, if the user doesn't match the owner (user isn't dbo... which the user normally shouldn't be), it's best to specify the owner of the object.
The exception is with any stored procedures starting with sp_. SQL Server will look in the master database for the stored procedure before performing the other two lookups.
K. Brian Kelley
@kbriankelley -
Brian's advice is accurate, but I don't think he is answering the right question. You want to know if you need to specify the database name in the query.
You only need to specify the database name if the database is not the one you are connected to.
Kathi Kellenberger
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Vas,
You do not have to specify a database name if you are sure you are inside the database you need to query. If you use Query Analyzer and the the database dropdown you select Pubs database then you can select from Pubs tables without specifying the database name. But you will have to specify Northwind database name if being connected to Pubs you want to query Northwind. As an alternative, you may use USE statement to change the focus:
USE PUBS
select * from authors
select * from Northwind.dbo.orders
USE Northwind
select * from pubs.dbo.authors
select * from orders
Regards,Yelena Varsha
-
To address the direct question of whether you need to provide full names to improve performance, no. Not unless you're neurotic about it. The SQL Server lookups to resolve the object references during the Prepare step will not even compare (or be measurable) to the process time to actually execute the SQL. This is especially true if you are only talking about the DBName component. Even if you have thousands of tables explicitely providing the "dbo." part won't provide a noticable performance gain (if that is even an option for your connection). If it is an SP then it's entirely moot because the plan is reused, but then you asked about adhoc queries so I expect you had that one figured out already. Spend your performance analysis time elsewhere. No need to worry about this one. Happy coding.
-
Oops. I missed the part of the database name. Kathi and Yelena are correct, of course. If you're within the database, you're fine, otherwise you do need to specify the db.
Aaron, not specifying the owner for a stored procedure can cause a performance hit. If you don't specify the owner on a stored procedure SQL Server may have initial difficulty finding the execution plan. This puts an exclusive lock on the stored procedure, serializing it (meaning only one process can use it at a time). Obviously this is a performance hit. Here's a KB article covering the recompile issue.
INF: SQL Blocking Due to [[COMPILE]] Locks
I wrote a bit about all the various causes of stored procedures recompiling here:
K. Brian Kelley
@kbriankelley -
Thanks bkelley. Read, learned (hopefully), and virtual briefcased. I guess we have a bit of work ahead of us. Our application (third party initiated) now has about 1700 stored procedures and while our development environment uses dbo to connect our QA and Production environments do not and prefix is never specified in the EXEC statements. It looks like we'll be making a lot of VB changes to add 'dbo.' to execs and since a good number of SPs EXEC other SPs we have a bit of work there. Oh boy.
-
Brian,
Would the lookup work without qualification the same for every user that may also be aliased to DBO? When the article talks about the DB owner they are refering to the the one actual DBO that you normally get by being the official DB owner. Then an aliased DBO would work the same in lookups as the first and proper DBO?
-
Thanks to all for your responses.
I should have worded my question a little better. Aaron caught the drift of my question though. My question was specifically referencing ad hoc sql statements and their performance.
I read a little bit about Ad Hoc caching last night and this is what I understood. Please let me know if I am wrong.
When an ad hoc sql statement is issued such as SELECT * FROM dbname.dbo.table and assuming the compile cost is not cheap, it puts the ad hoc plan in the cache and sets the initial cost to 0. When the same query comes along next time (exact textual match), it finds the plan in cache and uses it and increments the cost by 1. The significance of having a cost of 0 is the plan can be removed from buffer cache when need be.
Thinking about it, it doesn't matter if I use the dbname in ad hoc sql statements. It would still look for exact textual matches. If the following sql statement SELECT * FROM dbo.emp is reused many times, it would definitely find the query plan in the cache. I do understand that it knows to execute the query in the context of the database Query Analyzer is under. SELECT * FROM dbo.emp is not the same as SELECT * FROM dbname.dbo.emp and has a different plan.
Brian, I have read your article about Stored Procedures and caching and it helped me undertand Procedure Cache. In reference to your article, I do understand that using the owner name is very important. What about the database name? I think that unless we are coding across databases or across servers, there is no need to reference the server name or the database name. I believe and my tests show that there are no cache misses when either of the following statements are executed on Query Analyzer
EXEC servername.dbname.dbo.procname
EXEC dbname.dbo.procname
EXEC dbo.procname
Thanks again for your time.
Vas Lakshman
-
Yes, if you alias the user to dbo it'll try dbo.object first. Easy way to test: create a stored procedure in Northwind owned by dbo and given execute to public.
CREATE PROC dbo.usp_Test
AS
SELECT @@VERSION
GO
GRANT EXECUTE ON dbo.usp_Test TO Public
GO
Set up Profiler to trace and add the SP:Starting, SP:CacheHit, SP:CacheInsert, SP:CacheMiss, SP:CacheRemove, SP:ExecContextHit, SP:Recompile, and SP:Completed events.
Create a normal user, log in as that user, go to Northwind and execute the stored procedure without specifying the owner. The first time you will get a CacheMiss because the execution plan doesn't exist. Execute the stored procedure again without specifying the owner. You'll get an initial CacheMiss but then you should get a ExecContextHit right after it. Now, execute specifying the owner. You'll only get the ExecContextHit. Log off the user and then alias the user in Northwind to dbo. Log back on and execute without specifying the owner. You will only get an ExecContextHit.
K. Brian Kelley
@kbriankelley -
You do not have to specify the server and database name in order to cache. Here's an interesting test. Execute the following adhoc query from master:
SELECT * FROM sysobjects WHERE xtype = 'P'
Then do a SELECT * FROM syscacheobjects to see the execution plan cached (and parameterized). It's important to note that if you do specify a database name it caches the execute plan as a different one altogether:
SELECT * FROM master.dbo.sysobjects WHERE xtype = 'P'
Now do a SELECT * FROM syscacheobjects and you'll see an execution plan entry for each query.
K. Brian Kelley
@kbriankelley -
Thanks for your response Brian.
I ran the test you showed. I see an entry in syscacheobjects for the first query, however, I do not see an entry for the second one. I do not see an entry when I run the following statement either
SELECT * FROM dbo.sysobjects WHERE xtype = 'P'
-
Hrm. Mine cached 'em both. Odd.
K. Brian Kelley
@kbriankelley -
Excellent Discussion!
I just would like to add one thing unrelated to the original question but important for the quering across databases and owners.
SQL Server SP3 (and SP3A) introduced a new feature that is disabled by default: "Allow Cross-Database Ownership Chaining..."
This feature must be enabled either on the individual databases level or on the server level if you do have ownership chaning. I had 3 cases when rolling out SP3 with default options when 3 applications were brocken and I had to fix then by checking this checkbox.
One scenario is when you prefere to store data in one database, data access stored procedures in another database, user credentials and state management in yet another database. This setup was very polular several years ago.
Regards,Yelena Varsha
-
Just a small point of clarification. Cross DB Ownership Chaining is present in SQL Server 7.0 and 2000. However, we had no option to turn it off until SQL Server 2000 SP3.
Best practice is to leave it off at the server level. If you must have it, turn it on at the database level. Keep in mind master and tempdb require it, model cannot have it.
K. Brian Kelley
@kbriankelley
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply