数据查询读取优化

主要随着数据的增加，用到in的，消耗时间几何增长。由于在前期测试评估不到位，没有查看具体的代码。测试版数据量跟线上的数据差距很大，一般很难看出。对于涉及多表复杂查询的功能需要特别留意。

优化前 线上74.031秒，测试版0.031秒。随着数据量增加而增加。

select *

  from (select "Extent1".*,row_number() OVER(ORDER BY ID desc) as "row_number"from (select *from (select s.id,s.shipment_no,s.order_no,s.destination,s.estimated_ship_date,s.orig_warehouse_idfrom ship_shipment sWhere is_deleted = 0and not exists(select sst.idfrom ship_shipment sstleft join ship_picklist_item spion spi.shipment_id = sst.idleft join ship_picklist spon sp.id = spi.picklist_idinner join (select te.type,ti.work_effort_id,ti.bill_id_tofrom trsf_work_effort teinner join trsf_work_effort_item tion ti.work_effort_id = te.idand te.type = 13where ti.is_deleted = 0) ton t.bill_id_to = spi.idwhere spi.is_deleted = 0and sp.pick_type = 3and sst.id = s.id)and not exists(select pi.shipment_idfrom ship_picklist_item pileft join ship_picklist sppon spp.id = pi.picklist_idleft join trsf_work_effort tweon twe.id = pi.work_effort_idwhere spp.pick_type = 3and twe.status != 3and pi.is_deleted = 0and pi.shipment_id = s.idgroup by pi.shipment_id)) ssWhere 1 = 1and id in(select i.shipment_idfrom ship_picklist_item iinner join ship_picklist spon sp.id = i.picklist_idand sp.pick_type = 3where i.is_deleted = 0group by i.shipment_idhaving sum(nvl(i.quantity_picked, 0)) = sum(nvl(i.quantity_sorted, 0)) and sum(nvl(i.quantity_picked, 0)) > 0)and ss.ORIG_WAREHOUSE_ID = 170) "Extent1") "Extent2"where "row_number" > 0and rownum <= 20

优化后：线上0.359秒  测试版：0.047秒

select *

  from (select s.id,s.shipment_no,s.order_no,s.destination,s.estimated_ship_date,s.orig_warehouse_idfrom ship_shipment sWhere is_deleted = 0 and not exists(select sst.idfrom ship_shipment sstleft join ship_picklist_item spion spi.shipment_id = sst.idleft join ship_picklist spon sp.id = spi.picklist_idinner join (select te.type, ti.work_effort_id, ti.bill_id_tofrom trsf_work_effort teinner join trsf_work_effort_item tion ti.work_effort_id = te.idand te.type = 13where ti.is_deleted = 0) ton t.bill_id_to = spi.idwhere spi.is_deleted = 0and sp.pick_type = 3and sst.id = s.id)and not exists (select pi.shipment_idfrom ship_picklist_item pileft join ship_picklist sppon spp.id = pi.picklist_idleft join trsf_work_effort tweon twe.id = pi.work_effort_idwhere spp.pick_type = 3and twe.status != 3and pi.is_deleted = 0and pi.shipment_id = s.idgroup by pi.shipment_id)) ss,
(select i.shipment_idfrom ship_picklist_item i inner join ship_picklist spon sp.id = i.picklist_idand sp.pick_type = 3where i.is_deleted = 0 group by i.shipment_idhaving sum(nvl(i.quantity_picked, 0)) = sum(nvl(i.quantity_sorted, 0)) and sum(nvl(i.quantity_picked, 0)) > 0) spi where ss.id = spi.shipment_id and ss.orig_warehouse_id = 1

转载于:https://www.cnblogs.com/llvoeto/p/6085954.html

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！