Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Alias yes or no for Performance

Author  Topic 

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-03-27 : 02:30:32
Hi all,
I have a small argument with friend here regarding use of alias names for tables will affect the performance of the query in case of a large database. Please provide your thoughts on the same.

Necessity is the mother of all inventions!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-27 : 02:32:09
The one who says "no" wins...


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-03-27 : 02:46:35
Thank you Peter Larsson
I win than.

Necessity is the mother of all inventions!
Go to Top of Page

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-03-27 : 05:30:03
but the one who says "aliases improve readability"...wins that other arguement!!!
Go to Top of Page

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-03-27 : 06:44:27
Sorry Andrew we were only arguing on performance of the query on very large databases. We both agree that readability is improved.


Necessity is the mother of all inventions!
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-03-27 : 13:19:57
quote:
Originally posted by AndrewMurphy

but the one who says "aliases improve readability"...wins that other arguement!!!

Absolutely not. The use of aliases servers more often to make code obscure, and is a crutch for those who cannot type well.

e4 d5 xd5 Nf6
Go to Top of Page

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-03-27 : 13:51:30
alias would mean some detail reading to be done, but i dont think anything is hidden Blindman.Typing not well is not reason since u always have ctrl+c and ctrl+v.

Necessity is the mother of all inventions!
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-27 : 13:51:59
Type?

You mean cut and paste...

In any case, it's too bad we can't do a poll



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-03-27 : 13:52:52
Peso: is there really a perf downside to using aliases? I can't imagine why. wouldn't the parser produce the same parse tree either way? is the query plan affected somehow? any resource links on this topic?

blindman: sometimes less is more. at the cmd prompt, do you like to type dir, or would you rather type PleaseGiveMeListOfFilesAndDirectoriesInThisDirectory?


www.elsasoft.org
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-27 : 13:55:16
quote:
Originally posted by blindman

quote:
Originally posted by AndrewMurphy

but the one who says "aliases improve readability"...wins that other arguement!!!

Absolutely not. The use of aliases servers more often to make code obscure, and is a crutch for those who cannot type well.

e4 d5 xd5 Nf6



you are joking, right?

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-03-27 : 14:02:07
Is there anyone who has actually tested alias vs. no alias, or is this just old wives tales and/or anecdotes?










CODO ERGO SUM
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-27 : 14:03:28
quote:
Originally posted by jsmith8858

quote:
Originally posted by blindman

quote:
Originally posted by AndrewMurphy

but the one who says "aliases improve readability"...wins that other arguement!!!

Absolutely not. The use of aliases servers more often to make code obscure, and is a crutch for those who cannot type well.

e4 d5 xd5 Nf6



you are joking, right?

- Jeff
http://weblogs.sqlteam.com/JeffS




No, he is not...we've had this debate many times over at dbforums

He prefers

SELECT Orders.OrderId FROM Orders



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-03-27 : 14:05:21
I tried testing on a tables which had rows like 1 million each with no indexes. But they seem to work at the same time.

Necessity is the mother of all inventions!
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-03-27 : 14:07:53
quote:
Originally posted by jezemine

blindman: sometimes less is more. at the cmd prompt, do you like to type dir, or would you rather type PleaseGiveMeListOfFilesAndDirectoriesInThisDirectory?


www.elsasoft.org

"Dir" is not an alias. It is the name of the command. Would it frustrate you to sit down to debug a new batch file system where the prior developer had renamed the command from "Dir" to "D", to save some typing? How long would it take you to figure out what was going on?

e4 d5 xd5 Nf6
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-27 : 14:11:30
Blind dude,

With my short alias, I have no problem...I just make sure I label all the columns so I know where everything is coming from, even if they are unique...it's self documenting

and the debate rages on



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-03-27 : 14:25:01
quote:
Originally posted by blindman

quote:
Originally posted by jezemine

blindman: sometimes less is more. at the cmd prompt, do you like to type dir, or would you rather type PleaseGiveMeListOfFilesAndDirectoriesInThisDirectory?


www.elsasoft.org

"Dir" is not an alias. It is the name of the command. Would it frustrate you to sit down to debug a new batch file system where the prior developer had renamed the command from "Dir" to "D", to save some typing? How long would it take you to figure out what was going on?

e4 d5 xd5 Nf6



point taken. you must have been burned by some poorly named aliases in the past.

it doesn't mean the concept is a bad one though. In any case we don't write code to demonstrate or improve our typing skills - we do it mainly because it's loads of fun, right? (except when you have to type out some really long table name...) that's why I do it anyway.

also I refuse to believe that aliases cause a perf problem until someone shows me a credible reference. parsers just don't work that way.


www.elsasoft.org
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-27 : 14:27:58
I forgot, I know how to clear the PROCACHE, but how do I free the buffers?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-27 : 14:34:13
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Tara Kizer
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-27 : 15:02:17
Thanks Tara...I keep getting different results, but the last several runs show only that table without the alias, in this case a single table, runs longer...bizarre, a short alias a full alias run the same.

I'll have to make a more complicated test with many tables

http://weblogs.sqlteam.com/brettk/archive/2007/03/27/60141.aspx



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2007-03-27 : 15:13:05
I believe that in a query plan, all names are converted to their object IDs. This includes table names, column names, and as I found after restoring one under a new ID one day, database names as well. The only conceivable argument could have to do with compilation time. You >might< be able to make the case that compilation would be shorter with no aliases at all, but that would be a matter of a few CPU ticks at best, and not worth the effort.
Go to Top of Page

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-03-27 : 15:25:43
talk about premature optimization...


www.elsasoft.org
Go to Top of Page
    Next Page

- Advertisement -