The Path to High Performing SQL Queries
Some tips to speed up your queries
This post is about how to optimize SQL queries with regard to yielding high performance gains. In doing so, three fundamental rules will be depicted which should generally be considered when it comes to sophisticated queries where a huge amount of data is involved. In addition to this, I will also present some methods and principles to optimize a query’s performance.
First of all it has to be said that performance in SQL queries is depending on many different aspects such as the underlying database management system (DBMS), the database’s architecture or the IT infrastructre you are working in.
Firt of all I would like to point out that the rules and optimizations measures presented are either based on experience in my professional environment or backed up by indicated literature. Furthermore, it has to be said that other DBMS might work with other data handling principles and methods. Hence, the guidelines below may do not have any influence or even lead to less performing queries. The system environment, as well as the hardware, must also be regarded as influencing factors which may temper the effect of the advice below.
Personally, I made great experiences applying these rules and optimization techniques which is why I would like to share it.
Rules
Shortly after some first tries with SQL one will realize that there exist many different ways to reach the desirable result. However, one should still be aware of some basic rules when high performance is required. The rules explained in this section cannot be regarded as a silver bullet: their effect should be verified with representative data and execution plans.
Filtering Selective Data Early
Imagine a database containing two tables, one called tbl_People including names, gender and addresses of all people from the world and the other one, which is related [1:n] called tbl_Disease including all possible diseases existing. The aim now would be to show all men who live in the United States and are diagnosed with lung cancer. In relational algebra, applying selective filters before a join can reduce the intermediate result, as statement \eqref{eq:f1} illustrates. In SQL, however, a cost-based query optimizer will often push predicates down and choose the physical join order independently of the written order.
Statement \eqref{eq:f2} joins the entities before applying the filters. For an inner join, it is logically equivalent to \eqref{eq:f1}, and an optimizer may generate the same execution plan for both:
Thrifty Column Presentation
It is all too easy to code a query with SELECT *, which projects all columns available, regardless of whether they are needed. Imagine a table with 150 columns even though only three columns are required. Retrieving only the necessary columns can reduce I/O, memory use, and network transfer, and may allow the optimizer to use a covering index. The performance difference depends on the query and DBMS, but selecting only required columns is a good default
Parameterize Ad Hoc Queries
Ad hoc is Latin and means for this purpose. An ad hoc SQL statement can still receive an execution plan, and systems such as SQL Server may cache and reuse that plan. Nevertheless, saved or parameterized queries often improve plan reuse, avoid repeated parsing and compilation, and reduce SQL-injection risks compared with constructing SQL from concatenated input
Optimization
Besides rules about how to construct a query the most efficient way, performance is often also a question of the database’s architecture. Since renovation a DB is a arduous undertaking, I would like to introduce four techniques to increase query performance that do not require dangerous architectural database manipulations.
Temporary Tables
The desirable result of data selection often cannot be made within one single query. For complex intermediate results that are reused or benefit from their own indexes and statistics, a temporary table can yield impressive gains. It also introduces writing, indexing, and cleanup overhead, so it should be compared with a single query using an execution plan and representative data
Instead of processing an intricate SQL statement which may involve many different tables with a big relation chain over and over again, the results can initially be written in a temporary table which then will be shown to the user. After the user concludes all modifications, the data can be compared and eventually written back in the appropriate tables. Besides a possible performance improvement, this approach provides the advantage of being able to draw on a temporary data state when it comes to debugging.
Another possible use case occurs when a filtered subset of a large table is reused in several operations. Materializing that subset in a temporary table may improve performance, but the optimizer can often apply the filter efficiently without manual materialization
A guiding principle regarding the application of temporary tables is the characteristic that no productive data should be affected while operating with this method. Consequently, the content of these type of tables should always be considered as erasable at any time due to its intermediate purpose.
Indexes
Imagine a movie collector is looking for a specific film in his personal collection. Unfortunately, he did not sort the items regarding any order which forces him to look through all DVDs he owns. This issue would not have occurred when he initially would have sorted his collection alphabetically or, even better, numbered each item and listed it together with its storage location in a book. Although this would cost effort to create and maintain, such a register would make searching for a DVD easier and faster. This analogy explains the principle of database indexes: an index is a separate data structure that helps the database locate records efficiently
Instead of going through all rows in a table, the database may use an index to inspect a much smaller subset. Indexes can help columns used selectively in filters, joins, and sorting, including many foreign-key columns. They are not automatically beneficial: low selectivity, small tables, and write-heavy workloads may favor a scan, while every index consumes storage and adds maintenance cost.
Redundancy
Normalization is a good default when constructing a relational database. In measured cases, however, deliberate denormalization can trade additional storage and update complexity for faster reads.
It can sometimes be helpful to copy a key into a table linked to the original through a long relation chain, as illustrated in the figure below. This is denormalization and requires a reliable way to preserve consistency.
When the appropriate A-name of an E-object shall be queried, the database has to go through the entire relation chain to return the correct value. But listing the A-ID in tbl_E as a copy, provides the possibility to shorten this query procedure significantly as indicated below.
Nonetheless, this approach is risky in data hierarchies with a high update rate because every relevant change must also update the copied field. Systems such as SQL Server can automate this with triggers, but triggers add hidden work and execute within the surrounding transaction. Denormalization should therefore be justified by measurements and protected by appropriate consistency mechanisms
Subqueries
A subquery inserts one SQL statement inside another. A nested select is not inherently slower than a join or a saved query: cost-based optimizers can often transform logically equivalent forms into the same plan. Correlated subqueries can be expensive when repeatedly evaluated, but separating statements does not by itself guarantee materialization or parallel execution. The reliable approach is to compare execution plans and measurements on the target DBMS
For instance, if the aim would be to return a list with all people’s first and family names including the number of other persons who are practicing the same sport as themselves. As this evaluation requires a subquery which will depict the appropriate number of participators each sport, two approaches arise regarding solve this issue.
When applying the nested select approach, the SQL statement would look like the following:
The inner SELECT is the subquery, which is constructed in a nested way.
For comparison, consider the alternative solution below, in which the subquery gets invoked by a second query:
Conclusion
At a first glance, query performance might not seem a necessary issue to cope with but once a certain data volume is transcended, this topic will become more and more important. This article provides a small repertoire of possible optimization solutions which eventually can be adopted to its individual use case. Nonetheless, it has to be said that I only touched the surface of database science. Considering all the numerous different database management systems somehow operate differently than others, it would hardly be possible to write down a master solution for high performing SQL queries. Anyways, you now know about some basic tools to tune your SQL statements in order to decrease waiting times.