Optimising SQL code can be a complicated and frustrating process. Recently, we were working with a client that had a critical SQL script which they needed assistance with. The script needed to collate data from 4 different data providers, and format it for insertion into a database table. They had a prototype of the script which produced the correct results but took over 30 minutes to complete.
We were asked to investigate and speed the script up. At 6000 lines of SQL, returning around 130 columns, understanding the code was a job in itself, but upon initial inspection there were a few areas that could be improved:
- Sub queries were being used throughout, in a script of this size, this would create a heavy load on the SQL server.
- Many of the subqueries were repeated throughout the code, making for a wasteful use of the database resources.
- Having such a big query makes future maintenance confusing and frustrating.
The original intention was for the query to be created as a view. There can be advantages in using views, such as the ease of use for the end of user – views can be queryable just as if they were themselves a database table – but in this case it would have limited the potential for optimising the code. Replacing sub queries in this case with both temporary tables and variables would allow us cut down the execution time drastically. But using views in SQL prohibits the use of both, so we changed the view to a stored procedure.
This, combined with other changes, cut the execution time of the code to around 20 seconds.
We discussed with the customer their future requirements for the query. We agreed with such a large query, it would be desirable to make it easily maintainable for them in future, therefore it made sense to split out the code for each data provider into their own script. This was done with the idea that if they needed to add a new provider in future, adding a separate script would be more straightforward rather than trying to edit the existing one.
If you have long running queries and would like us to assist with optimising them, why don’t you get in touch? Contact us
<< Back to resources
