SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Why Object Qualification is important.


Why Object Qualification is important.

Author
Message
srienstr
srienstr
Mr or Mrs. 500
Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)

Group: General Forum Members
Points: 508 Visits: 459
Bother. At least I have them running separately through the Job Agent rather than calling each other so I'll only have to change them in one place. Thanks for letting me know Roy.


Puto me cogitare, ergo puto me esse.
I think that I think, therefore I think that I am.
JJ B
JJ B
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1137 Visits: 2860
Fun article and great discussion. Thanks.
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43821 Visits: 14925
I did a quick test kind of following Gregory's suggestion except I did not use profiler I user Set Statistics Time On. The first thing I did was create a user who did not "own" the dbo schema nor have dbo as it's default schema, but did have select permissions on the dbo schema. I wanted to make sure I was crossing schemas. I also ran DBCC DropCleanBuffers and DBCC FreeProcCache between each execution. Then I ran it only executing the DBCC statements before the first run of each query. The query did use a covering non-clustered index as well. I was trying to stick it in here, but decided the formatting wasn't appropriate so I attached an Excel file instead.

A quick summary is that, if the plan is in cache they took about the same time. When the plan was not in cache, the non-qualified query took about 33ms to parse and compile and the qualified query to 1ms to parse and compile. This is a very big difference in performance.

I also ran it with the users default schema being DBO, results not in the Excel File, and the Parse and Compile times were the same after running the DBCC statements. This would lead me to believe that in SQL 2005, if you have only 1 schema, that they both perform the same way. I am not going to make any definitive statements based on this limited testing though.

I think the biggest argument for using fully qualified names in ad-hoc SQL is consistency so that you only get 1 plan in cache for each query vs. 1 for non-qualified queries and 1 for qualified queries. This allows you to have more distinct plans in the cache.

Paul,

I guess I was slightly confused in terminology between compiled and cached. I know that ad-hoc queries plans are cached and, after looking it up in BOL, realize that this is what compilation really is. Thanks for the prompt.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Attachments
Object Qualification Test.xls (31 views, 21.00 KB)
Roy Ernest
Roy Ernest
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8461 Visits: 6891
Hey Jack,
Thanks for the excel file. Thats a pretty good testing.. Smile

-Roy
Jason1972
Jason1972
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 167
To recap...is the consensus that using non-fully qualified object names, including for tables, stored procedures, and other objects, will have a performance hit for both SQL 2000 and SQL 2005?
Roy Ernest
Roy Ernest
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8461 Visits: 6891
Sideout, Yes it is always better to call objects with their qualifier.

-Roy
sjsubscribe
sjsubscribe
Mr or Mrs. 500
Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)

Group: General Forum Members
Points: 555 Visits: 595
Good article, but perhaps a bit overreaching in its conclusions: "The best thing to do is to make sure you qualify all objects with the Schema (Also known as Owner)"

Well, good advice if you're dealing with one server and single schemas or need to optimize for in specific instances. But if you're writing procedures that need to run across many schemas, servers, and automatically pick the right tables without undue hard coding makes sense.

For example, I have several lookup tables with the same names but with different data on different servers, schemas, and so on. But my stored procedures are the same across all machines. By not using dbo, I let the appropriate table get picked up at execution time. Price I pay is minimal compared to managing customized code for each server. To save compute cycles, fully qualified names could make a difference in certain circumstances, but to save programmer cycles, then the substitution model could be your friend.
srienstr
srienstr
Mr or Mrs. 500
Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)

Group: General Forum Members
Points: 508 Visits: 459
sjsubscribe (3/10/2008)
Well, good advice if you're dealing with one server and single schemas or need to optimize for in specific instances. But if you're writing procedures that need to run across many schemas, servers, and automatically pick the right tables without undue hard coding makes sense.

For example, I have several lookup tables with the same names but with different data on different servers, schemas, and so on. But my stored procedures are the same across all machines. By not using dbo, I let the appropriate table get picked up at execution time. Price I pay is minimal compared to managing customized code for each server. To save compute cycles, fully qualified names could make a difference in certain circumstances, but to save programmer cycles, then the substitution model could be your friend.

Do you have difference lookup tables in the same database with different owners, or are they owned by dbo but in different databases/on different servers? If you need different results within the same database then the substitution method makes sense, but using the two-part name wouldn't impact the functionality you're looking for if the relevant tables are all owned by dbo in different databases.

Three part naming is obviously right out in your situation, but my understanding is that three part naming has no performance advantage to the more flexible two part naming.


Puto me cogitare, ergo puto me esse.
I think that I think, therefore I think that I am.
sjsubscribe
sjsubscribe
Mr or Mrs. 500
Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)

Group: General Forum Members
Points: 555 Visits: 595
Do you have difference lookup tables in the same database with different owners, or are they owned by dbo but in different databases/on different servers?


Yes.

he best thing to do is to make sure you qualify all objects with the Schema...


No. It's not the best thing.
srienstr
srienstr
Mr or Mrs. 500
Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)Mr or Mrs. 500 (508 reputation)

Group: General Forum Members
Points: 508 Visits: 459
sjsubscribe (3/10/2008)
Do you have difference lookup tables in the same database with different owners, or are they owned by dbo but in different databases/on different servers?


Yes.

Are you saying that both apply, or was I unclear in phrasing my question?
he best thing to do is to make sure you qualify all objects with the Schema...


No. It's not the best thing.

While basically everything falls into "It depends", it is still often, if not quite universally, advisable. 32ms of compile time may be insignificant, and a few ms on each run may be acceptable, but unless there is a specific need to choose a less efficient method, efficient is preferred.


Puto me cogitare, ergo puto me esse.
I think that I think, therefore I think that I am.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search