记一次Sql优化过程
这几天在写一个存储过程，反复优化了几次，从最开始的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;
代码是没有问题，运行结果也没有问题，但就是慢，经过导师指点，“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;
此时，运行效率已大大提升，测试了一下，只要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;
今天既学到了知识，又有工资拿，真是太开心啦，哈哈哈。。。
各位看官，如果有更好的建议，不吝赐教，欢迎指导。
