The Efficiency Analysis and Improvement Methods of left join in SQL, leftjoin

The Efficiency Analysis and Improvement Methods of left join in SQL, leftjoin

As the data volume and access volume of websites increase, the access speed slows down, so I began to find a solution to the slow speed of optimization.

The following is an SQL analysis process in the program. Of course, the execution efficiency of the program is not only about SQL statements, but also about server configuration, network speed, programming language, and other methods, today, let's analyzeEfficiency of left join in SQL statements

The SQL statement contains the following information:

1. SQL contains data processing functions, such as nvl and case when functions.

2. SQL contains inner join, left join, and other associations.

3. Sorting and paging in SQL

The following is the analysis process.

1. First, the sorting is removed, and the speed is indeed very fast, but there is no way, the sorting is necessary, just to prove that the sorting is indeed very resource-consuming.

2. Remove functions such as nvl and case when, and the result speed is almost unchanged.

3. Remove the inner join table at a speed of dozens of milliseconds.

4. Remove the left join table and increase the speed from 4 seconds to 1 second.

In summary, left join is the culprit of slow speed.Add left join fields as indexesIn the test, it is found that the speed can be kept at around 1 second. Solve the problem.

Conclusion: left join is a resource-consuming operation. If the associated fields are not indexed, the speed is very slow. Therefore, if left join is used,It is best to use index fields for Association. OrAdd an index to the associated field

Some people also said on the Internet that the table data on the Right of left join should be filled and left join should be replaced with inner join. I have tried it. The speed is indeed much faster, but this method is not tested considering the data volume.