求sql语句优化

2012-07-23 02:21:49 136 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

Sql语句的优化以及Sql语句优化工具这周在做项目的时候，发现有些Sql语句写的复杂，一个复杂的Sql语句不仅性能比较低下，当涉及到重构的时候也是不容易理解和读懂的，并且一个复杂的sql语句会显著增加程序的耦合度，所以Sql语句的优化就显的很必要了。 Sql语句复杂化的原因首先列举一下Sql语句复杂化的原因: 将业务逻辑由Sql语句实现数据库表的设计不符合第三范式 Sql语句中含有大量冗余的不必要逻辑没有很好的利用视图如何优化Sql语句呢不要超过5个表以上的连接超过5张表以上的连接会大大增加sql引擎的消耗。考虑使用临阅读详情



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多条
为什么会这样呢？

求师兄师姐给指点一下，谢谢！

...全文

AI Agent评估：指标、策略与最佳实践 AI智能体正逐渐融入到各个领域，从日常的对话助手到复杂的工作流程自动化，其应用范围不断拓展。然而，确保这些智能体能够可靠、高效地完成任务至关重要，这就使得AI智能体评估成为了人工智能发展过程中不可或缺的一环。本文将深入探讨AI智能体评估的指标、策略以及最佳实践，为读者全面解读这一关键领域。阅读详情

水桶IIII

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

白天的猫头鹰 2012-07-23

打赏
举报

[Quote=引用楼主的回复:]
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……
[/Quote]

在联表查询时候，进行group by效率上肯定是很低的，还不如单独外面先进行group by 再进行联表查询操作

另外这里也可以使用CTE 操作，这样就避免使用临时表

白天的猫头鹰 2012-07-23

打赏
举报

写的太复杂了，多个表关联，里面还存在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

然后再进行表关联

天-笑 2012-07-23

打赏
举报



--try2

select invent.cDepotId,invent.cprodid 

		,isnull(in_iQuantity,0) as in_iQuantity

		,isnull(in_iSuCentiare,0) as in_iSuCentiare

		,isnull(in_iSuAmt,0) as in_iSuAmt

		,isnull(ou_iQuanTity,0) as ou_iQuanTity

		,isnull(ou_iSuCentiare,0) as 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

) Paperlog

on 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

天-笑 2012-07-23

打赏
举报





--try

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(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

) Paperlog

on 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

开着拖拉机泡妞 2012-07-23

打赏
举报

建议是使用临时表处理掉一部分子查询

--小F-- 2012-07-23

打赏
举报

case when in_iQuantity is null then 0 else in_iQuantity

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

另外你的子查询也太多了都是嵌套。

求sql语句优化

相关推荐