记一次Sql优化过程

2年前 (2022) 程序员胖胖胖虎阿

305 0 0

这几天在写一个存储过程，反复优化了几次，从最开始的7分钟左右，优化到最后的几秒，并且这个过程中我的导师帮我指点了很多问题，这些指点都是非常宝贵的，独乐乐不如众乐乐，一起来分享这次的优化过程吧。

这个存过程的需求是这样的，抓取某个时间段内的订单明细，然后计算并汇总到某表即可。

于是乎，我写出第一版的存储过程，代码如下：

  /******************************************/
  /* 合并当前版本时间段内MO的维修换料需求   */
  /* p_begin 起始时间                       */
  /* p_user  创建人                         */
  /* p_version 版本编码                     */
  /* p_version 需求版本头表id               */
  /* Created by wufei in 2013-10-29         */
  /******************************************/
  procedure clc_Mat_Addition_Require(p_begin   date,
                                     p_user    varchar2,
                                     x_result  out varchar2,
                                     x_msg     out varchar2) is
            v_count int;        --处理行数
            v_num number;       --维修换料数
            v_version_code mms_mo_ori_version.version_code%type;     --版本号
            v_version_id   mms_mo_ori_version.version_id%type;       --需求单头号
            v_raise exception;
  begin
      v_version_code:=to_char(p_begin,'yyyyMMdd');
      v_version_id := fun_mms_get_guid();
      --查询历史版本表，已执行过不允许执行
      select count(*) into v_count from mms_mo_ori_version mmov 
      where mmov.version_code = to_char(p_begin,'yyyyMMdd');
      
      if v_count>0 then
         raise v_raise;
      end if;
      
       v_count:=0;
        --生成新版本头数据
        insert into mms_mo_ori_version
          (version_id,
           version_code,
           start_time,
           end_time,
           creation_date,
           created_by,
           mat_type) 
        values
          (v_version_id,
           v_version_code,
           p_begin,
           p_begin+1,
           sysdate,
           p_user,
           1);--类别：维修换料
      
      for line in (
          select cwr.inventory_item_id,cwr.item_code,cwr.description,sum(cwr.quantity_open) as quantity_open
          from ifce.cux_wip_requirement_v cwr,
               ifce.cux_wip_entity_all_v  cwal
          where cwr.WIP_ENTITY_ID = cwal.WIP_ENTITY_ID
                and cwal.START_DATE >= p_begin
                and cwal.START_DATE < p_begin+1
                and cwr.quantity_open > 0
                group by cwr.INVENTORY_ITEM_ID,cwr.item_code,cwr.description
                   ) 
      loop


      --获取维修换料数
      select ifce.wip_logistics.fun_get_Iqcquit_sum(line.ITEM_CODE,trunc(p_begin)) 
             into v_num from dual;
      if (v_num >0) then --当维修换料需求比例数大于0时插入
          insert into mms_mo_mat_require
            (mat_requireid,
             mat_id,
             mat_code,
             mat_desc,
             require_time,
             require_qty,
             status,
             creation_date,
             created_by,
             version,
             mat_type,
             super_market_inv_code)
            select fun_mms_get_guid(),               
                   line.inventory_item_id,
                   line.item_code,
                   line.description,
                   p_begin,
                   line.quantity_open*v_num,
                   '0',
                   sysdate,
                   p_user,
                   v_version_code,
                   1,
                   '42B'
              from dual;
            v_count:=v_count+1;                  
        end if;
          
          end loop;
          
          commit;
          x_result:='Y';
          x_msg:=to_char(v_count);
   exception
     when v_raise then
             x_result:='N';
             x_msg:='当前日期已执行过维修换料运算。';
      when others then
        rollback;
        x_result:='N';
        x_msg:='程序异常';
  end clc_Mat_Addition_Require;

View Code

代码是没有问题，运行结果也没有问题，但就是慢，经过导师指点，“cwr.WIP_ENTITY_ID = cwal.WIP_ENTITY_ID”这里是有问题，这两个表之间用这种方式连接，索引会不起作用，并且这个表的时间没有加索引，综合起来就比较慢了，大概需要7秒才能运行完成。

找到了问题之后，就开始了改写。

改写的逻辑是这样的：

1，首先不使用这种连接方式，并且从另外一个本地表中用时间做过滤，这个时间是有索引的。

2，从车间排程表中，查询出需要运行的单据，并遍历。

3，遍历单据的明细。

4，插入维修换料数据。

5，对已插入的维修换料数汇总。

改写之后代码如下：

  /******************************************/
  /* Mo维修换料需求运算                     */
  /* p_begin 起始时间                       */
  /* p_user  创建人                         */
  /* p_version 版本编码                     */
  /* p_version 需求版本头表id               */
  /* Created by wufei in 2013-10-29         */
  /******************************************/
  procedure clc_Mat_Addition_Require3(p_begin   date,
                                     p_user    varchar2,
                                     x_result  out varchar2,
                                     x_msg     out varchar2) is 
            v_count int;        --处理行数
            v_num number;       --维修换料数
            v_version_code mms_mo_ori_version.version_code%type;     --版本号
            v_version_id   mms_mo_ori_version.version_id%type;       --需求单头号
            v_raise exception;
            v_wareHouse  mms_dictionary_item.input_code1%type;       --补料仓代码                   
  begin
      v_version_code:=to_char(p_begin,'yyyyMMdd');
      v_version_id := fun_mms_get_guid();
      --查询字典表里的补料仓代码
      select mdi.input_code1 into v_wareHouse from mms_dictionary_item mdi 
      where code='AdditionWarehouse';
      --查询历史版本表，已执行过不允许执行
      select count(*) into v_count from mms_mo_ori_version mmov 
      where mmov.version_code = to_char(p_begin,'yyyyMMdd');
      
      if v_count>0 then
         raise v_raise;
      end if;
      
       v_count:=0;
        --生成新版本头数据
        insert into mms_mo_ori_version
          (version_id,
           version_code,
           start_time,
           end_time,
           creation_date,
           created_by,
           mat_type) 
        values
          (v_version_id,
           v_version_code,
           p_begin,
           p_begin+1,
           sysdate,
           p_user,
           1);--类别：维修换料          
     
    for line in (
          select wdps.wip_entity_id
          from  mms_wdps wdps
          where wdps.plan_date >= p_begin
                and wdps.plan_date <= p_begin+1             
                 ) 
    loop
      for detailLine in (
          select cwr.inventory_item_id,cwr.item_code,cwr.description,sum(cwr.quantity_open) as quantity_open
          from ifce.cux_wip_requirement_v cwr
          where cwr.WIP_ENTITY_ID = line.wip_entity_id              
                and cwr.quantity_open > 0
                group by cwr.INVENTORY_ITEM_ID,cwr.item_code,cwr.description
      )
      loop  
        --获取维修换料数
        select ifce.wip_logistics.fun_get_Iqcquit_sum(detailLine.ITEM_CODE,trunc(p_begin)) 
               into v_num from dual;
        if (v_num >0) then --当维修换料需求比例数大于0时插入          
            insert into mms_mo_mat_require
              (mat_requireid,
               mat_id,
               mat_code,
               mat_desc,
               require_time,
               require_qty,
               status,
               creation_date,
               created_by,
               version,
               mat_type,
               super_market_inv_code)
              select fun_mms_get_guid(),               
                     detailLine.inventory_item_id,
                     detailLine.item_code,
                     detailLine.description,
                     p_begin,
                     detailLine.quantity_open*v_num,
                     0,
                     sysdate,
                     p_user,
                     v_version_code,
                     5,--将Mat_type改为5，稍后过滤，汇总
                     v_wareHouse
                from dual;
              v_count:=v_count+1;                  
          end if;
       end loop;   
     end loop;          
          commit;
          --将当天插入的维修换料汇总
          insert into mms_mo_mat_require
              (mat_requireid,
               mat_id,
               mat_code,
               mat_desc,
               require_time,
               require_qty,
               status,
               creation_date,
               created_by,
               version,
               mat_type,
               super_market_inv_code)                
           (select fun_mms_get_guid(), 
                  mat_id,
                  mat_code,
                  mat_desc,
                  p_begin,
                  sum(require_qty) as qty,
                  0,
                  sysdate,
                  p_user,
                  v_version_code,
                  1,--类别：维修换料
                  v_wareHouse
                  from mms_mo_mat_require mr
            where  mr.mat_type=5 and mr.version=v_version_code
            group by  mat_id,
                      mat_code,
                      mat_desc);
          delete from mms_mo_mat_require where mat_type=5 and version=v_version_code;
          commit;
          
          x_result:='Y';
          x_msg:=to_char(v_count);
   exception
     when v_raise then
       rollback;
             x_result:='N';
             x_msg:='当前日期已执行过维修换料运算。';
      when others then

        x_result:='N';
        x_msg:='程序异常';
  end clc_Mat_Addition_Require3;

View Code

此时，运行效率已大大提升，测试了一下，只要0.42秒，但被导师看了之后，又提了几个问题。

1，程序中不应该使用两次commit;因为同一个会话中的数据是可以在检索到的，所以并不一定要提交到数据库才可以查看。

2，使用汇总插入并删除原来的数据也是不对的，因为针对数据库来说，删除是要写日志记录，耗费大量资源的。

所以针对此问题，又做了改动，把汇总并删除改为更新或插入。

代码如下：

  procedure clc_Mat_Addition_Require4(p_begin   date,
                                     p_user    varchar2,
                                     x_result  out varchar2,
                                     x_msg     out varchar2) is 
            v_count int;        --处理行数
            v_num number;       --维修换料数
            v_version_code mms_mo_ori_version.version_code%type;     --版本号
            v_version_id   mms_mo_ori_version.version_id%type;       --需求单头号
            v_raise exception;
            v_wareHouse  mms_dictionary_item.input_code1%type;       --补料仓代码    
            v_item_count int;                                        --物料明细行数;
  begin
      v_version_code:=to_char(p_begin,'yyyyMMdd');
      v_version_id := fun_mms_get_guid();
      --查询字典表里的补料仓代码
      select mdi.input_code1 into v_wareHouse from mms_dictionary_item mdi 
      where code='AdditionWarehouse';
      --查询历史版本表，已执行过不允许执行
      select count(*) into v_count from mms_mo_ori_version mmov 
      where mmov.version_code = to_char(p_begin,'yyyyMMdd');
      
      if v_count>0 then
         raise v_raise;
      end if;
      
       v_count:=0;
        --生成新版本头数据
        insert into mms_mo_ori_version
          (version_id,
           version_code,
           start_time,
           end_time,
           creation_date,
           created_by,
           mat_type) 
        values
          (v_version_id,
           v_version_code,
           p_begin,
           p_begin+1,
           sysdate,
           p_user,
           1);--类别：维修换料          
     
    for line in (
          select wdps.wip_entity_id
          from  mms_wdps wdps
          where wdps.plan_date >= p_begin
                and wdps.plan_date <= p_begin+1             
                 ) 
    loop
      for detailLine in (
          select cwr.inventory_item_id,cwr.item_code,cwr.description,sum(cwr.quantity_open) as quantity_open
          from ifce.cux_wip_requirement_v cwr
          where cwr.WIP_ENTITY_ID = line.wip_entity_id              
                and cwr.quantity_open > 0
                group by cwr.INVENTORY_ITEM_ID,cwr.item_code,cwr.description
      )
      loop  
        --获取维修换料数
        select ifce.wip_logistics.fun_get_Iqcquit_sum(detailLine.ITEM_CODE,trunc(p_begin)) 
               into v_num from dual;
        if (v_num >0) then --当维修换料需求比例数大于0时插入        
           select count(*) into v_item_count
           from mms_mo_mat_require 
           where mat_code=detailLine.item_code and version=v_version_code;
           
           if(v_item_count>0) then           
             update mms_mo_mat_require set require_qty= round(require_qty+detailLine.quantity_open*v_num)
             where mat_code=detailLine.item_code and version=v_version_code;
           else
            insert into mms_mo_mat_require
              (mat_requireid,
               mat_id,
               mat_code,
               mat_desc,
               require_time,
               require_qty,
               status,
               creation_date,
               created_by,
               version,
               mat_type,
               super_market_inv_code,
               attribute4)
              select fun_mms_get_guid(),               
                     detailLine.inventory_item_id,
                     detailLine.item_code,
                     detailLine.description,
                     p_begin,
                     round(detailLine.quantity_open*v_num),
                     0,
                     sysdate,
                     p_user,
                     v_version_code,
                     1,
                     v_wareHouse,
                     detailLine.quantity_open||' '||v_num||' '||line.wip_entity_id
                from dual;
             end if;
             v_count:=v_count+1;                  
          end if;
       end loop;   
     end loop;      
          commit;          
          x_result:='Y';
          x_msg:=to_char(v_count);
   exception
     when v_raise then
       rollback;
             x_result:='N';
             x_msg:='当前日期已执行过维修换料运算。';
      when others then
        x_result:='N';
        x_msg:='程序异常';
  end clc_Mat_Addition_Require4;