Two part naming convention and performance

As far as I can tell, the SQL Server gurus out here in the interwebs seem to be in general agreement that there are performance gains to be had by using a two-part table naming convention in your SQL. For example:

 SELECT shirt.sleeve_length
 FROM dbo.clothes shirt

Instead of

 SELECT shirt.sleeve_length
 FROM clothes shirt

(Also noting that when using linked servers or other dbs on the same server, you can get back to a two-part convention using synonyms)

Given the calibre of the commentators who espouse this view, there's a pretty good chance they're right :-)

My "shop" does not subscribe to this view though, because when they write their queries in SSMS and hit F5 there is no discernible difference in run time. "...so why should we waste our time adding the schema to everything?" I don't think their experimental design is particularly empirical, but I don't have an empirical design myself, that will provide evidence to the contrary.

Is there a way I can demonstrate empirically that a two-part table naming convention is better?

more ▼

asked May 16, 2012 at 10:37 PM in Default

avatar image

4.9k 42 52 58

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

The performance difference there is extremely small, but it's cumulative. The time you're saving is some internal system lookups that are eliminated by specifying the object owner. It's there. It's a fact. But to test it, you need to basically rewrite a majority of your queries and then put them into production. You just won't see it on a single execution of one query. You need hundreds of executions of hundreds of queries. Just explain to them that you're eliminating the internal look up which reduces every single execution of the query, every single time. 5 microseconds saved on every execution accumulates. Plus, how hard is it to comply with this?

But, it's not just for performance. It's also a good coding practice so that you can work with multiple schema's within your structures without getting errors. You're currently working in an environment that evidently only still uses the dbo schema. More and more designs are taking advantage of the added functionality of schema's and your schema-less code will be problematic.

There are a number of other practices that are the same. For example, don't use sp_ on procedure names. It leads to an additional search on a system database. End of the world stuff? Not at all. But why pay for the costs when you can so easily eliminate them?

more ▼

answered May 17, 2012 at 10:45 AM

avatar image

Grant Fritchey ♦♦
137k 20 47 81

Thanks Grant. "...only still uses the dbo schema..." We do use other schemata, but not to the concept's full potential. The idea was brought in by a bright young newcomer and roundly poo-pooed by the old timers. We have yet to come up against problems of, say, having the same table name in two different schemata. But your assumption is essentially right. They are seriously under-utilised, as is our use of synonyms (makes for a nightmare every time a server name changes). My battle to date has been getting the recalcitrants to appreciate the difference between a 20-second query and a 1-second query.

"...how hard is it to comply?..." That is a question of leadership, and I'll say no more about it ;-)

May 17, 2012 at 12:06 PM GPO

Sounds good. By the way, not using schema when you have multiples means that any tables referenced that are not part of the default schema, you'll get a compile error. So your code is going to start looking messy with some schema used in some places. Messy inconsistency for no purpose.

May 18, 2012 at 11:40 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

Not a direct answer but take a look at my blog where I took a detailed look at the execution time reported by SSMS

SSMS is NOT the place to compare execution times, especially when you are discussing fractions of a second differences

more ▼

answered May 17, 2012 at 11:12 AM

avatar image

Fatherjack ♦♦
43.8k 79 102 118

(comments are locked)
10|1200 characters needed characters left

This was already discussed on this same forum


Two good answers by Jeff Moden and TimothyAWiseman.

For the performance demonstration, I guess it would be pretty hard to show the very minimal cost of guessing the object schema. I can think of cache insert (cache not used) in case of adhoc/dynamic statements against the same table but with schema qualified and non-schema qualified, through SQL Profiler or by using a trace. Although the same is possibe by changing the text of the query even slightly.

The cache insert would also be the case if the table with same name is created later but not in the dbo/default schema (Seems like not done in your working environment)

more ▼

answered May 17, 2012 at 06:16 AM

avatar image

1.7k 1 5 10

Thanks for your feedback Robbin. "...This was already discussed on this same forum..." Not quite. My point is that if there's no way for me to PROVE that there's a difference in performance it's a bit rich for me to be telling my colleagues that there IS a difference. I'm trying to reconcile two seemingly contradictory positions. One that you should always use two part table names for performance reasons. The other is we can't demonstrate the performance effect because it is so minimal.

May 17, 2012 at 06:48 AM GPO
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: May 16, 2012 at 10:37 PM

Seen: 6126 times

Last Updated: May 18, 2012 at 11:40 AM

Copyright 2018 Redgate Software. Privacy Policy