sql 一段代码优化

[ebony0319]

https://gist.github.com/ebony0319/807231c11214a417cec50157930f1ead  显示 Gist 代码 

很长。非常长。
太多的左联结，但是每一个又不能少。
前端查询有点慢，点一下要转很多个圈。
这段代码其实主要突出的是入库情况，但是有些信息必须包含进去。于是想要什么就从对应的表去取值。于是太多的 left outer join
还有一点就是类似于这种
left outer join (select orderno,ordersn,slid, shnum num,count(*) rkcount from jy_rksq a group by orderno,ordersn,slid,shnum) rkcount on......
我不是很确定对先对全表进行了扫描然后在联结。

[wujunze]

楼主可以贴出表结构 方便优化 SQL

[ebony0319]

我这里再粘贴一下。有点混乱
select a.yt 用途说明,hw.HW 仓位,b.productno 物料编号,b.productname 物料名称 ,a.num 转入数,a.shnum 入库,a.dj 送货单价,a.dj*a.shnum 金额,
case when isnull(a.num,0)-isnull(a.shnum,0)> 0 then isnull(a.num,0)-isnull(a.shnum,0) else null end 存,
a.outnum 出 ,a.shdate 收货时间,a.shdh 送货单号,a.indate 转入时间 , jldw.dwname 单位,a.weight 重量,fz.productno 辅助编号,fz.productname 辅助名称,
a.cw 财务 ,a.orderno 订单编号,a.ordersn 笔数,o.peitaosn 配套,a.supliername 供应商, a.pjhpz 票据凭证,cg.dj 采购价, b.productcgj 受控价,a.cgdh 采购单号,
a.shperson 收货人,a.shno 入库单号,a.ckname 仓库,cgcount.cgcount 采购次数,rkcount.rkcount 入库次数,dbcount.dbcount 出仓次数,db2count.db2count 出仓 2 次数,
a.person 转入人,a.outno 转出编号,a.outperson 转出人,a.outdate 转出时间,a.slid,a.id,a.source,a.sourcedw 来源单位,a.sourceid,a.cgtype,a.xq2id,a.oldslid,
u.empname as 跟单人,ckname.ckname as 下单档口,b.newproductno ,
case when isnull(b.photo,'')='' then fz.photo else b.photo end as photo ,a.yck,
case when isnull(a.cgtype,0)=0 then '进仓 1'
when isnull(a.cgtype,0)=2 then '进仓 2' when isnull(a.cgtype,0)=3 then '换进仓' when isnull(a.cgtype,0)=4 then '退料进仓' end as 进仓类别
from rksq a left outer join product b on a.slid=b.id
left outer join order o on a.orderno=o.orderno and a.ordersn=o.ordersn
left outer join (select orderno,ordersn,slid,num,count(*) cgcount from cgwlsqdetail a group by orderno,ordersn,slid,num) cgcount on a.orderno=cgcount.orderno and a.ordersn=cgcount.ordersn and a.slid=cgcount.slid and a.num=cgcount.num
left outer join (select orderno,ordersn,slid, shnum num,count(*) rkcount from rksq a group by orderno,ordersn,slid,shnum) rkcount on a.orderno=rkcount.orderno and a.ordersn=rkcount.ordersn and a.slid=rkcount.slid and a.num=rkcount.num
left outer join (select distinct cgdh,dj,isnull(orderno,'') orderno,ordersn,slid from cgwlsqdetail) cg on a.cgdh=cg.cgdh and a.orderno=cg.orderno and a.ordersn=cg.ordersn and a.slid=cg.slid
left outer join (select orderno,ordersn,slid,dbnum num,count(*) dbcount from dbwlsqdetail a group by orderno,ordersn,slid,dbnum)
dbcount on a.orderno=dbcount.orderno and a.ordersn=dbcount.ordersn and a.slid=dbcount.slid and a.num=dbcount.num
left outer join (select orderno,ordersn,slid,dbnum num,count(*) db2count from dbwlsqdetail2 a group by orderno,ordersn,slid,dbnum) db2count on a.orderno=db2count.orderno and a.ordersn=db2count.ordersn and a.slid=db2count.slid and a.num=db2count.num
left outer join (select orderno,ordersn,slid, num,count(*) sfcount from sfsq a group by orderno,ordersn,slid,num) sfcount on a.orderno=sfcount.orderno and a.ordersn=sfcount.ordersn and a.slid=sfcount.slid and a.num=sfcount.num
left outer join user u on o.gdr=u.id left outer join ckname ckname on o.ckorderid=ckname.id
left outer join product cp on o.productid=cp.id
left outer join jldw jldw on b.jldwid=jldw.id
left outer join ( select a.ID,a.ProductID,a.ckid,a.HW,a.Memo ,b.ckName from HW a, ckName b where a.CKID =b.ID and b.ckname='商务' ) hw on a.SLID=hw.ProductID
left outer join product fz on b.fzcode = fz.productno where 1=1 and a.ckname='商务' and ( isnull(a.cgtype,0)=0 or ( isnull(a.cgtype,0)=2 ) ) and isnull(a.outnum,0)=0 order by a.id desc

[ebony0319]

@wujunze 有道理。我这就写出来。

[ebony0319]

@wujunze 数据库有两台连接的主线，一条是订单号和订单的笔数，一条是产品的 ID,通过这两个可以把所有的表都联系起来。表有点多，架构师走了。我现在只知道一些基础的表，一般都是跟踪查询的。 HW 是库位表， order 是订单表。后面一大堆还有采购表，物料需求，供应商表，然后还有什么入库什么表，出仓申请表，库存表....

[WhoMercy]

我的天...这种不经过优化的 SQL ，跟上家一模一样，都是设计的锅，开发者一味追求“方便”、“快捷”，根本不考虑扩展性和效率。
想要在 SQL 层优化是很难的，而且徒劳。因为如你所说，最后会发现，每个表的字段其实都是必要的。
我给两个思路：
1.页面分次加载数据，在需要的时候再查询某几个数据，如 A 表名和规格先显示，等用到 B 表数据时再去查询 B 表，不要一次性全部查询出来，如果前台不用显示保存后的数据，还可以异步更新数据；
2.添加冗余表、中间表或者缓存数据表，这需要有个缓存的生成机制， Memcache 、 Redis 、 MongoDB 、 Lucence 等很多工具都能实现该效果；

[jhdxr]

我也来补充个方案，换 Oracle

[abnerchou]

这么多表……建议先 populate 到小的 temp table 中（用 Inner Join 和 Insert ），最后输出的时候再根据需要用 Left Join 。这样可以缩小表的大小，加快检索速度。

[lianxiaoyi]

神一般的 SQL