asked
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?
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?
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 ;-)
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.
This was already discussed on this same forum
http://ask.sqlservercentral.com/questions/20337/fully-qualified-table-name.html 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)
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.
Not a direct answer but take a look at my blog where I took a detailed look at the execution time reported by SSMS - How to make sure you see the truth with Management Studio
http://www.simple-talk.com/community/blogs/jonathanallen/archive/2011/02/23/100137.aspx - Don't even believe SSMS when you think it's telling the truth
http://www.simple-talk.com/community/blogs/jonathanallen/archive/2011/02/28/100431.aspx SSMS is NOT the place to compare execution times, especially when you are discussing fractions of a second differences
7 People are following this question.
