SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Alias yes or no for Performance
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 6

Vijaykumar_Patil
Posting Yak Master

India
121 Posts

Posted - 03/27/2007 :  02:30:32  Show Profile  Visit Vijaykumar_Patil's Homepage  Click to see Vijaykumar_Patil's MSN Messenger address  Reply with Quote
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

Sweden
29910 Posts

Posted - 03/27/2007 :  02:32:09  Show Profile  Visit SwePeso's Homepage  Reply with Quote
The one who says "no" wins...


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Vijaykumar_Patil
Posting Yak Master

India
121 Posts

Posted - 03/27/2007 :  02:46:35  Show Profile  Visit Vijaykumar_Patil's Homepage  Click to see Vijaykumar_Patil's MSN Messenger address  Reply with Quote
Thank you Peter Larsson
I win than.

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

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

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

Vijaykumar_Patil
Posting Yak Master

India
121 Posts

Posted - 03/27/2007 :  06:44:27  Show Profile  Visit Vijaykumar_Patil's Homepage  Click to see Vijaykumar_Patil's MSN Messenger address  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 03/27/2007 :  13:19:57  Show Profile  Reply with Quote
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

India
121 Posts

Posted - 03/27/2007 :  13:51:30  Show Profile  Visit Vijaykumar_Patil's Homepage  Click to see Vijaykumar_Patil's MSN Messenger address  Reply with Quote
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 - 03/27/2007 :  13:51:59  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2884 Posts

Posted - 03/27/2007 :  13:52:52  Show Profile  Visit jezemine's Homepage  Reply with Quote
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

USA
7423 Posts

Posted - 03/27/2007 :  13:55:16  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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)

USA
7020 Posts

Posted - 03/27/2007 :  14:02:07  Show Profile  Reply with Quote
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 - 03/27/2007 :  14:03:28  Show Profile  Reply with Quote
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

India
121 Posts

Posted - 03/27/2007 :  14:05:21  Show Profile  Visit Vijaykumar_Patil's Homepage  Click to see Vijaykumar_Patil's MSN Messenger address  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 03/27/2007 :  14:07:53  Show Profile  Reply with Quote
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 - 03/27/2007 :  14:11:30  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2884 Posts

Posted - 03/27/2007 :  14:25:01  Show Profile  Visit jezemine's Homepage  Reply with Quote
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 - 03/27/2007 :  14:27:58  Show Profile  Reply with Quote
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

USA
35953 Posts

Posted - 03/27/2007 :  14:34:13  Show Profile  Visit tkizer's Homepage  Reply with Quote
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

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

X002548
Not Just a Number

15586 Posts

Posted - 03/27/2007 :  15:02:17  Show Profile  Reply with Quote
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 - 03/27/2007 :  15:13:05  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2884 Posts

Posted - 03/27/2007 :  15:25:43  Show Profile  Visit jezemine's Homepage  Reply with Quote
talk about premature optimization...


www.elsasoft.org
Go to Top of Page
Page: of 6 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000