Execution of SQL Statements Is Slow Due to Nested Loops in Execution Plans

Symptom

In a service environment, the performance of SQL queries is slow, and a Nested Loop Join is observed within the generated execution plan.

Cause Analysis

1. Run the EXPLAIN VERBOSE command to print the statement execution plan. As shown in the following figure, the SQL statement contains the NOT IN statement.

   

2. Check whether there are nested loops in the execution plan.

   

Analysis Results

• Nested loop is the main cause of slow statement execution.
• The NOT IN clause processes NULL values, which can lead to inefficient processing of statements through HashJoin, resulting in suboptimal performance.

Handling Procedure

1. In scenarios where the handling of NULL values is not a concern for users, or if NULL values are absent in the data, substituting NOT IN with NOT EXISTS can be an effective optimization strategy.