Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

The Less SQL Server Sorts, the Faster It Responds

Learn coding techniques to tweak your SQL queries so that SQL Server sorts less data and returns much faster.


advertisement
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.



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap