Browse DevX
Sign up for e-mail newsletters from DevX


The Less SQL Server Sorts, the Faster It Responds : Page 2

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




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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>=@name) or (@i<>1 and first_name>=@name) 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>=@name order by last_name else select top 20 * from customers where first_name>=@name 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 client string 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.

Alexander Kuznetsov has over 10 years of experience in database design, development, troubleshooting, and administration. Currently, he works with DRW Trading Group and concentrates on database design, development, and performance improvements.
Comment and Contribute






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



Thanks for your registration, follow us on our social networks to keep up-to-date