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:
(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?
asked May 16 '12 at 10:37 PM in Default
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?
answered May 17 '12 at 10:45 AM
Grant Fritchey ♦♦
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
answered May 17 '12 at 11:12 AM
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)
answered May 17 '12 at 06:16 AM