Table aliases
-
A general Question.
Are there any performance reasons for using abbreviated table aliases rather than using the full table name?
Regards
Rajesh
-
It basically makes the code easier to read/debug. Other than that sql server won't see any difference
... Although I could imagine that the less text the server has to process, the faster it goes, but I don't see that making a huge difference at the end of the day. -
Thanks very much for the reply.
It was my impression, but in Oracle, according to our Oracle DBA
- If an alias is not present, the engine must resolve which tables own the specified columns.
- A short alias is parsed more quickly than a long table name or alias. If possible, reduce the alias to a single letter.
Does it applies to SQL as well?
Regards
Rajesh
-
The resolving part will be true in any engine, that's why you must always specify from which table the data is comming from (when a join is used). As for the parsing part, I can only assume that if there's less data to parse, then it takes less time to do it but I can't provide any proof of that.
-
Rajesh - Remi's already (kinda) addressed the second point...as for the first - can't think how it would be resolved unless you explicitly spell out tableName.columnName when there is ambiguity and there're 2 or more tables in the query with the same column name!!!
And since you do have to explicitly identify the table - if the name is a long one it certainly saves on typing as well as (as the RG has already pointed out) increases readability a hundredfold...
**ASCII stupid question, get a stupid ANSI !!!** -
I must add here - "unless you have the typing speed of the RG"...for I notice that in the 3 minutes that it took me to type this out...remi's already posted a response and gone on to newer pastures...
**ASCII stupid question, get a stupid ANSI !!!** -
Gone and back again
. -
I am pretty much convinced that in order to avoid ambiguity, we should use alias names. Also if there is a self join, there in no way we could join except using aliases.
But I am not 100% convinced by these statements.
· If an alias is not present, the engine must resolve which tables own the specified columns.
· A short alias is parsed more quickly than a long table name or alias. If possible, reduce the alias to a single letter.
I am also aware that if used fully qualified object names sp_executesql would try to reuse the cached plans more effectively than without using the fully qualified object names.
Could anyone of you suggest any website which supports these performance gains if used table aliases. There won't be massive performance gain, however just out of curiosity...
Regards
Rajesh
-
Rajesh - the point I was trying to make is that for an ambiguous column name - you would HAVE TO explicitly use tableName - but it need not be an aliased name...(with self joins of course - as you pointed out - an alias is mandatory)...
as for websites - we'll be on the lookout and I'm sure someone will turn up sooner or later with a link and/or personal knowledge...
**ASCII stupid question, get a stupid ANSI !!!** -
For issues of 'alias-ing' tables in a query, one should have a 'site standard' <period>. Clarity and maintainabilty are the secondary goals in the composition of SQL (and stored procedures). The primary goal, of course, is for the query to work properly and efficiently. The 'esoteric' arguments of whether short or long alias names hardly seems worth the effort since this is something resovled in the query 'parse' stage prior to execution of the actual plan (OK maybe we burn 1 extra tic - 3 tics per millesecond). LIke I said, it hardly seems worth it.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
After decades of maintaining stored procedures, I have evolved into always using aliases for less typing and higher maintainability and readability and faster, easier editing. Since foreign key columns virtually always match the primary key column names, aliases make sense. Furthermore, the SqlServer best practices kit points out where aliases are missing in each stored procedure. I quickly assess the discipline and maturity of programmers on style things such as this.
-
I really appreciate all your valuable suggestions and comments, but I am keener on your comments on the following statements?
If an alias is not present, the engine must resolve which tables own the specified columns.
· A short alias is parsed more quickly than a long table name or alias. If possible, reduce the alias to a single letter.
What do you all reckon?
Regards
Rajesh
-
I reckon the time it takes the engine to parse a long alias vs a short alias is microscopic. The time it takes you to figure out what a large query is doing when it has a bunch of single-letter aliases is much more costly.
Not using a alias at all is probably a bad practice, but also more because of human readablility issues and not because it takes the server a few nanoseconds to figure it out.
The extra time (and you'd better have some pretty quick fingers on the stopwatch to notice it) to compile a longer query will only happen when it is compiled. The same thing applies to comments. A verbose stored procedure will execute with the same plan as one written with the minimum amount of typing (assuming they have the same function).
It's been a couple of decades since you had to worry about how much memory the compiler's symbol table takes up. Maybe you're reading something written by some 60's COBOL refugee.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply