#0169-SQL Server-Table/Column Aliasing does not affect query performance-Part 2


Earlier this week, I wrote a post on whether table and column aliasing affects query performance or not (http://beyondrelational.com/modules/2/blogs/77/Posts/14948/0168-sql-server-tablecolumn-aliasing-does-not-affect-query-performance.aspx) . We were able to determine that overall time-wise, both queries were performing equally well. However, one of the comments received as part of the feedback was to also include results of the following in addition to the use of simply the execution plan.

  • Statistics IO
  • Statistics Profile
  • Actual CPU times

I therefore repeated the tests after modifying the query a little bit to include the necessary SET statements and setting the buffer to a clean state.

IMPORTANT NOTE: In this script, we use 2 DBCC commands – FREEPROCCACHE and DROPCLEANBUFFERS to simulate a cold buffer condition, which is generally achieved when SQL Server restarts. Doing so indiscriminately can have severe performance implications. Please do not execute the scripts shown in this example in your production or quality assurance environments.

USE AdventureWorks2012
GO

/*************************************************************************************************************
                                      IMPORTANT WARNING
--------------------------------------------------------------------------------------------------------------
DBCC FREEPROCCACHE and DROPCLEANBUFFERS are used to simulate a cold buffer condition, 
that is generally achieved when SQL Server restarts. 

Doing so indiscriminately can have severe performance implications. 

Please do not execute the scripts shown in this example in your production or quality assurance environments.

The author, this site or Microsoft are not responsible
for any damage caused by indiscriminate use of these DBCC commands
*************************************************************************************************************/
DBCC FREEPROCCACHE WITH NO_INFOMSGS
GO
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
GO

SET STATISTICS IO ON
SET STATISTICS PROFILE ON

SELECT 'Non-Aliased Query',
       HRE.BusinessEntityID,
       HRE.LoginID,
       HRE.NationalIDNumber,
       HRE.OrganizationNode,
       HRE.OrganizationLevel,
       HRE.JobTitle,
       HRE.BirthDate,
       HRE.MaritalStatus,
       HRE.Gender,
       HRE.HireDate,
       HRE.SalariedFlag,
       HRE.VacationHours,
       HRE.SickLeaveHours,
       HRE.CurrentFlag,
       HRE.rowguid,
       HRE.ModifiedDate,
       Dept.DepartmentID,
       Dept.ShiftID,
       Dept.StartDate,
       Dept.EndDate,
       Dept.ModifiedDate,
       HumanResources.Department.Name,
       HumanResources.Department.GroupName,
       HumanResources.Department.ModifiedDate,
       HumanResources.Shift.Name,
       HumanResources.Shift.ShiftID,
       HumanResources.Shift.StartTime,
       HumanResources.Shift.EndTime,
       HumanResources.Shift.ModifiedDate
FROM HumanResources.Employee AS HRE 
INNER JOIN HumanResources.EmployeeDepartmentHistory AS Dept ON HRE.BusinessEntityID = Dept.BusinessEntityID
INNER JOIN HumanResources.Department ON Dept.DepartmentID = HumanResources.Department.DepartmentID 
INNER JOIN HumanResources.Shift ON Dept.ShiftID = HumanResources.Shift.ShiftID
GO

DBCC FREEPROCCACHE WITH NO_INFOMSGS
GO
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
GO

SELECT 'Aliased Query',
       EmployeeID = HRE.BusinessEntityID,
       LoginID = HRE.LoginID,
       NationalIDNumber = HRE.NationalIDNumber,
       OrganizationNode = HRE.OrganizationNode,
       OrganizationLevel = HRE.OrganizationLevel,
       JobTitle = HRE.JobTitle,
       BirthDate = HRE.BirthDate,
       MaritalStatus = HRE.MaritalStatus,
       Gender = HRE.Gender,
       HireDate = HRE.HireDate,
       SalariedFlag = HRE.SalariedFlag,
       VacationHours = HRE.VacationHours,
       SickLeaveHours = HRE.SickLeaveHours,
       CurrentFlag = HRE.CurrentFlag,
       rowguid = HRE.rowguid,
       EmployeeModifiedDate = HRE.ModifiedDate,
       DepartmentID = Dept.DepartmentID,
       ShiftID = Dept.ShiftID,
       StartDate = Dept.StartDate,
       EndDate = Dept.EndDate,
       DepartmentModifiedDate = Dept.ModifiedDate,
       DepartmentName = HumanResources.Department.Name,
       GroupName = HumanResources.Department.GroupName,
       DepartmentModifiedDate = HumanResources.Department.ModifiedDate,
       ShiftName = HumanResources.Shift.Name,
       ShiftID = HumanResources.Shift.ShiftID,
       StartTime = HumanResources.Shift.StartTime,
       EndTime = HumanResources.Shift.EndTime,
       ShiftModifiedDate = HumanResources.Shift.ModifiedDate
FROM HumanResources.Employee AS HRE 
INNER JOIN HumanResources.EmployeeDepartmentHistory AS Dept ON HRE.BusinessEntityID = Dept.BusinessEntityID
INNER JOIN HumanResources.Department ON Dept.DepartmentID = HumanResources.Department.DepartmentID 
INNER JOIN HumanResources.Shift ON Dept.ShiftID = HumanResources.Shift.ShiftID
GO

SET STATISTICS IO OFF
SET STATISTICS PROFILE OFF

Running the query gives us the following results:

  • Query output
  • After each query’s output, we receive a result set containing the performance statistics for the given query
  • In the “Messages” tab, the IO statistics for the query execution are available

Let us evaluate each one-by-one:

Statistics I/O

We started with clean buffers for both queries to avoid any impact to the I/O statistics arising out of data already being available in the buffer (i.e. a “logical” read). The statistics I/O for both queries yields the same output, which is:

Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘Employee’. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘EmployeeDepartmentHistory’. Scan count 1, logical reads 4, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘Shift’. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘Department’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Here’s the screenshot:

Output of STATISTICS IO is the same for aliased & non-alised queries

Statistics Profile

The statistics Profile is nothing but a textual representation of the query profile information that can be obtained from the execution plan also. Besides many others, the output contains the following values of interest:

  1. The number of rows processed by each operation
  2. The number of times a particular operation was executed
  3. Operator used
  4. Estimated Rows
  5. Estimated CPU
  6. Average Row Size
  7. Total Subtree cost

To compare the result sets, I simply used Excel. You can use any file comparison tool of your choice.

Comparison of the Profiler statistics for aliased v/s non-alised queries shows that apart from a slight increase in average row size, the values are the same.

Comparing the output reveals that the two result sets are identical except for:

  1. The obvious difference in the query text
  2. A slight increase in the Average Row Size is observed for the aliased query (which can be attributed to the increased number of characters used for aliasing)
  3. The Estimated CPU time and the Total Subtree Cost remains the same

Summarizing

Combining the results of the experiments above and the observations from my previous post, we can summarize that:

  Affected By Aliasing?
Query Performance – IO No
Query Performance – CPU No
Query Performance – Avg. Row Size Possible
Query Performance – Time No
Plan Cache Size No
Plan reuse Possible

What have been your observations related to aliasing? Do leave a note as you go.

Until we meet next time,

Be courteous. Drive responsibly.

Advertisements

5 thoughts on “#0169-SQL Server-Table/Column Aliasing does not affect query performance-Part 2

  1. ErikEckhardt

    Wow, that was a lot of work! I am impressed. If you’ll allow me, the only remaining thing would be to parse the two queries thousands of times (without running them, and with clearing the plan cache in between each one) and show total CPU consumption. This kind of test would truly definitively expose any possible difference in the cost of execution plan selection for aliases vs. no aliases.

    However, I have to say that any experienced SQL Server professional won’t need to see these statistics. An understanding of the phases of query execution plus actual query experience is certainly all that’s needed to prove that aliases require only a minuscule amount of resources. In all the the detailed work the engine has to do to parse and validate a query and choose an execution plan–something that happens quite quickly already–aliasing is one of the tiniest parts, accomplished without even any I/O on schema metadata!

    Like

    Reply
  2. Nakul Vachhrajani

    Erik: Thank-you!

    I agree, that for most who have been working with SQL Server as a profession, parsing queries thousands of times is not required because aliasing is just a tiny little part of the whole processing. In my humble opinion, aliases are nothing to be afraid of – if they make a query more readable, they should be used by all means.

    Like

    Reply
  3. ErikEckhardt

    I would dearly love to see some scholarly research combining information on the formatting of code, the semiotics relating to it, concomitant comprehension and reading speed & clarity, and the brain architecture that explains why it is so important.

    Like

    Reply
  4. Nakul Vachhrajani

    Thank-you for your kind words, Madhivanan!

    @Erik: I agree. Often when I tell newer team members to format the code they write, use spaces instead of tabs, etc they look at me as if I spoke Martian. These things are elementary and fundamental.

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s