Case study – 2307x faster!

Description:

Problem: Stored procedure takes 38 seconds to execute. It times out on the application front-end. It was performing multiple scans and reading too much data.
Solution:  The Stored procedure re-written. Also, added a second select with a UNION ALL, this was necessary to divide the WHERE clause selectivity.

Other notes: Result set for both versions of the stored procedure is the same.However, row ordering is different.
Therefore, before putting this change into production, please make sure that the application does not care how data is received (we suspect it does not matter), as the app sorts the result set anyway.

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. Say you have 3 lane highway. And 5 cars use it every 1min.

What if you add 50 cars? The speed is still 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.

Same with SQL Servers. That’s why speed tuning most critical resources are essential.

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

And the more future cars we can put on it.

Check out more case studies here!

 

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Comment

Your email address will not be published. Required fields are marked *

4.8/5

42

TESTIMONIALS

FREE Scripts to Instantly
Improve SQL Server Performance

3 FREE Scripts to Instantly
Improve SQL Server Performance

Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.