The Less SQL Server Sorts, the Faster It Responds

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.

Next Page