ometimes we developers put unnecessary burden on the server, having it perform too many sorts or sort more data than necessary. By applying the techniques described in the following sections to your design and coding practices, you’ll reduce the number of sorts your server performs?and provide your users with much better response times.

Technique 1: Sort Only the Columns You Really Need to

SQL Server has to sort the result set to execute this all-too-common query:

select customer_id, last_name, first_name, address, city, state, postal, sum(amount) sum_amount from customers join orders on customers.customer_id = orders.customer_id group by customer_id, last_name, first_name, address, city, state, postal 

The problem is some of the columns in the result set are quite wide. Sorting a narrow result set (only customer_id and amount) and adding all the other columns after the sort as follows may be several times faster:

select customer_id, last_name, first_name, address, city, state, postal, sum_amount from customers join ( select customer_id, sum(amount) sum_amount from orders group by customer_id) sum_orders on customers.customer_id = sum_orders.customer_id 

This is a simple and easy way to have your query return several times faster. However, you have to pay a price for the improved performance: the query becomes longer and more difficult to read. So use this approach in moderation: to speed up only the queries that run frequently or respond very slowly.

Technique 2: Add a Unique Index and Eliminate an Unnecessary Sort Altogether

Because of the DISTINCT keyword, SQL Server will sort the result set of this query:

select distinct SSN, last_name, first_name, street_address, city, zip from employees 

Is the sort really necessary though? If the Social Security Numbers are entered correctly for all the employees, you will not get any duplicates anyway?even without the DISTINCT clause. If you add a unique index or constraint on SSN, the optimizer will recognize that the sort is unnecessary. Without the sort, the query will run significantly faster.

The better performance won’t cost you much: the constraint will slightly slow down modifications against the table. However, on the plus side, you will have your data integrity enforced.

Tip: Whenever you optimize a query, always check for missing constraints.

Technique 3: Be Specific and Get Better Execution Plans Without Sorts

The following generic procedure always scans the whole table, even though there are indexes on both first_name and last_name:

create procedure generic_select(@i int, @name varchar(50)) as select top 20 * from customers where (@i=1 and last_name>[email protected]) or (@i<>1 and first_name>[email protected]) order by case when @i=1 then last_name else first_name end 

The execution plan for this stored procedure is very simple. Whatever parameters you call it with, SQL Server will scan the whole table no matter what. On top of that, SQL Server will have to sort that big result set. You might think that indexes on last_name and first_name would be useful, but guess again. The optimizer will not use them. However, speeding up this poor performer is very easy. Just replace the generic select query with two more-specific ones as follows, and you will get two good execution plans:

create procedure two_specific_selects(@i int, @name varchar(50)) as if @i=1 select top 20 * from customers where last_name>[email protected] order by last_name else select top 20 * from customers where first_name>[email protected] order by first_name

Again, the price for better performance is longer (and more difficult to maintain) source code.

Tip: This example is intended to be as straightforward as possible. As such, parameter sniffing won’t ever kick in and complicate the issue. In many real life situations, however, parameter sniffing may play a significant role. To learn more about it, read “Reusing Query Plans” by Kalen Delaney (SQL Server Magazine subscription required) and Ken Henderson’s A Technique for Ensuring Plan Stability in SQL Server 2000 blog.

Technique 4: Sort on the Client and Improve Your Server’s Throughput

Sorting a result set on the client may be very easy. For instance, the following C# code snippet sorts the result set on the server before retrieving it to the client:

SqlDataAdapter da = new SqlDataAdapter("Select Lastname, Firstname, 
	Hiredate from Employees order by Lastname ASC",MyConnection);DataSet ds = new DataSet();da.Fill(ds,"Employees");

You can easily tweak it a little bit so that the result set is sorted on the client:

SqlDataAdapter da = new SqlDataAdapter("Select Lastname, Firstname, Hiredate from Employees ",MyConnection);DataSet ds = new DataSet();da.Fill(ds,"Employees");DataTable employeesTable = ds.Tables[0];// Approach 1: Use Sort property of DataView class// Get the DefaultViewManager of a DataTable. DataView myDataView = employeesTable.DefaultView; myDataView.Sort = "LastName DESC";// Approach 2: Use Select method. The sort will still take place on the clientstring strSort; // Sort ascending by column named LastName. strSort = "LastName ASC"; // Use the Select method to sort the data in the DataTable according to the sort criteria. DataRow[] sortedRows = employeesTable.Select( "", strSort);

Consider using these techniques for frequently run queries. You may significantly reduce the workload on the server. Don’t overdo it though. While in many cases you can safely delegate sorts to the client, aggregations in most cases belong on the server. Should you move the aggregations to the client, you would have to transfer the raw data via the network, which is usually very bad for performance.

Alternatives for Eliminating Sorts and Speeding Up Queries

Other techniques for speeding up or eliminating sorts are clustering indexes, index covering, and indexed views. These are beyond the scope of this article, so refer to Professional SQL Server 2000 Programming by Robert Vieira and The Guru’s Guide to Transact-SQL by Ken Henderson for more information.