This series of articles will discuss SQL server performance. In the title, I use SQL Performance because the articles could include SQL statement (Query) optimization and also include SQL Server performance issues, and probably include stored procedure performance issues.
- SQL Performance (0), Summary
- SQL Performance (1), Optimized SELECT Query (A)
- SQL Performance (2), Optimized SELECT Query (B) --- this article
- SQL Performance (3), Optimized Data Modifying Query
- SQL Performance (4), Tune the SQL Server Database
A: Introduction
According to Pareto principle’s 80/20 rule, we put 20% effort in article 1, SQL Performance (1), Optimized SELECT Query (A), to discuss the minimum actions we need to do for SQL performance tuning SELECT statements, and hopefully to get 80% performance improvement; while in this article, we will discuss the 80% effort to get another 20% performance improvement.
We will discuss with 12 tips below.
B: Tips for Improvement of Tuning Query
- Create joins with INNER JOIN (not WHERE --- Cross Join)
- Avoid Multiple Joins in a Single Query
- Avoid using table variables in joins
- Using Temp Table wisely
- Using a Subquery
- Using a CTE
- Avoid Correlated SQL Subqueries
- Avoid Multi-Statement Table-Valued Functions (TVFs), instead using inline TVFs
- Avoid using wildcard characters at the beginning of the LIKE pattern
- Avoid using COUNT(), instead of Exist()
- Avoid Using GROUP BY, ORDER BY, and DISTINCT
- Avoid Different Datatype on JOIN and WHERE Conditions
- Avoid using HAVING to define a filter, instead of WHERE
- Avoid OR conditions
- Avoid sorting with a mixed order
1. Create joins with INNER JOIN (not WHERE --- Cross Join)[ref]
Some SQL developers, such as me, starting from Oracle, used to make joins with WHERE clauses, such as the following,
SELECT Customers.CustomerID, Customers.Name, Sales.LastSaleDate
FROM Customers, Sales
WHERE Customers.CustomerID = Sales.CustomerID
This type of join creates a Cartesian Join, also called a Cartesian Product or CROSS JOIN.
In a Cartesian Join, all possible combinations of the variables are created. In this example, if we had 1,000 customers with 1,000 total sales, the query would first generate 1,000,000 results, then filter for the 1,000 records where CustomerID is correctly joined. This is an inefficient use of database resources, as the database has done 100x more work than required. Cartesian Joins are especially problematic in large-scale databases because a Cartesian Join of two large tables could create billions or trillions of results.
To prevent creating a Cartesian Join, use INNER JOIN instead,
SELECT Customers.CustomerID, Customers.Name, Sales.LastSaleDate
FROM Customers INNER JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
The database would only generate the 1,000 desired records where CustomerID is equal.
Note
Some DBMS systems are able to recognize WHERE joins and automatically run them as INNER JOINs instead. In those DBMS systems, there will be no difference in performance between a WHERE join and INNER JOIN. However, INNER JOIN is recognized by all DBMS systems.
I check SQL Server 2019 that belongs to this category.
2. Avoid Multiple Joins in a Single Query[ref]
Making joins less complicated.
Try to avoid writing a query using multiple joins that include outer joins, cross apply, outer apply, and other complex subqueries.
Reason
When the joins are complex, the number of possible execution plans exponentially increases. This, in turn, will reduce the choices for the query optimizer to decide the join order and join type. Sometimes, the query optimizer is forced to use nested loop joins, irrespective of the performance consequences for queries with excessively complex cross apply or subqueries. Also, due to time and performance constraint, SQL Engine will try to generate the “good enough” execution plan versus trying to generate the best possible execution plan.
What would address your question is the subject JOIN DECOMPOSITION.
You can decompose a join by running multiple single-table queries instead of a multi-table join, and then performing the join in the application. For example, instead of this single query,
SELECT * FROM tag
JOIN tag_post ON tag_post.tag_id = tag.id
JOIN post ON tag_post.post_id = post.id
WHERE tag.tag = 'mysql';
You might run these queries,
SELECT * FROM tag WHERE tag = 'mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904);
Why do this? It looks wasteful at first glance because you've increased the number of queries without getting anything in return. However, such restructuring can actually give significant performance advantages,
- Caching can be more efficient. Many applications cache "objects" that map directly to tables. In this example, if the object with the tag
mysqlis already cached, the application will skip the first query. If you find posts with an ID of 123, 567, or 908 in the cache, you can remove them from theIN()list. The query cache might also benefit from this strategy. If only one of the tables changes frequently, decomposing a join can reduce the number of cache invalidations. - Executing the queries individually can sometimes reduce lock contention
- The queries themselves can be more efficient. In this example, using a
IN()list instead of a join lets MySQL sort row IDs and retrieve rows more optimally than might be possible with a join. - You can reduce redundant row accesses. Doing a join in the application means retrieving each row only once., whereas a join in the query is essentially denormalization that might repeatedly access the same data. For the same reason, such restructuring might also reduce the total network traffic and memory usage.
- To some extent, you can view this technique as manually implementing a hash join instead of the nested loops algorithm MySQL uses to execute a join. A hash join might be more efficient.
As a result, doings joins in the application can be more efficient when you cache and reuse a lot of data from earlier queries, you distribute data across multiple servers, you replace joins with
IN() lists or a join refers to the same table multiple times.Note
Another example can be seen here.
3. Avoid using table variables in joins[ref]
Use temporary tables, CTEs (Common Table Expressions), or derived tables in joins instead.
Reason
Even though table variables are very fast and efficient in a lot of situations, the SQL Server engine sees it as a single row. Due to this reason, SQL will produce an execution plan that will perform horribly when used in joins.
a. Using Temp Table wisely[ref]
Temporary tables usually increase a query’s complexity. If your code can be written in a simple, straightforward manner when you have to join a large table and there are conditions on said table, you can increase database performance by transferring your data in a temp table, and then making a join on that. Your temp table will have fewer rows than the original (large) table, so the join will finish faster!
The decision isn’t always straightforward, but this example will give you a sense of situations in which you might want to use temp tables,
Imagine a customer table with millions of records. You have to make a join on a specific region. You can achieve this by using a
SELECT INTO statement and then joining with the temp table,
SELECT * INTO #Temp FROM Customer WHERE RegionID = 5
SELECT r.RegionName, t.Name FROM Region r JOIN #Temp t ON t.RegionID = r.RegionID
(Note: some SQL developers also avoid using SELECT INTO to create temp tables, saying that this command locks the tempdb database, disallowing other users from creating temp tables. Fortunately, this is fixed in 7.0 and later.)
b. Using a Subquery[ref]
As an alternative to temp tables, you might consider using a subquery as a table,
SELECT r.RegionName, t.Name FROM Region r
JOIN (SELECT Name, RegionID FROM Customer WHERE RegionID = 5) AS t
ON t.RegionID = r.RegionID
All of these SQL snippets will return the same data. But with temp tables, we could, for example, create an index in the temp table to improve performance. There’s some good discussion here on the differences between temporary tables and subqueries.
c. Using a CTE[ref]
Another option, you might consider using a CTE as a table,
WITH Employee_CTE (EmployeeNumber, Title)
AS
(SELECT NationalIDNumber,
JobTitle
FROM HumanResources.Employee)
SELECT EmployeeNumber,
Title
FROM Employee_CTE
Join the conversation! Your thoughts help the community grow.