Table of Contents
Optimising SQL server 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:
- Subqueries 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.
Optimising SQL server performance now and for the future
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.
Need Help with Optimising SQL Server Queries?
If you have long running queries and would like us to assist with optimising SQL server performance, why don’t you get in touch?
Contact us or take a look at our SQL Server Health Check.
Developed over a number of years, our most experienced SQL Server consultants have created a thorough and concise structure for reviewing, health checking and optimising SQL Server databases.
Maintaining the databases behind your organisation’s operations can be complicated and costly, especially when infrastructure and applications have grown over time.
View our Database Consolidation services.
MySQL Managed Services
WellData offers MySQL management and optimising SQL Server services for constant proactive MySQL database support.
This service covers all your database’s business-as-usual (BAU) requirements on an ongoing basis.
If you need help optimising SQL server performance or queries this could be the right solution for you and your team.
WellData’s database and server managed services takes care of databases, servers and applications for organisations of all shapes and sizes.
We take a proactive approach to the support of your systems, ensuring they are fully maintained in the best possible condition.
Systems can be rolled on and off the contract at any time during the term of the contract, flexing as your business needs change.
Our monitoring goes far beyond simple availability checks and into areas of general performance like: memory use, locking, growth, throughput, equipment failures, and more. The list is comprehensive.
Get in touch today to discuss your database and server management needs
