4 Replies - 867 Views - Last Post: 02 November 2012 - 07:18 AM Rate Topic: -----

#1 rgfirefly24  Icon User is offline

  • D.I.C Lover
  • member icon


Reputation: 267
  • View blog
  • Posts: 1,476
  • Joined: 07-April 08

2 part naming vs 3 part naming vs fully qualified naming schemes

Posted 30 October 2012 - 12:38 PM

So at work we are doing a review on some sql standards, and one of the pieces talks about how many parts should be included in a naming scheme. A little background for those who might not get what I'm talking about:

2 part naming scheme:
owner.Table
3 part naming scheme:
Database.owner.Table
Fully Qualified naming scheme:
Server.Database.owner.Table

So I've tried looking up performance difference between them and all I can find in the small performance difference between unqualified (table) and 2 part naming. I can't find anything that talks about 3 part or Fully qualified. My question is this: Is there a difference in performance with specifying the Database/Server on every table call, or does it not matter?

Is This A Good Question/Topic? 0
  • +

Replies To: 2 part naming vs 3 part naming vs fully qualified naming schemes

#2 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: 2 part naming vs 3 part naming vs fully qualified naming schemes

Posted 01 November 2012 - 01:47 AM

I tend to put in more than the table name itself only when it is required. A good example is when you are doing cross-database or cross-server joins. Nonetheless, by using the AS keyword, you need only fully qualify in the first reference, afterwards refering to the alias.

I've not thought about what the performance difference is between the various methods. I imagine there is little to none, especially if your DB server is taking advantage of resuing execution plans (as can be the case with SSMS / MSSQL). I have certainly never seen anything in query execution plans to do with aliasing or resolving names.

I think you'll find that the best performance increases you can get are writing your queries with good optimisation principles, and also applying indexes correctly. A good round of indexes, especially on large tables, can reduce query times by large amounts, 90% or more is not uncommon. I'm going to write a tutorial on indexing soon, if you're interested, shoot me a message and I'll keep you in the loop.

This post has been edited by e_i_pi: 01 November 2012 - 01:48 AM

Was This Post Helpful? 0
  • +
  • -

#3 rgfirefly24  Icon User is offline

  • D.I.C Lover
  • member icon


Reputation: 267
  • View blog
  • Posts: 1,476
  • Joined: 07-April 08

Re: 2 part naming vs 3 part naming vs fully qualified naming schemes

Posted 02 November 2012 - 03:30 AM

You are correct about proper optimization and index being 90% of what gives queries good performance. The standards i am reviewing talk specifically about 3 part and fully qualified naming giving performance benefit, but i havent been able to find anything that supports that, infact i think i read somewhere that prefixing a server name when running from that server slows performance because it still has to make a call out. also, I would like to be kept in the loop as you work on the index tutorial. Maybe this is for a part 2 or three, but good topics that I would love to see in an index tutorial are fragmentation and over use of non-clustered indexes.
Was This Post Helpful? 0
  • +
  • -

#4 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: 2 part naming vs 3 part naming vs fully qualified naming schemes

Posted 02 November 2012 - 04:34 AM

What are you running on? MSSQL+SSMS, MySQL+phpMyAdmin, PostgreSQL+phpPgAdmin, ...? There will likely be differences between DB software. MySQL, for instance, doesn't support schemas(ref), but does consider them analogous to databases.

Keep in mind that the time taken to properly lookup a resolved name will be during the compilation time, not the execution time. If the query is cached in some way (i.e. - the execution plan is kept and reused) then the additional time on the entire roundtrip will only be incurred during the first run.
Was This Post Helpful? 0
  • +
  • -

#5 rgfirefly24  Icon User is offline

  • D.I.C Lover
  • member icon


Reputation: 267
  • View blog
  • Posts: 1,476
  • Joined: 07-April 08

Re: 2 part naming vs 3 part naming vs fully qualified naming schemes

Posted 02 November 2012 - 07:18 AM

I am running on MSSQL + SSMS
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1