#0302 – SQL Server – Myths – ORDER BY does not impact performance


One of the most common mistakes that I see developers do is the use the ORDER BY clause in every stored procedure – irrespective of whether the application can sort the data independently or not. There have been many posts written on this topic, and I wanted to do my bit to spread awareness by busting this myth.

The truth is that the ORDER BY clause consumes precious server resources. As we know, the ORDER BY clause is applied after the select. What I mean by this is that once the result set of a query is generated, SQL Server will then perform the sort. In order to do the sort operation, SQL Server will need to use some temporary disk space from the tempdb and also consume processor time. Just imagine the amount of resources being consume to service hundreds of concurrent connections – each requiring a sorted output.

Here’s a simple example, executed by keeping the “Actual Execution plan” ON:

USE AdventureWorks2012;
GO
--Query without ORDER BY
SELECT hredh.BusinessEntityID,
       hre.JobTitle,
       hredh.DepartmentID,
       hrd.Name,
       hredh.StartDate,
       hredh.EndDate,
       hredh.ShiftID,
       hrs.Name
FROM HumanResources.Shift AS hrs 
INNER JOIN HumanResources.EmployeeDepartmentHistory AS hredh ON hredh.ShiftID = hrs.ShiftID
INNER JOIN HumanResources.Employee AS hre ON hredh.BusinessEntityID = hre.BusinessEntityID
INNER JOIN HumanResources.Department AS hrd ON hredh.DepartmentID = hrd.DepartmentID;
GO

USE AdventureWorks2012;
GO
--Query with ORDER BY
SELECT hredh.BusinessEntityID,
       hre.JobTitle,
       hredh.DepartmentID,
       hrd.Name,
       hredh.StartDate,
       hredh.EndDate,
       hredh.ShiftID,
       hrs.Name
FROM HumanResources.Shift AS hrs 
INNER JOIN HumanResources.EmployeeDepartmentHistory AS hredh ON hredh.ShiftID = hrs.ShiftID
INNER JOIN HumanResources.Employee AS hre ON hredh.BusinessEntityID = hre.BusinessEntityID
INNER JOIN HumanResources.Department AS hrd ON hredh.DepartmentID = hrd.DepartmentID
ORDER BY hredh.DepartmentID;
GO

image

As can be seen from the execution plan above, the query with the ORDER BY consumes more processing time (it increased from 46% to 54% of the batch) due to the introduction of the SORT operator.

Conclusion

Most applications only need pre-sorted output in about 20% of the cases. In all other cases, the sort operations should be avoided – SQL Server works best when processing the entire set of records, so why sort them?

Until we meet next time,

Be courteous. Drive responsibly.

Advertisements

One thought on “#0302 – SQL Server – Myths – ORDER BY does not impact performance

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