Microsoft SQL Server

Performance Optimization

Version - SQL Server 2012
Client based in – India

The Challenge

One of existing our clients whom we provide support for BI and SQL Server had a stored procedure giving poor performance and we were asked to analyse it for optimization. The limitation was to just re-structure the queries without making any changes to the underlying DB structure.

The Solution

We found that unnecessary use of temporary tables was made, where the result could be managed without them. These temporary tables accounted for a lot of memory usage. Keeping in mind the final output required, we eliminated certain temporary tables and wherever possible, combined multiple results into a single result set. Also, there was heavy and incorrect use JOINS in the SELECT statement giving the final output. We eliminated certain JOINs from the this query and shifted them to separate functions as per requirements. After optimization, the query that previously took 1.75 hrs to run for a certain set of inputs finished within 6 seconds.