x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

Tuesday, March 27, 2007

Alias to be or not to be

 

EDIT:  This is bizzare..I ran multiple table joins, and reran it ovr and over, and the times are always different and one time one is faster than the othe and other times it's the other wway around.

We were having a discussion over at SQLTeam on whether to use full table name aliases or short aliases to label columns.  It always seemed to be a mattter of preference and debate on how self docuenting the code.  For m I will always use short aliases and make sure I lable every column, even if it's unique, just so when I come back to the code I don't have to guess or go to the data model.  Some suggest that that's not good enough and fully qualify the columns with the name.  I think that is overkill, but hey, to each his or her own. 

Then we got into a discussion about performance and if there was any.  My first thought was, no way...then someone asked if anyone had ever tested it...so I got to thinking...ys, yes, I know, a dangerous proposition.  And while this is not diffinitive by any means, and I've seen varying results, but the table without the aliases at all took longer.  I have to run some more complicated tests, but this is what we get from the following code.

ShortLabelTime
--------------
93

FullLabelTime
-------------
93

NoLableTime
-----------
126

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99 (Col1 int IDENTITY(1,1), Col2 char(1), Col3 datetime DEFAULT(GetDate()))
GO

DECLARE @x int
SELECT  @x = 1
WHILE @x < 10000
  BEGIN
 INSERT INTO myTable99(Col2) SELECT 'x'
 SELECT @x = @x + 1
  END
GO

DECLARE @s datetime

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SELECT @s = GetDate()
SELECT a.Col1, a.Col2, a.Col3 FROM myTable99 a WHERE Col1 = 5000
SELECT DATEDIFF(ms,@s,GetDate()) AS ShortLabelTime

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SELECT @s = GetDate()
SELECT myTable99.Col1, myTable99.Col2, myTable99.Col3 FROM myTable99 WHERE Col1 = 5000
SELECT DATEDIFF(ms,@s,GetDate()) AS FullLabelTime

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SELECT @s = GetDate()
SELECT Col1, Col2, Col3 FROM myTable99 WHERE Col1 = 5000
SELECT DATEDIFF(ms,@s,GetDate()) AS NoLableTime
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

 

posted @ Tuesday, March 27, 2007 2:59 PM | Feedback (2) | Filed Under [ SQL Server ]

Powered by:
Powered By Subtext Powered By ASP.NET