ORACLE应用调优:请避免SQL做大量循环逻辑处理

     前阵子遇到一个案例:一个同事说以前一个运行很正常的包,突然间比以前慢了很多,执行时间非常长,晚上的作业调用这个包跑了几个小时也没有跑出数据。于是 我在跟踪、优化过程中定位到包中一个存储过程的一段SQL,我将原SQL简化了一下(对应的表名、函数全都随机取名替换掉),大体如下所示,在一个游标 中,循环更新表TMP_JO_ORDERS, 其中需要通过函数获取一些值,这些值用来更新目标表的字段值

 

FOR CUR_JO IN (SELECT JOB_ORDER_NO FROM TMP_JO_ORDERS WHERE SEW_START >=SYSDATE ) LOOP

        SELECT MAIN_ITC.GET_MUST_INFO(CUR_JO.JOB_ORDER_NO,'SEWING','BUTTON')   INTO MY_M_BUTTON FROM DUAL;
        SELECT MAIN_ITC.GET_MUST_INFO(CUR_JO.JOB_ORDER_NO,'SEWING','LABEL')    INTO MY_M_LABEL  FROM DUAL;
        SELECT MAIN_ITC.GET_MUST_INFO(CUR_JO.JOB_ORDER_NO,'SEWING','TAPE')     INTO MY_M_TAPE   FROM DUAL;
        SELECT MAIN_ITC.GET_MUST_INFO(CUR_JO.JOB_ORDER_NO,'SEWING','ZIPPER')   INTO MY_M_ZIPPER FROM DUAL;
        SELECT MAIN_ITC.GET_MUST_INFO(CUR_JO.JOB_ORDER_NO,'SEWING','OTHERS')   INTO MY_M_OTHERS FROM DUAL;
        SELECT MAIN_ITC.GET_MUST_INFO(CUR_JO.JOB_ORDER_NO,'THREAD','ALL')      INTO MY_M_THREAD FROM DUAL;
        SELECT MAIN_ITC.GET_MUST_INFO(CUR_JO.JOB_ORDER_NO,'INTERLINING','ALL') INTO MY_M_INTERLINING FROM DUAL;
        SELECT MAIN_ITC.GET_MUST_INFO(CUR_JO.JOB_ORDER_NO,'PACKING','ALL')     INTO MY_M_PACKING FROM DUAL;
       
        UPDATE TMP_JO_ORDERS A
        SET M_BUTTON=MY_M_BUTTON
             ,M_LABEL=MY_M_LABEL
             ,M_TAPE=MY_M_TAPE
             ,M_ZIPPER=MY_M_ZIPPER
             ,M_OTHERS=MY_M_OTHERS
             ,M_THREAD=MY_M_THREAD
             ,M_INTERLINING=MY_M_INTERLINING
             ,M_PACKING=MY_M_PACKING
        WHERE JOB_ORDER_NO=CUR_JO.JOB_ORDER_NO;
END LOOP;

 

其 实以前运行正常,突然出现性能问题,是因为SELECT JOB_ORDER_NO FROM TMP_JO_ORDERS WHERE SEW_START >=SYSDATE的数据量由于业务量突然增加了很多,所以游标的循环次数从以前几十次突然飚增到8千多次。

假设游标里面的SQL执行时间需要2秒,以前只循环了30次,那么运算该SQL需要2*30=60秒,如果循环次数突然飚增到8000次,2*8000=16000秒,这就是几个小时的时间。你可以想象一下,这个性能会突然下降到一种无法忍受的程度!

那么怎么优化呢? 当然是减少循环次数。仔细观察了这段SQL,弄明白写这个SQL的老兄的业务逻辑后,上面的循环处理完全可以用下面一个SQL语句替换,完全没有必要一条记录一条记录更新。当时修改后测试,发现修改后的SQL,不到1分钟就运行出来了。

UPDATE TMP_JO_ORDERS A
        SET M_BUTTON     =MAIN_INTERFACE.GET_MUST_INFO(JOB_ORDER_NO,'SEWING','BUTTON')
             ,M_LABEL      =MAIN_INTERFACE.GET_MUST_INFO(JOB_ORDER_NO,'SEWING','LABEL')
             ,M_TAPE       =MAIN_INTERFACE.GET_MUST_INFO(JOB_ORDER_NO,'SEWING','TAPE')
             ,M_ZIPPER     =MAIN_INTERFACE.GET_MUST_INFO(JOB_ORDER_NO,'SEWING','ZIPPER')
             ,M_OTHERS     =MAIN_INTERFACE.GET_MUST_INFO(JOB_ORDER_NO,'SEWING','OTHERS')
             ,M_THREAD     =MAIN_INTERFACE.GET_MUST_INFO(JOB_ORDER_NO,'THREAD','ALL')
             ,M_INTERLINING=MAIN_INTERFACE.GET_MUST_INFO(JOB_ORDER_NO,'INTERLINING','ALL')
             ,M_PACKING    =MAIN_INTERFACE.GET_MUST_INFO(JOB_ORDER_NO,'PACKING','ALL')
    WHERE SEW_START >=SYSDATE;

 

     其实这只是一个特殊的案例,我只是将其当做一个引子,引入我想阐述的观点:我们知道SQL是结构化查询语言,擅长于结构化查询,而不擅长于逻辑处理 (WHIE、IF..ELSE),但是有时候,很多人喜欢用SQL来处理业务逻辑,当然也不是说不能在存储过程、函数里面做一些业务逻辑处理,只是发现不 少人过度放大SQL的逻辑处理功能,将复杂的逻辑运算全部搬到包、存储过程里面处理,例如上面的循环运算,这样做的一个糟糕结果就是性能问题,就好像一个 擅长于短跑的人,你硬要他去参加长跑。那么比赛结果肯定不会好到哪里去。

 

    在开发中,我们要对业务逻辑做一些优化处理,避免复杂的逻辑运算,尤其避免循环次数非常大的业务逻辑处理,一方面我们要简化业务逻辑,有些业务逻辑运算转 到程序中去处理,另外一方面我们可以用SQL很巧妙的实现很多逻辑复杂的需求,避免我们去做大量复杂的逻辑处理,而不要在复杂的业务下写出更加复杂的 SQL语句.例如上面的例子,我以前在一篇文章MS SQL 挑战问题也述说了这样一种观念。

上一篇:Open Cascade中的内存管理


下一篇:前端特殊错误,Bug汇总