Menu
A long time ago I was advised to sort data at the application layer and not use the ORDER BY clause in SQL. The reasoning was the .Net will sort more efficiently the SQL engine.
Conflicting with this advice is the SSIS Best Practices I've encountered that recommends sorting data in the SQL, where one can, and avoiding the Sort transformation.
The SSIS advice makes sense to me. So now I am wondering if the initial advice of avoiding the ORDER BY is bogus.
Given a not-too-complex query, does the ORDER BY necessarily mean a performance hit?
Thanks.
552
Ascending is the default sort order in an ORDER BY clause. As you can see, the ORDER BY clause is placed after the FROM statement. After the ORDER BY keyword, we have the column or columns that the rows will be sorted by.
The ORDER BY clause is used to sort rows in a result table. Here is its syntax: The example below shows records in the furniture_data table that have been sorted in ascending order according to the furniture column. Ascending is the default sort order in an ORDER BY clause. As you can see, the ORDER BY clause is placed after the FROM statement.
Besides sorting rows using one or more columns, you can also use ORDER BY with simple and searched CASE expressions. ( To learn more about CASE, read this article.) Why use CASE in an ORDER BY clause?
This example sorts rows in descending order according to the points column. If two or more records have the same value, the database uses the furniture column to sort these records in ascending order. The bookcase and chair rows have the same number of points (5), so these rows are also sorted by the values in the furniture column.
Brent Ozar's argument for avoiding ORDER BY boils down to SQL Server licenses being expensive and application server licenses being cheap. The "application server" in the case of SSIS is, in fact, SQL Server, so the "cheaper server" argument doesn't apply.
I've never seen the argument that .NET sorting was inherently faster than SQL Server sorting, but I'd be extremely surprised if that was generally true (especially given the amount of meta-information about the underlying data that is available to the SQL Server query optimizer but is not available to a generic .NET Sort() method). I do know that the SSIS Sort transformation can put a big performance hit on the data flow, since all the data has to be cached by SSIS before the sorting can begin.
So, in the specific case of choosing between using a T-SQL ORDER BY clause to sort data or an SSIS Sort transformation, I'd always choose the ORDER BY clause to start with.
198
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
