The Less SQL Server Sorts, the Faster It Responds

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.

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

devx-admin

devx-admin

Share the Post:
Poland Energy Future

Westinghouse Builds Polish Power Plant

Westinghouse Electric Company and Bechtel have come together to establish a formal partnership in order to design and construct Poland’s inaugural nuclear power plant at

EV Labor Market

EV Industry Hurting For Skilled Labor

The United Auto Workers strike has highlighted the anticipated change towards a future dominated by electric vehicles (EVs), a shift which numerous people think will

Soaring EV Quotas

Soaring EV Quotas Spark Battle Against Time

Automakers are still expected to meet stringent electric vehicle (EV) sales quotas, despite the delayed ban on new petrol and diesel cars. Starting January 2023,

Affordable Electric Revolution

Tesla Rivals Make Bold Moves

Tesla, a name synonymous with EVs, has consistently been at the forefront of the automotive industry’s electric revolution. The products that Elon Musk has developed

Poland Energy Future

Westinghouse Builds Polish Power Plant

Westinghouse Electric Company and Bechtel have come together to establish a formal partnership in order to design and construct Poland’s inaugural nuclear power plant at the Lubiatowo-Kopalino site in Pomerania.

EV Labor Market

EV Industry Hurting For Skilled Labor

The United Auto Workers strike has highlighted the anticipated change towards a future dominated by electric vehicles (EVs), a shift which numerous people think will result in job losses. However,

Soaring EV Quotas

Soaring EV Quotas Spark Battle Against Time

Automakers are still expected to meet stringent electric vehicle (EV) sales quotas, despite the delayed ban on new petrol and diesel cars. Starting January 2023, more than one-fifth of automobiles

Affordable Electric Revolution

Tesla Rivals Make Bold Moves

Tesla, a name synonymous with EVs, has consistently been at the forefront of the automotive industry’s electric revolution. The products that Elon Musk has developed are at the forefront because

Sunsets' Technique

Inside the Climate Battle: Make Sunsets’ Technique

On February 12, 2023, Luke Iseman and Andrew Song from the solar geoengineering firm Make Sunsets showcased their technique for injecting sulfur dioxide (SO₂) into the stratosphere as a means

AI Adherence Prediction

AI Algorithm Predicts Treatment Adherence

Swoop, a prominent consumer health data company, has unveiled a cutting-edge algorithm capable of predicting adherence to treatment in people with Multiple Sclerosis (MS) and other health conditions. Utilizing artificial

Personalized UX

Here’s Why You Need to Use JavaScript and Cookies

In today’s increasingly digital world, websites often rely on JavaScript and cookies to provide users with a more seamless and personalized browsing experience. These key components allow websites to display

Geoengineering Methods

Scientists Dimming the Sun: It’s a Good Thing

Scientists at the University of Bern have been exploring geoengineering methods that could potentially slow down the melting of the West Antarctic ice sheet by reducing sunlight exposure. Among these

why startups succeed

The Top Reasons Why Startups Succeed

Everyone hears the stories. Apple was started in a garage. Musk slept in a rented office space while he was creating PayPal with his brother. Facebook was coded by a

Bold Evolution

Intel’s Bold Comeback

Intel, a leading figure in the semiconductor industry, has underperformed in the stock market over the past five years, with shares dropping by 4% as opposed to the 176% return

Semiconductor market

Semiconductor Slump: Rebound on the Horizon

In recent years, the semiconductor sector has faced a slump due to decreasing PC and smartphone sales, especially in 2022 and 2023. Nonetheless, as 2024 approaches, the industry seems to

Elevated Content Deals

Elevate Your Content Creation with Amazing Deals

The latest Tech Deals cater to creators of different levels and budgets, featuring a variety of computer accessories and tools designed specifically for content creation. Enhance your technological setup with

Learn Web Security

An Easy Way to Learn Web Security

The Web Security Academy has recently introduced new educational courses designed to offer a comprehensible and straightforward journey through the intricate realm of web security. These carefully designed learning courses

Military Drones Revolution

Military Drones: New Mobile Command Centers

The Air Force Special Operations Command (AFSOC) is currently working on a pioneering project that aims to transform MQ-9 Reaper drones into mobile command centers to better manage smaller unmanned

Tech Partnership

US and Vietnam: The Next Tech Leaders?

The US and Vietnam have entered into a series of multi-billion-dollar business deals, marking a significant leap forward in their cooperation in vital sectors like artificial intelligence (AI), semiconductors, and

Huge Savings

Score Massive Savings on Portable Gaming

This week in tech bargains, a well-known firm has considerably reduced the price of its portable gaming device, cutting costs by as much as 20 percent, which matches the lowest

Cloudfare Protection

Unbreakable: Cloudflare One Data Protection Suite

Recently, Cloudflare introduced its One Data Protection Suite, an extensive collection of sophisticated security tools designed to protect data in various environments, including web, private, and SaaS applications. The suite

Drone Revolution

Cool Drone Tech Unveiled at London Event

At the DSEI defense event in London, Israeli defense firms exhibited cutting-edge drone technology featuring vertical-takeoff-and-landing (VTOL) abilities while launching two innovative systems that have already been acquired by clients.

2D Semiconductor Revolution

Disrupting Electronics with 2D Semiconductors

The rapid development in electronic devices has created an increasing demand for advanced semiconductors. While silicon has traditionally been the go-to material for such applications, it suffers from certain limitations.

Cisco Growth

Cisco Cuts Jobs To Optimize Growth

Tech giant Cisco Systems Inc. recently unveiled plans to reduce its workforce in two Californian cities, with the goal of optimizing the company’s cost structure. The company has decided to

FAA Authorization

FAA Approves Drone Deliveries

In a significant development for the US drone industry, drone delivery company Zipline has gained Federal Aviation Administration (FAA) authorization, permitting them to operate drones beyond the visual line of

Mortgage Rate Challenges

Prop-Tech Firms Face Mortgage Rate Challenges

The surge in mortgage rates and a subsequent decrease in home buying have presented challenges for prop-tech firms like Divvy Homes, a rent-to-own start-up company. With a previous valuation of

Lighthouse Updates

Microsoft 365 Lighthouse: Powerful Updates

Microsoft has introduced a new update to Microsoft 365 Lighthouse, which includes support for alerts and notifications. This update is designed to give Managed Service Providers (MSPs) increased control and

Website Lock

Mysterious Website Blockage Sparks Concern

Recently, visitors of a well-known resource website encountered a message blocking their access, resulting in disappointment and frustration among its users. While the reason for this limitation remains uncertain, specialists