批处理，更新数据库时，效率很低【已解决】

这个问题解决了，给where条件中的字段加个索引就好了。谢谢大家了！收起

你的业务结构过于复杂，这里建议如下
1) 不要将所有的更新放在同一个逻辑里面做，适当拆分;
2) 同一个更新尽量在一个SQL中去完成，比如用上面的同学提到的MERGE语句;收起

批处理 不好一个record，一个RECORD 做。COMMIT IS EXPENSIVE.收起

用merge试试呢收起

DROP PROCEDURE "SINO_ZSK"."SETRKJYMX";
CREATE PROCEDURE "SINO_ZSK"."SETRKJYMX" ()
  SPECIFIC "SINO_ZSK"."SQL140821134825571"
  LANGUAGE SQL
  NOT DETERMINISTIC
  CALLED ON NULL INPUT
  EXTERNAL ACTION
  OLD SAVEPOINT LEVEL
  MODIFIES SQL DATA
  INHERIT SPECIAL REGISTERS
  BEGIN
  DECLARE SQLCODE INT;
  DECLARE  v_jyxxnm  VARCHAR(32);
  DECLARE  v_kzz  VARCHAR(50);
  DECLARE  v_ksf  VARCHAR(50);
  DECLARE  v_kbwsl  VARCHAR(50);
  DECLARE  v_ksml  VARCHAR(50);
  DECLARE  v_kzjml  VARCHAR(50);
  DECLARE  v_kgwcm  VARCHAR(50);
  DECLARE  v_khlm  VARCHAR(50);
  DECLARE  v_khhl  VARCHAR(50);
  
  DECLARE  v_sfkl  VARCHAR(50);
  DECLARE  v_zzkl  VARCHAR(50);
  DECLARE  v_bwslkl  VARCHAR(50);
  DECLARE  v_smlkl  VARCHAR(50);
  DECLARE  v_zjmlkl  VARCHAR(50);
  DECLARE  v_gwcmkl  VARCHAR(50);
  DECLARE  v_hlmkl  VARCHAR(50);
  DECLARE  v_hhlkl  VARCHAR(50);
  DECLARE C1 CURSOR FOR
    SELECT A.RC_RKJYXX_RKJYXXNM
        , to_char(A.RC_RKJYXX_KZZ) AS RC_RKJYXX_KZZ
        , to_char(A.RC_RKJYXX_KSF) AS RC_RKJYXX_KSF
        , to_char(A.RC_RKJYXX_KBWSL) AS RC_RKJYXX_KBWSL
        , to_char(A.RC_RKJYXX_KSML) AS RC_RKJYXX_KSML
        , to_char(A.RC_RKJYXX_KZJML) AS RC_RKJYXX_KZJML
        , to_char(A.RC_RKJYXX_KGWCM) AS RC_RKJYXX_KGWCM
        , to_char(A.RC_RKJYXX_KHLM) AS RC_RKJYXX_KHLM
        , to_char(A.RC_RKJYXX_KHHL) AS RC_RKJYXX_KHHL
        
        , to_char(B.RC_RKJJXX_SFKL) AS RC_RKJJXX_SFKL
        , to_char(B.RC_RKJJXX_ZZKL) AS RC_RKJJXX_ZZKL
        , to_char(B.RC_RKJJXX_BWSLKL) AS RC_RKJJXX_BWSLKL
        , to_char(B.RC_RKJJXX_SMLKL) AS RC_RKJJXX_SMLKL
        , to_char(B.RC_RKJJXX_ZJMLKL) AS RC_RKJJXX_ZJMLKL
        , to_char(B.RC_RKJJXX_GWCMKL) AS RC_RKJJXX_GWCMKL
        , to_char(B.RC_RKJJXX_HLMKL) AS RC_RKJJXX_HLMKL
        , to_char(B.RC_RKJJXX_HHLKL) AS RC_RKJJXX_HHLKL
    FROM RC_RKJYXX A
        LEFT JOIN RC_RKJJXX B ON A.RC_RKJYXX_ZZNM = B.RC_RKJJXX_ZZNM AND A.RC_RKJYXX_RKDNM = B.RC_RKJJXX_RKDNM
    WHERE RC_RKJYXX_JYSJ >= '201401' AND (A.RC_RKJYXX_KZZ IS NOT NULL OR A.RC_RKJYXX_KSF IS NOT NULL OR
        A.RC_RKJYXX_KBWSL IS NOT NULL OR A.RC_RKJYXX_KSML IS NOT NULL OR A.RC_RKJYXX_KZJML IS NOT NULL OR
        A.RC_RKJYXX_KGWCM IS NOT NULL OR A.RC_RKJYXX_KHLM IS NOT NULL OR A.RC_RKJYXX_KHHL IS NOT NULL OR
        B.RC_RKJJXX_SFKL IS NOT NULL OR B.RC_RKJJXX_ZZKL IS NOT NULL OR
        B.RC_RKJJXX_BWSLKL IS NOT NULL OR B.RC_RKJJXX_SMLKL IS NOT NULL OR B.RC_RKJJXX_ZJMLKL IS NOT NULL OR
        B.RC_RKJJXX_GWCMKL IS NOT NULL OR B.RC_RKJJXX_HLMKL IS NOT NULL OR B.RC_RKJJXX_HHLKL IS NOT NULL)
        AND (A.RC_RKJYXX_KZZ <> 0 OR A.RC_RKJYXX_KSF <> 0 OR
        A.RC_RKJYXX_KBWSL <> 0 OR A.RC_RKJYXX_KSML <> 0 OR A.RC_RKJYXX_KZJML <> 0 OR
        A.RC_RKJYXX_KGWCM <> 0 OR A.RC_RKJYXX_KHLM <> 0 OR A.RC_RKJYXX_KHHL <> 0 OR
        B.RC_RKJJXX_SFKL <> 0 OR B.RC_RKJJXX_ZZKL <> 0 OR
        B.RC_RKJJXX_BWSLKL <> 0 OR B.RC_RKJJXX_SMLKL <> 0 OR B.RC_RKJJXX_ZJMLKL <> 0 OR
        B.RC_RKJJXX_GWCMKL <> 0 OR B.RC_RKJJXX_HLMKL <> 0 OR B.RC_RKJJXX_HHLKL <> 0);

       
  OPEN C1;
  FETCH C1 INTO v_jyxxnm,v_kzz,v_ksf,v_kbwsl,v_ksml,v_kzjml,v_kgwcm,v_khlm,v_khhl,
                v_sfkl,v_zzkl,v_bwslkl,v_smlkl,v_zjmlkl,v_gwcmkl,v_hlmkl,v_hhlkl;
  WHILE ( SQLCODE = 0 ) DO
        UPDATE RC_RKJYMX SET RC_RKJYMX_KLBL = v_kzz,RC_RKJYMX_KLSL = v_sfkl WHERE RC_RKJYMX_RKJYXXNM = v_jyxxnm AND RC_RKJYMX_JYXMNM = '001';
        UPDATE RC_RKJYMX SET RC_RKJYMX_KLBL = v_ksf,RC_RKJYMX_KLSL = v_zzkl WHERE RC_RKJYMX_RKJYXXNM = v_jyxxnm AND RC_RKJYMX_JYXMNM = '002';
        UPDATE RC_RKJYMX SET RC_RKJYMX_KLBL = v_kbwsl,RC_RKJYMX_KLSL = v_bwslkl WHERE RC_RKJYMX_RKJYXXNM = v_jyxxnm AND RC_RKJYMX_JYXMNM = '003';
        UPDATE RC_RKJYMX SET RC_RKJYMX_KLBL = v_ksml,RC_RKJYMX_KLSL = v_smlkl WHERE RC_RKJYMX_RKJYXXNM = v_jyxxnm AND RC_RKJYMX_JYXMNM = '011';
        UPDATE RC_RKJYMX SET RC_RKJYMX_KLBL = v_kzjml,RC_RKJYMX_KLSL = v_zjmlkl WHERE RC_RKJYMX_RKJYXXNM = v_jyxxnm AND RC_RKJYMX_JYXMNM = '008';
        UPDATE RC_RKJYMX SET RC_RKJYMX_KLBL = v_kgwcm,RC_RKJYMX_KLSL = v_gwcmkl WHERE RC_RKJYMX_RKJYXXNM = v_jyxxnm AND RC_RKJYMX_JYXMNM = '029';
        UPDATE RC_RKJYMX SET RC_RKJYMX_KLBL = v_khlm,RC_RKJYMX_KLSL = v_hlmkl WHERE RC_RKJYMX_RKJYXXNM = v_jyxxnm AND RC_RKJYMX_JYXMNM = '027';
        UPDATE RC_RKJYMX SET RC_RKJYMX_KLBL = v_khhl,RC_RKJYMX_KLSL = v_hhlkl WHERE RC_RKJYMX_RKJYXXNM = v_jyxxnm AND RC_RKJYMX_JYXMNM = '028';
        FETCH C1 INTO v_jyxxnm,v_kzz,v_ksf,v_kbwsl,v_ksml,v_kzjml,v_kgwcm,v_khlm,v_khhl,
                v_sfkl,v_zzkl,v_bwslkl,v_smlkl,v_zjmlkl,v_gwcmkl,v_hlmkl,v_hhlkl;
  END WHILE;
  CLOSE C1;
END;收起