更新时间:2022-07-29 GMT+08:00

案例:改写SQL消除子查询(案例2)

现象描述

某局点客户反馈如下SQL语句的执行时间超过1天未结束:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
UPDATE calc_empfyc_c_cusr1 t1
SET ln_rec_count =
 (
    SELECT CASE WHEN current_date - ln_process_date + 1 <= 12 THEN 0 ELSE t2.ln_rec_count END 
    FROM calc_empfyc_c1_policysend_tmp t2
    WHERE t1.ln_branch = t2.ln_branch AND t1.ls_policyno_cusr1 = t2.ls_policyno_cusr1
)
WHERE dsign = '1'
AND flag = '1'
AND EXISTS
    (SELECT 1
    FROM calc_empfyc_c1_policysend_tmp t2
    WHERE t1.ln_branch = t2.ln_branch AND t1.ls_policyno_cusr1 = t2.ls_policyno_cusr1
    );

对应的执行计划如下:

优化说明

很明显,执行计划中存在SubPlan,并且SubPlan中的运算相当重,即此SubPlan是一个明确的性能瓶颈点。

根据SQL语意等价改写SQL消除SubPlan如下:

1
2
3
4
5
6
UPDATE calc_empfyc_c_cusr1 t1
SET ln_rec_count = CASE WHEN current_date - ln_process_date + 1 <= 12 THEN 0 ELSE t2.ln_rec_count END
FROM calc_empfyc_c1_policysend_tmp t2
WHERE 
t1.dsign = '1' AND t1.flag = '1' 
AND t1.ln_branch = t2.ln_branch AND t1.ls_policyno_cusr1 = t2.ls_policyno_cusr1;

改写之后SQL语句在50S内执行完成