Performance Tuning Tip#2

Tip: Do not use Order by unless the output is not required in sorted format. If 'Order by' is required then include only required columns in 'Order by' .

Demo:

I am using Production.TransactionHistory and Production.Product Table in AdventureWorks2017 database. You can download the database from here in OLTP downloads

'Order by' clause may increase the query execution time drastically, especially when columns are not coming in a sorted format. Sometimes 'order by' is really required, but many time query contains an 'order by' which is not required or it contains too many columns and many of them are not needed in 'order by'.

Let's see how order by impact the query performance:

Let's enable statistics time for current session

SET Statistics Time ON

We will run below query and check the CPU time for both query



--Query 1
DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

SELECT TH.TransactionID
	,P.ProductID
	,P.Name
	,P.ProductNumber
	,P.Color
	,TH.TransactionType
	,TH.Quantity
	,TH.ActualCost
FROM Production.TransactionHistory TH
RIGHT JOIN Production.Product P ON TH.ProductID = P.ProductID
ORDER BY P.Name
	,P.color
	,TH.TransactionType
	,TH.Quantity
	,TH.ActualCost
GO

--Query 2
DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

SELECT TH.TransactionID
	,P.ProductID
	,P.Name
	,P.ProductNumber
	,P.Color
	,TH.TransactionType
	,TH.Quantity
	,TH.ActualCost
FROM Production.TransactionHistory TH
RIGHT JOIN Production.Product P ON TH.ProductID = P.ProductID

Both queries are almost same except first query has an 'order by' clause on few columns while the second query does not contain any 'order by' 

Now execute the the whole script at once and check the stats 

Statistics Time

SQL Server parse and compile time:
CPU time = 31 ms, elapsed time = 62 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

(113506 rows affected)

SQL Server Execution Times:
CPU time = 843 ms, elapsed time = 1909 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 6 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2 ms.

(113506 rows affected)

SQL Server Execution Times:
CPU time = 203 ms, elapsed time = 1292 ms.

 

First query has taken 843 ms on CPU while second query(without order by) has taken only 203 ms.

Now let's include an 'order by' also in second query but on only one column.




DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

SELECT TH.TransactionID
	,P.ProductID
	,P.Name
	,P.ProductNumber
	,P.Color
	,TH.TransactionType
	,TH.Quantity
	,TH.ActualCost
FROM Production.TransactionHistory TH
RIGHT JOIN Production.Product P ON TH.ProductID = P.ProductID
ORDER BY P.Name
	,P.color
	,TH.TransactionType
	,TH.Quantity
	,TH.ActualCost
GO

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

SELECT TH.TransactionID
	,P.ProductID
	,P.Name
	,P.ProductNumber
	,P.Color
	,TH.TransactionType
	,TH.Quantity
	,TH.ActualCost
FROM Production.TransactionHistory TH
RIGHT JOIN Production.Product P ON TH.ProductID = P.ProductID
ORDER BY P.Name

Let's execute the above script and check the stats

SQL Server parse and compile time:
CPU time = 15 ms, elapsed time = 209 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 4 ms.

(113506 rows affected)

SQL Server Execution Times:
CPU time = 969 ms, elapsed time = 8017 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 43 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2 ms.

(113506 rows affected)

SQL Server Execution Times:
CPU time = 701 ms, elapsed time = 1401 ms.

First query was running for 969 ms while second query was running for 701 ms after including 'order by' on one column. As you can see the CPU time was increased drastically after including 'order by' but CPU time is still less than the first query.  I am using a small data set for demo but in real time scenario on production server where data is huge, this difference may increase drastically.

So we can conclude that

  1. 'Order by' causes extra overhead on query execution and it should be avoided if not really needed
  2. If final output is required in sorted format, then we should include only required column in 'order by'.  Inclusion of unnecessary columns may lead to poor performance.

Follow me:

Blog: http://Sqldepository.com

YouTube : Sql depository

Facebook: https://www.facebook.com/sqldepository/

Google+ :https://plus.google.com/u/0/107093915534620159617

 

Author: Sauras Pandey

Sauras Pandey is an MCP, SQL Server DBA/Architect with 9+ years of experience working in a variety of environments

Leave a Reply

Your email address will not be published.