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 performsand 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)
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
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
keyword, SQL Server will sort the result set of this query:
select distinct SSN, last_name, first_name, street_address, city, zip
Is the sort really necessary though? If the Social Security Numbers are entered correctly for all the employees, you will not get any duplicates anywayeven 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.