How We Made T-SQL Queries 38 Times Faster

Reading from disk is the slowest operation SQL Server does.

Therefore, tuning for less disk “reads” is often the primary goal.

Problem

The stored procedure took 38,000 ms to execute. It times out on the application frontend. It was performing multiple scans and reading too much data.

Solution

The stored procedure was rewritten.

Also, we added a second SELECT with a UNION ALL, this was necessary to divide the WHERE clause selectivity.

Note: Both versions of the stored procedure deliver the same info, but the order's different.

Before launching this change, just make sure the app doesn't mind the data order. It probably doesn't, since the app sorts the data itself.

Before vs. after

The improvement was huge: Duration of the procedure dropped from 38,000 ms to just 1,000 ms!

Here is the detailed breakdown.

Disk reads took a massive dive, plummeting from 687,475 down to just 314.

That’s more than a 2,189-fold decrease!

Overall, we observed a staggering improvement, making the query execution 38 times faster!

Why does disk improvement matter for stored procedure speed?

It’s simple. The less you access the disk, the more disk capacity is left over.

It works just like a highway. Imagine a 3-lane highway with just 5 cars driving on it every minute.

What if you add 50 cars? The speed remains the same because 55 cars don’t overload that highway.

What if you add another 500 or 5000? Now you are starting a slowdown in traffic.

They all still get home. But not at the same speed anymore.

It's the same deal with SQL Servers. That's why it's crucial to speed-tune the most critical resources.

The fewer hits to the storage, the more capacity available.

In this tuning session, better performance was achieved by overhauling a stored procedure and adding a second SELECT with a UNION ALL to divide the WHERE clause selectivity.

As a result - 38 times faster query!