In our previous article we saw the great advantages of applying Best Programming Practices in SQL Server. We’d like to explore this further, this time focusing on query optimization.

SQL is a declarative language: each query declares what we want the database engine to do, but it doesn’t say how. However, it turns out that the how (the plan) is what affects the efficiency of the queries, so it is also quite important.

Query optimization

Query optimization is a huge topic that could easily become overwhelming. The best way to address a performance issue is to look for focus areas searching for specific things that are likely to cause high latency. In these scenarios, finding the suspicious high resource-consuming code can quickly narrow down the search and allow us to solve a problem rather than having to go through each line of code.

The key is to identify common design patterns that are indicative of low-performance Transact-SQL code. Developing ways to recognize these patterns (that is, to detect errors), can allow us to immediately focus on what is most likely to be the problem.

Query optimization sometimes requires additional resources, such as adding a new index. But when we can improve performance solely by rewriting a query, we can reduce resource consumption at no cost (other than our time). Thus, query optimization can be a direct source of cost savings, particularly if we can adopt Best Practices to create queries in the most efficient way possible from the beginning.

A common mistake when it comes to testing queries and measuring performance improvement is running them in a Development environment when the system isn’t running different queries from other users and where there are usually very few records. Here everything runs quickly because there is no load on the Database Development Server. But then there is often not enough time set aside afterward for testing in other environments. Quality and Testing environments end up being, at best, mere transition protocols on the path from development to Production. In the end, if something goes wrong while using the new code, the phrase you typically hear is: “I don’t know how it can be going so slow, it used to be fast and worked fine before”.

When one such query is promoted to Production and is run in a busy environment, the query may malfunction and undermine application performance. If in the previous article we saw the importance of mastering our Database model as the main skill, here we have the Second (also mandatory) Best Practice: You must always monitor and test performance, always, even if it looks like a query won’t need much in the way of Database Server resources.

Therefore, before integrating our queries in the application code, it’s a clever idea to use developer tools such as Integrated Development Environments (IDEs) for relational databases. Microsoft SQL Server Management Studio, for example, works well for Windows users, or Azure Data Studio for Windows, MacOS and Linux.

At Teldat, we continue to improve each day by applying Best Practices to our cloud-based SQL Server Database developments.