语句如下:
select distinct
left(a.spmch,14) as spmch,a.tongym,left(a.shpgg,15) as shpgg,left(a.shpchd,8) as shpchd,hshsj,hshsj as hshj,a.pfpj,a.dbj,a.lshj,
b.shl-isnull(e.shl,0)-isnull(z.shl,0)-isnull(m.shl,0)-isnull(n.shl,0)-isnull(p.shl,0) as kcshl,b.pihao,b.sxrq,zhgg,a.jlgg as '大包装',huowname,bmjc,d.jwh,fuzr,zdshj ,jixing,a.spbh,a.shengccj,jj,shj,a.dw,0 as shl,
hshjj,a.zjm,b.spid, b.hw,pizhwh,chbdj,shlv,koul,jlgg,ISNULL(jiel,'') as jiel,
case when jiel<>'' then '$003B91'
when jiel='' then '$000000'
else '$000000' end as __fcolor,
case when jiel<>'' then '$FFFFFF'
when jiel='' then '$FFFFFF'
else '$FFFFFF' end as __bcolor
from spkfk a(nolock) inner join sphwph b(nolock) on a.spid=b.spid
inner join huoweizl c(nolock) on b.hw=c.hw
inner join hwsp d(nolock) on d.spid=a.spid and d.hw=c.hw
left join (select spid,hw,pihao,sum(shl) as shl from jxdjmx(nolock) where is_zx='否' and djbh like 'XSG%' group by spid,hw,pihao) e on a.spid=e.spid and b.pihao=e.pihao and b.hw=e.hw and e.hw=c.hw
left join (select spid,pihao,sum(shl) as shl from tmp_dj_xsg212(nolock) group by spid,pihao ) z on a.spid=z.spid and z.pihao=b.pihao
left join (select spid,hw,pihao,sum(shl) as shl from jxddmx(nolock) where is_zx='否' and djbh like 'XSF%' group by spid,hw,pihao) m on a.spid=m.spid and b.pihao=m.pihao and b.hw=m.hw and m.hw=c.hw
left join (select spid,pihao,sum(shl) as shl from tmp_dj_xsf210(nolock) group by spid,pihao ) n on a.spid=n.spid and n.pihao=b.pihao
left join (select spid,pihao,sum(shl) as shl from tmp_dj_xsa220(nolock) group by spid,pihao ) p on a.spid=p.spid and p.pihao=b.pihao
left join gsp302 f on a.spid=f.spid and f.jiel<>''
where a.beactive='是'
and (a.spbh like '%'+@zjm+'%' or a.spmch like '%'+@zjm+'%' or a.zjm like '%'+@zjm+'%' or a.tymzjm like '%'+@zjm+'%')
and huowname<>'缓销库' and b.shl<>0group by a.dbj,a.pfpj,a.tongym,b.shl,e.shl,z.shl,m.shl,n.shl,p.shl,b.pihao,a.hshjj,a.jj,a.hshsj,a.shj,a.spmch,a.shpgg
,a.shpchd,a.hshsj,a.lshj,a.dw,a.jlgg,a.zhgg,c.huowname,c.bmjc,d.jwh,c.fuzr,b.sxrq,a.zdshj,a.jixing,a.spbh,
a.shengccj,a.zjm,b.spid,b.hw,a.pizhwh,d.chbdj,a.shlv,a.koul,a.jlgg,f.jiel
having b.shl-isnull(e.shl,0)-isnull(z.shl,0)-isnull(m.shl,0)-isnull(n.shl,0)-isnull(p.shl,0)<>0
而且同时还有同样大小的其他查询语句在运行,我们的CPU是至强2.4的,请问这样的工作量会不会造成堵塞和死锁。
我们一般的进程在70-100个左右,请各位大哥解答,谢谢
select distinct
left(a.spmch,14) as spmch,a.tongym,left(a.shpgg,15) as shpgg,left(a.shpchd,8) as shpchd,hshsj,hshsj as hshj,a.pfpj,a.dbj,a.lshj,
b.shl-isnull(e.shl,0)-isnull(z.shl,0)-isnull(m.shl,0)-isnull(n.shl,0)-isnull(p.shl,0) as kcshl,b.pihao,b.sxrq,zhgg,a.jlgg as '大包装',huowname,bmjc,d.jwh,fuzr,zdshj ,jixing,a.spbh,a.shengccj,jj,shj,a.dw,0 as shl,
hshjj,a.zjm,b.spid, b.hw,pizhwh,chbdj,shlv,koul,jlgg,ISNULL(jiel,'') as jiel,
case when jiel<>'' then '$003B91'
when jiel='' then '$000000'
else '$000000' end as __fcolor,
case when jiel<>'' then '$FFFFFF'
when jiel='' then '$FFFFFF'
else '$FFFFFF' end as __bcolor
from spkfk a(nolock) inner join sphwph b(nolock) on a.spid=b.spid
inner join huoweizl c(nolock) on b.hw=c.hw
inner join hwsp d(nolock) on d.spid=a.spid and d.hw=c.hw
left join (select spid,hw,pihao,sum(shl) as shl from jxdjmx(nolock) where is_zx='否' and djbh like 'XSG%' group by spid,hw,pihao) e on a.spid=e.spid and b.pihao=e.pihao and b.hw=e.hw and e.hw=c.hw
left join (select spid,pihao,sum(shl) as shl from tmp_dj_xsg212(nolock) group by spid,pihao ) z on a.spid=z.spid and z.pihao=b.pihao
left join (select spid,hw,pihao,sum(shl) as shl from jxddmx(nolock) where is_zx='否' and djbh like 'XSF%' group by spid,hw,pihao) m on a.spid=m.spid and b.pihao=m.pihao and b.hw=m.hw and m.hw=c.hw
left join (select spid,pihao,sum(shl) as shl from tmp_dj_xsf210(nolock) group by spid,pihao ) n on a.spid=n.spid and n.pihao=b.pihao
left join (select spid,pihao,sum(shl) as shl from tmp_dj_xsa220(nolock) group by spid,pihao ) p on a.spid=p.spid and p.pihao=b.pihao
left join gsp302 f on a.spid=f.spid and f.jiel<>''
where a.beactive='是'
and (a.spbh like '%'+@zjm+'%' or a.spmch like '%'+@zjm+'%' or a.zjm like '%'+@zjm+'%' or a.tymzjm like '%'+@zjm+'%')
and huowname<>'缓销库' and b.shl<>0group by a.dbj,a.pfpj,a.tongym,b.shl,e.shl,z.shl,m.shl,n.shl,p.shl,b.pihao,a.hshjj,a.jj,a.hshsj,a.shj,a.spmch,a.shpgg
,a.shpchd,a.hshsj,a.lshj,a.dw,a.jlgg,a.zhgg,c.huowname,c.bmjc,d.jwh,c.fuzr,b.sxrq,a.zdshj,a.jixing,a.spbh,
a.shengccj,a.zjm,b.spid,b.hw,a.pizhwh,d.chbdj,a.shlv,a.koul,a.jlgg,f.jiel
having b.shl-isnull(e.shl,0)-isnull(z.shl,0)-isnull(m.shl,0)-isnull(n.shl,0)-isnull(p.shl,0)<>0
而且同时还有同样大小的其他查询语句在运行,我们的CPU是至强2.4的,请问这样的工作量会不会造成堵塞和死锁。
我们一般的进程在70-100个左右,请各位大哥解答,谢谢
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货