大查询下,一个子查询,造成90%的占用,一共三个类似查询,加上需要10秒,不加上秒开
(select top 1 cfe4 from a_spmx where spbh=V.spbh and ckbh=V.ckbh and scph=V.scph and dwid=v.dwid and isnull(cfe4,'')<>'') cfe4
一共三个类似的
还有一个
isnull((select top 1 GFBH from k_rkw w left join k_rk t on w.link=t.link where ckbh=v.ckbh and spbh=v.spbh and scph=v.scph order by t.line desc),0) GFBH,
去掉 order by t.line desc 速度就没有问题,加上,需要5秒才开
(select top 1 cfe4 from a_spmx where spbh=V.spbh and ckbh=V.ckbh and scph=V.scph and dwid=v.dwid and isnull(cfe4,'')<>'') cfe4
一共三个类似的
还有一个
isnull((select top 1 GFBH from k_rkw w left join k_rk t on w.link=t.link where ckbh=v.ckbh and spbh=v.spbh and scph=v.scph order by t.line desc),0) GFBH,
去掉 order by t.line desc 速度就没有问题,加上,需要5秒才开
计划下提示 索引假脱机造成开销
isnull((select top 1 GFBH from k_rkw w left join k_rk t on w.link=t.link where ckbh=v.ckbh and spbh=v.spbh and scph=v.scph order by t.line desc),0) GFBH,
语句提示排序占用开销
实在不行,去掉排序后,不占用开销。()
GFMC 供方名称,
仓库,
商品,
其它二,
批号,
SPSL,
DFE1,
每根重量
,CFE1
,CFE3
,CFE4
FROM
(Select
CKMC 仓库,
s.spmc 品号,
SPMC 商品,
ZHBZ 其它二,
SCPH 批号,
YESL+isnull((select sum(spsl) from k_rkw w left join k_rk t on w.link=t.link where bstt=0 and ckbh=v.ckbh and spbh=v.spbh and scph=v.scph),0) SPSL,
YESL1+isnull((select sum(w.dfe1) from k_rkw w left join k_rk t on w.link=t.link where bstt=0 and ckbh=v.ckbh and spbh=v.spbh and scph=v.scph),0) DFE1,
YEJE+isnull((select sum(spje) from k_rkw w left join k_rk t on w.link=t.link where bstt=0 and ckbh=v.ckbh and spbh=v.spbh and scph=v.scph),0) 库存成本,
isnull((select top 1 agsl from k_rkw w left join k_rk t on w.link=t.link where ckbh=v.ckbh and spbh=v.spbh and scph=v.scph),0) 每根重量,
isnull((select top 1 GFBH from k_rkw w left join k_rk t on w.link=t.link where ckbh=v.ckbh and spbh=v.spbh and scph=v.scph order by t.line desc),0) GFBH
,(select top 1 cfe1 from a_spmx where spbh=V.spbh and ckbh=V.ckbh and scph=V.scph and dwid=v.dwid and isnull(cfe1,'')<>'') cfe1
,(select top 1 cfe3 from a_spmx where spbh=V.spbh and ckbh=V.ckbh and scph=V.scph and dwid=v.dwid and isnull(cfe3,'')<>'') cfe3
,(select top 1 cfe4 from a_spmx where spbh=V.spbh and ckbh=V.ckbh and scph=V.scph and dwid=v.dwid and isnull(cfe4,'')<>'') cfe4
-- 注释掉上面三个查询速度就正常,不注释掉注释就很慢
From V_BAT V
LEFT JOIN D_CK C ON C.CKBH=V.CKBH
LEFT JOIN D_SP S ON S.SPBH=V.SPBH
Where
C.CKBH LIKE '%'
and S.SPBH LIKE '%'
and v.dwid like '0%'
)
A LEFT JOIN D_GYS S ON A.GFBH=S.GFBH
WHERE
A.GFBH LIKE '%'
and round(dfe1,4)<>0
spbh=V.spbh and ckbh=V.ckbh and scph=V.scph
注释掉这三个条件,查询速度也正常,
感觉这三个条件和上边的语句里的V.scph V.ckbh V.spbh 定义有问题
精简到下边
Select (select top 1 cfe1 from a_spmx where spbh=test1.spbh and ckbh=test1.ckbh and scph=test1.scph and dwid=test1.dwid and isnull(cfe1,'')<>'') cfe1
,(select top 1 cfe3 from a_spmx where spbh=test1.spbh and ckbh=test1.ckbh and scph=test1.scph and dwid=test1.dwid and isnull(cfe3,'')<>'') cfe3
,(select top 1 cfe4 from a_spmx where spbh=test1.spbh and ckbh=test1.ckbh and scph=test1.scph and dwid=test1.dwid and isnull(cfe4,'')<>'') cfe4
From
V_BAT
test1
LEFT JOIN D_CK C ON C.CKBH=test1.CKBH
LEFT JOIN D_SP S ON S.SPBH=test1.SPBH
Where
C.CKBH LIKE '%'
and S.SPBH LIKE '%'
and test1.dwid like '0%'所有的问题都是这个查询造成test1 是一个随便命名的虚表
isnull(cfe1,'')<>''这个可以简化为cfe1<>''
null<>''是成立的
null<>''是成立的,比较的结果是false,对你的筛选来说是适用的,也就是说null的数据比较都是false,所以不用特意去转换
create index IX_a_spmx_INDEX on a_spmx (spbh,ckbh,scph,dwid,cfe1,cfe2,cfe3,cfe4,orderby的列)