同样的sql开发库和测试库 效率差距很远啊

[YKnightMare]

同样的sql 但是开发库只需要0.5秒，测试库要20多秒

select *
  from (select oi.orderstate state,
               case
                 when mo.displayorderno is null then
                  trim(wo.ordertype) || wo.orderno
                 else
                  mo.displayorderno
               end orderno,
               mi.itemno,
               mi.orderid orderid,
               p.realpn pn,
               mb.reserved_qty,
               mb.stock,
               mb.location,
               mb.bn,
               p.partid,
               mb.issued_qty as issuedqty,
               mi.req_orderid as reqorderid,
               mi.qty as qty,
               mi.shortage_qty as shortageqty
          from mrqitem mi
          left join mrqitembatch mb
            on mb.orderid = mi.orderid
           and mb.itemno = mi.itemno
          left join orders wo
            on mi.req_orderid = wo.orderid
          left join orderitemstate oi
            on mi.orderid = oi.orderid
           and mi.itemno = oi.itemno
          left join pnrreg p
            on mi.partid = p.partid
          left join mat_orders mo
            on mo.orderid = wo.orderid
         where mb.stock = 'HAKM'
           and p.partid in (980005743, 980006342, 980006343, 980006644)
           and mb.reserved_qty > 0
           and (mb.reserved_qty - mb.issued_qty) > 0)
 where orderid <> '9823229'
 order by orderid desc

看了下执行计划如下图
开发库
测试库
还没试生产库，但是差距这么远怕到了生产出问题啊

[YKnightMare]

引用 2 楼 u012557814 的回复:

你的sql工具是不是限制条数输出了？看看设置。

结果一共只有4条。。执行计划的话我截图的是全部了。。

[YKnightMare]

还有把p.partid in (980005743, 980006342, 980006343, 980006644)换成mi.partid in (980005743, 980006342, 980006343, 980006644) 然后测试库也只要0.5秒不到了。。

[落落叶叶无声]

你的sql工具是不是限制条数输出了？看看设置。

[sxq129601]

没看见执行计划都不一样吗，把所有表先重新收集下统计信息再看看