Shulou(Shulou.com)05/31 Report--

This article mainly shows you the "sample analysis of left join in sql", which is easy to understand and clear. I hope it can help you solve your doubts. Let the editor lead you to study and study the "sample analysis of left join in sql".

As the amount of data and visits to the website becomes larger and larger, the speed of access becomes slower and slower, so we begin to find ways to solve the reasons for the slow speed of optimization.

The following is the analysis process of a sql in the program. Of course, the execution efficiency of the program is not only the problem of sql statements, but also the problems of server configuration, network speed, program language and so on. Today, let's first analyze the efficiency of left join in sql statements.

The sql statement contains the following information:

1. Sql includes data processing functions, such as nvl function, case when function, etc.

2. Inner join,left join and other relationships are included in sql.

3. Sorting and paging are available in sql

The following is the analysis process

1. First of all, get rid of the sorting, the speed is really fast, but there is no way, sorting is necessary, this is just to prove that sorting is really resource-consuming.

2. If the functions such as nvl,case when are removed, the speed of the result is almost unchanged.

3. The speed of removing the inner join table is a few tens of milliseconds faster.

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

Taken together, left join is the culprit of the slow speed, so all the fields of left join association relations are indexed. In the test, it is found that the speed can be maintained at about 1 second. Problem solved.

Conclusion: left join is a very resource-intensive operation, and the speed is very slow if the associated field does not have an index, so if there is a left join, it is best to associate it with an index field. Or index the associated field

Someone on the Internet also said that make up the table data on the right side of left join, and then replace left join with inner join. I have tried, and the speed is indeed much faster, but considering the problem of the amount of data, this method is not tested.

The above is all the content of the article "sample Analysis of left join in sql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.