首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

求sql话语优化

2012-08-10 
求sql语句优化SQL codeselect invent.cDepotId,invent.cprodid ,case when in_iQuantity is null then 0 e

求sql语句优化

SQL code
select invent.cDepotId,invent.cprodid ,case when in_iQuantity is null then 0 else in_iQuantity end in_iQuantity ,case when in_iSuCentiare is null then 0 else in_iSuCentiare end in_iSuCentiare ,case when in_iSuAmt is null then 0 else in_iSuAmt end in_iSuAmt ,case when ou_iQuanTity is null then 0 else ou_iQuanTity end ou_iQuanTity ,case when ou_iSuCentiare is null then 0 else ou_iSuCentiare end ou_iSuCentiare from  (select depot.cDepotId,cDepotName,invent.cClassId,cProdid,cProdName,iCentiare,cSpecif,cUnit from invent FULL OUTER JOIN depot on invent.cProdid<>depot.cDepotId )invent left join (select cDepotId,paperlog.cProdId,sum(iQuanTity)in_iQuanTity,sum(iSuCentiare)in_iSuCentiare,sum(iSuAmt)in_iSuAmt   from paperlog,papClass  where paperlog.cPapcType=papClass.cPapcType and paperlog.iyear=2012 and paperlog.imonth=7 and papClass.cPapcName='入库' group by cDepotId,cProdId) inPaperlog on invent.cProdId=inPaperlog.cProdid and invent.cDepotId=inPaperlog.cDepotId left join (select cDepotId,paperlog.cProdId,sum(iQuanTity)ou_iQuanTity,sum(iSuCentiare)ou_iSuCentiare from paperlog,papClass  where paperlog.cPapcType=papClass.cPapcType and paperlog.iyear=2012 and paperlog.imonth=7 and papClass.cPapcName='出库' group by cDepotId,cProdId)ouPaperlog on invent.cProdId=ouPaperlog.cProdId and invent.cDepotId=ouPaperlog.cDepotId  where in_iQuantity<>0 or in_iSuCentiare<>0 or in_iSuAmt<>0 or ou_iQuanTity<>0 or ou_iSuCentiare<>0 order by invent.cprodid,invent.cDepotId

这个语句,执行不了,Sql服务器好像死循环了
如果把
where in_iQuantity<>0 or in_iSuCentiare<>0 or in_iSuAmt<>0 or ou_iQuanTity<>0 or ou_iSuCentiare<>0 
这句给删除了,查询出来的记录也才80000多条
为什么会这样呢?

求师兄师姐给指点一下,谢谢!

[解决办法]
case when in_iQuantity is null then 0 else in_iQuantity 

这样的可以换成ISNULL(in_iQuantity ,0)

另外你的子查询也太多了 都是嵌套。
[解决办法]
建议是使用临时表处理掉一部分子查询
[解决办法]
SQL code
--tryselect invent.cDepotId,invent.cprodid         ,case when in_iQuantity is null then 0 else in_iQuantity end in_iQuantity         ,case when in_iSuCentiare is null then 0 else in_iSuCentiare end in_iSuCentiare         ,case when in_iSuAmt is null then 0 else in_iSuAmt end in_iSuAmt         ,case when ou_iQuanTity is null then 0 else ou_iQuanTity end ou_iQuanTity         ,case when ou_iSuCentiare is null then 0 else ou_iSuCentiare end ou_iSuCentiare from  (    select depot.cDepotId,cDepotName,invent.cClassId,cProdid,cProdName,iCentiare,cSpecif,cUnit     from invent FULL OUTER JOIN depot on invent.cProdid<>depot.cDepotId )invent left join (    select cDepotId,paperlog.cProdId,            sum(case when papClass.cPapcName = '入库'  then iQuanTity else null end) as in_iQuanTity,            sum(case when papClass.cPapcName = '入库'  then iSuCentiare else null end) as iSuCentiare,            sum(case when papClass.cPapcName = '入库'  then iSuAmt else null end) as iSuAmt,            sum(case when papClass.cPapcName = '出库'  then iQuanTity else null end) as ou_iQuanTity,            sum(case when papClass.cPapcName = '出库'  then iSuCentiare else null end) as ou_iSuCentiare    from paperlog,papClass      where paperlog.cPapcType=papClass.cPapcType and paperlog.iyear=2012 and paperlog.imonth=7      group by cDepotId,cProdId) Paperlogon invent.cProdId=Paperlog.cProdid and invent.cDepotId=Paperlog.cDepotId where in_iQuantity<>0 or in_iSuCentiare<>0 or in_iSuAmt<>0 or ou_iQuanTity<>0 or ou_iSuCentiare<>0 order by invent.cprodid,invent.cDepotId
------解决方案--------------------


写的太复杂了,多个表关联,里面还存在group by,这样必然会慢,而且还很多<>

建议做以下修改
第一种方式
1、使用存储过程或sql自定义函数,
2、分拆以上的sql语句,单独三个临时表,并给临时表创建索引
临时表一
select depot.cDepotId,cDepotName,invent.cClassId,cProdid,cProdName,iCentiare,cSpecif,cUnit 
from invent FULL OUTER JOIN depot on invent.cProdid<>depot.cDepotId

临时表二
select cDepotId,paperlog.cProdId,sum(iQuanTity)in_iQuanTity,sum(iSuCentiare)in_iSuCentiare,sum(iSuAmt)in_iSuAmt
from paperlog,papClass
where paperlog.cPapcType=papClass.cPapcType and paperlog.iyear=2012 and paperlog.imonth=7 and papClass.cPapcName='入库' 
group by cDepotId,cProdId) inPaperlog on invent.cProdId=inPaperlog.cProdid and invent.cDepotId=inPaperlog.cDepotId 

临时表三
select cDepotId,paperlog.cProdId,sum(iQuanTity)ou_iQuanTity,sum(iSuCentiare)ou_iSuCentiare 
from paperlog,papClass
where paperlog.cPapcType=papClass.cPapcType and paperlog.iyear=2012 and paperlog.imonth=7 and papClass.cPapcName='出库' 
group by cDepotId,cProdId

然后再进行表关联




[解决办法]

探讨
SQL code

select invent.cDepotId,invent.cprodid
,case when in_iQuantity is null then 0 else in_iQuantity end in_iQuantity
,case when in_iSuCentiare is null then 0 else in_iSuCentiare end in_iSuCent……

热点排行