It’s one thing to claim competence in a particular technology and quite another to demonstrate competence. Thus we at Boomcycle like to relate an “astonishing true tale” of Microsoft SQL query optimization in the real world: a client who had a problem with Microsoft SQL Server 2008 that the Boomcycle team successfully resolved.
The purpose of this work was to resolve an issue with the slow performance of an MS SQL query. Our client had an ASP.NET web site using an MS SQL database as the data source. One of website pages displays a list of orders in a grid view. This is an extremely common use of MSSQL in ASP.NET. In the client’s web application, a fixed number of orders can be displayed at the same time on the order page. In order to see the next set of records, the user is required to click a page number at the bottom of the page.
Unfortunately the query that extracts a set of records from the database required more than one minute to run. This exquisitely torturous wait led to near total abandonment of this tool by the client’s staff.
The problematic query was a fairly complex query with several sub-queries and multiple table joins. To resolve the issue our Boomcycle MSSQL engineers decided to split the query into several parts, analyze the execution plan for each part with built-in MS SQL Server Management Studio tool, and identify the part(s) of the query that was causing the delay and the reason for this delay.
While using the Server Management Studio Tool, our team performed a series of queries to the problematic query composite tables using different join criteria. The execution plans for these queries were analyzed to see what indexes are engaged.
Happy accidents sometimes happen, even in complex MSSQL query optimization: after a bit of study, Boomcycle engineers discovered that the original problematic query began to run much faster: 6-8 seconds instead of more than one minute. It turned out that running each sub-query in the graphical query analyzer tool created statistics which allowed the SQL Server to build a cost-efficient query execution plan for the original query.
Nevertheless after getting this result our client was anxious to make the query run even faster, and thus our team continued its query optimization investigation. Ultimately we discovered that the main performance problem with the old query was that it was written in a way which did not allow the MSSQL Server to take advantage of parallel query execution. The parallel query execution feature can significantly increase performance on multiprocessors systems. Specifically, the problematic query used the SQL UNION operator which removed parallel execution in case of simple join of two composite queries. Boomcycle MSSQL experts re-wrote the query to ensure that multiprocessor parallel execution would be engaged when it ran.
In addition to the parallel query execution fix, the problematic query was cleansed of useless sub-queries. The original query used an external view that performed superfluous data operations. The view was replaced with the tables that were really required.
As result of Boomcycle’s optimization efforts, query execution time was decreased to 3-4 seconds to output all rows and decreased to 1-2 seconds to search a record with specific criteria.
Boomcycle’s client was very happy, and his staff began to use the once-abandoned system again!
Even after all that was done there were additional avenues to explore for further optimization by adding new indexes for the most often used query criteria. However our client was content with the execution speed and decided his problem had been solved.
Do you have any MSSQL problems that Boomcycle’s team can solve?