Rethinking Sorting In SQL Server Stored Procedures

We know that sorting can be one of the most expensive things in an execution plan as shown below. However, we continue to do ORDER BYs repeatedly. Yes, I 100% agree that there is a need to sort a results set and that this should be done in the procedure for good reason, but my concern is having multiple sorts, erroneous sorts, and the sorts that can be done elsewhere. These are the ones that waste resources and can stifle performance.
 
Many of us writing procedures tend to write in code blocks. We write the SELECT, JOINS, FROMs and WHERES then immediately follow it up with and ORDER BY as a way to check result sets before moving onto the next block of code. I admit I do this almost every time. But what most developers do not do is remove unneeded ORDER BYs that are not required. This is very costly and can lead to suboptimal performance not only of your procedure but also for TEMPDB as this is where all sorting takes place.
Do you sort in your procedures that are used for data consumers like reports, ETL or an application? If you do, I ask, why are you sorting in the procedure and not in the consumer ? Many report end users will resort the data in Excel, or the report itself gives parameters for custom sorts or the data doesn’t need a sort at all. Why are you wasting resources on the SQL Server side just for it to be nullified? By removing unneeded sorts or performing the sort in the application tier you can have big performance gains. I would rather have a report, ETL process or application take the performance hit then a procedure.
 
Let’s look at one of the procedures that are available in AdventureWorks2016CPT3 called uspGetOrderTrackingBySalesOrderID. We will run it using the example execution in the code and then remove the ORDER BY, compile and rerun. We will be able to see clearly see the difference.
  1. USE [AdventureWorks2016CTP3]  
  2.   
  3. GO  
  4.   
  5. /****** Object:  StoredProcedure [dbo].[uspGetOrderTrackingBySalesOrderID]    Script Date: 1/28/2020 11:31:16 AM ******/  
  6.   
  7. SET ANSI_NULLS ON  
  8.   
  9. GO  
  10. SET QUOTED_IDENTIFIER ON  
  11. GO  
  12.   
  13. ALTER PROCEDURE [dbo].[uspGetOrderTrackingBySalesOrderID]  
  14.   
  15.    @SalesOrderID [intNULL  
  16. AS  
  17. BEGIN  
  18. /* Example:  
  19.   
  20.       exec dbo.uspGetOrderTrackingBySalesOrderID 53498  
  21. */  
  22.    SET NOCOUNT ON;  
  23.    SET STATISTICS IO, TIME ON  
  24.   
  25.    SELECT  
  26.       ot.SalesOrderID,  
  27.       ot.CarrierTrackingNumber,  
  28.       ot.OrderTrackingID,  
  29.       ot.TrackingEventID,  
  30.       te.EventName,  
  31.       ot.EventDetails,  
  32.       ot.EventDateTime  
  33.    FROM  
  34.       Sales.OrderTracking ot,  
  35.       Sales.TrackingEvent te  
  36.    WHERE  
  37.       ot.SalesOrderID = @SalesOrderID AND  
  38.       ot.TrackingEventID = te.TrackingEventID  
  39.    --ORDER BY  
  40.    --   ot.SalesOrderID,  
  41.    --   ot.TrackingEventID;  
  42. END;  

Plan with ORDER BY

 
Sorting In Stored Procedures
 
Plan without ORDER BY and Query Store graph showing the difference in duration between the two. You can clearly see the performance improvement, and this was just one sort in a very simple procedure.
 
Sorting In Stored Procedures
 
Take a moment and consider the sorting that happens in your code. I’d ask that when writing store procedures, doing code reviews or performance tuning that you take a second to ask why the sorts are being done in the data tier and if they can be performed elsewhere. You can see get some performance gains not only in your code but in TEMPDB as well when sorting is reigned in.


Similar Articles
Denny Cherry and Associates
Expert Consultants From HA to DR to up-time to SQL virtualization to scalability.