Importance of creating an index on a table's join key

I haven't written SQL related data for a long timeArticleNow, the division of labor in the technical department is clearer than before. The website department does not write SQL query data by itself. The data is provided by other departments. This is not the case in all cases. Some projects can only be completed by themselves because they have not been managed before. In this SQL query, I realized the importance of the associated key to create an index during join.

Note:
1: free_room and freroom tables have a small data volume of less than 5000 rows.
2: room_type_num. This table has more than 10 million data records, and room_type_id does not have an index.

Query statement:

Code

From free_room F with (nolock)
Left join freoom fr with (nolock) on F. ID = Fr. free_room_id
Inner join room_type_num R with (nolock) on R. room_type_id = F. room_type_id

 

After executing the above results, we find that the system selects hash join when associating the room_type_num table. The best case is not hash join, because it does not meet the requirements of hash join. In my previous article, I briefly explained the concepts of loop join and hash join. First, I will post the following:

FirstAlgorithm: Nested loop:

Definition: nested loop join is a good choice for a small subset of connected data. In a nested loop, the internal table is driven by the External table, and each row returned by the External table must be searched to find the row matching it, therefore, the result set returned by the entire query cannot be too large (more than 10 thousand is not suitable). The table with a smaller subset returned should be used as the External table.

Algorithm 2: Hash join:

Definition: Hash join is a common method for connecting large datasets. The optimizer uses a small table (or data source) in two tables to create a hash in the memory using the connection key, scan a large table and detect the hash to find the rows matching the hash. This method applies when a small table can be fully stored in the memory, so that the total cost is the sum of the costs of accessing the two tables. However, when the table is large, it cannot be completely put into the memory. In this case, the optimizer splits it into several different partitions, the partition cannot be written into the temporary segment of the disk in the memory. A large temporary segment is required to improve I/O performance as much as possible.

DBAs recommends that you create an index in room_type_id and rewrite inner join to inner loop join room_type_num in the SQL script. The execution efficiency after modification is compared as follows:

 

 

Problem:After the test, I found that after the index is created by room_type_id, the SQL query optimizer no longer uses hash join room_type_num and automatically selects the optimal loop join. In the SQL script, the manual writing optimization prompt does not need to be pointed out. As long as the index is created in place, the optimizer will work with us to select the optimal execution plan for query. Otherwise, the optimizer can onlyProgramThe clerk walked down the backdoor.

Figure 1 shows the execution plan before the index is created:

SQL Server parse and compile time:

CPU time = 0 MS, elapsed time = 1 ms.

 

SQL Server execution times:

CPU time = 0 MS, elapsed time = 1 ms.

SQL Server parse and compile time:

CPU time = 15 MS, elapsed time = 189 Ms.

 

SQL Server execution times:

CPU time = 0 MS, elapsed time = 1 ms.

 

SQL Server execution times:

CPU time = 0 MS, elapsed time = 1 ms.

 

SQL Server execution times:

CPU time = 0 MS, elapsed time = 1 ms.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

SQL Server execution times:

CPU time = 94 MS, elapsed time = 101 Ms.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

SQL Server execution times:

CPU time = 16 MS, elapsed time = 6 ms.

 

SQL Server execution times:

CPU time = 0 MS, elapsed time = 1 ms.

 

SQL Server execution times:

CPU time = 0 MS, elapsed time = 1 ms.

 

SQL Server execution times:

CPU time = 0 MS, elapsed time = 1 ms.

 

SQL Server execution times:

CPU time = 0 MS, elapsed time = 1 ms.

 

(0 row (s) affected)

Table 'worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'free _ Room '. scan count 2, logical reads 16, physical reads 1, read-ahead reads 48, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'free _ room_use '. scan count 1, logical reads 8, physical reads 5, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

(20 row (s) affected)

 

(1 row (s) affected)

 

SQL Server execution times:

CPU time = 15 MS, elapsed time = 100 ms.

SQL Server parse and compile time:

CPU time = 0 MS, elapsed time = 1 ms.

 

SQL Server execution times:

CPU time = 0 MS, elapsed time = 1 ms.

Figure 2 shows the execution plan after the index is added:

 

SQL Server parse and compile time:

CPU time = 0 MS, elapsed time = 1 ms.

 

SQL Server execution times:

CPU time = 0 MS, elapsed time = 1 ms.

SQL Server parse and compile time:

CPU time = 16 MS, elapsed time = 45 Ms.

 

SQL Server execution times:

CPU time = 0 MS, elapsed time = 1 ms.

 

SQL Server execution times:

CPU time = 0 MS, elapsed time = 1 ms.

 

SQL Server execution times:

CPU time = 0 MS, elapsed time = 1 ms.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

SQL Server execution times:

CPU time = 109 MS, elapsed time = 102 Ms.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

SQL Server execution times:

CPU time = 0 MS, elapsed time = 1 ms.

 

SQL Server execution times:

CPU time = 0 MS, elapsed time = 1 ms.

 

SQL Server execution times:

CPU time = 0 MS, elapsed time = 1 ms.

 

SQL Server execution times:

CPU time = 0 MS, elapsed time = 1 ms.

 

SQL Server execution times:

CPU time = 0 MS, elapsed time = 1 ms.

 

(0 row (s) affected)

Table 'worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'free _ Room '. scan count 2, logical reads 16, physical reads 1, read-ahead reads 48, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'free _ room_use '. scan count 1, logical reads 8, physical reads 5, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

(20 row (s) affected)

 

(1 row (s) affected)

 

SQL Server execution times:

 CPU time = 0 MS, elapsed time = 27 Ms.

SQL Server parse and compile time:

CPU time = 0 MS, elapsed time = 1 ms.

 

SQL Server execution times:

CPU time = 0 MS, elapsed time = 1 ms.

 

Summary:Creating an appropriate index on the join key of the joined table affects the SQL optimizer's selection of the execution plan. The size and distribution of data will affect the execution plan. Therefore, for SQL optimization, you cannot simply apply the concept. The optimization is successful only when you see the results.