sql优化，新员工，现在要优化一个项目的sql，就这点分了， 全给了，只希望有满意结果。

li_shiwei 2014-04-01 04:04:41 


SELECT     FlyPM_Contract.CT_ID, FlyPM_Contract.CT_ConCode, FlyPM_Contract.CT_ConName, CommonParameter.CP_Name AS CT_ConStatus, 

                      CommonParameter_1.CP_Name AS CT_ConType, FlyPM_Contract.CT_ConMoney, FlyPM_Customer.sName AS CT_ConFirstSide, FlyPM_Contract.CT_ConFirstLinkMan, 

                      FlyPM_Contract.CT_ConFirstLinkTel, view_Department.PD_Name AS CT_ConMainDept, 

                      CommonParameter_2.CP_Name AS CT_ConClassify, FlyPM_Contract.CT_ConInvest, FlyPM_Contract.CT_ConScale, FlyPM_Contract.CT_ConPrjAddress, 

                      dbo.GetPhaseNameListCon(FlyPM_Contract.CT_ID) AS CT_ConPhase, FlyPM_Contract.CT_ConSignDate, dbo.GetConCreaterNameListCon(FlyPM_Contract.CT_ID) AS CT_ConCreater, 

                      FlyPM_Contract.CT_ConCreateDate, FlyPM_Contract.CT_ConFeeInfo, FlyPM_Contract.CT_ConSummary, FlyPM_Contract.CT_ConRemark, FlyPM_Contract.bFlag, 

                      FlyPM_Contract.sAttachID, ISNULL(GathingSumTotal.GathingTotal, 0) AS GathingTotal, ISNULL(GathingSumTotal.GathingTotal, 0) 

                      - ISNULL(CurYearGathintTal.GathingMoneyCurYear, 0) AS GathingMoneyHistoryYear, ISNULL(CurYearGathintTal.GathingMoneyCurYear, 0) AS GathingMoneyCurYear, 

                      FlyPM_Contract.CT_ConMoney - ISNULL(GathingSumTotal.GathingTotal, 0) AS ShengYu, 

                      FlyPM_Contract.CT_ConStartDate, FlyPM_Contract.CT_ConDemandDate, FlyPM_Contract.CT_ConFactDate, 

                      CASE WHEN (FlyPM_Contract.CT_ConMoney - ISNULL(GathingSumTotal.GathingTotal, 0)) 

                      > 0 THEN '<font color=red>合同欠款</font>' WHEN (FlyPM_Contract.CT_ConMoney - ISNULL(GathingSumTotal.GathingTotal, 0)) 

                      < 0 THEN '<font color=cyan>合同多收</font>' ELSE '<font color=green>收款完结</font>' END AS notice, view_AllUser1.PU_Name AS CT_ConPreson, 

                      dbo.GetCoDeptNameListCon(FlyPM_Contract.CT_ID) AS CT_ConCoDept, FlyPM_Contract.CT_ConClassifyBenYuan, CASE WHEN bIsStarted IS NULL 

                      THEN '<font color=red>未立项  </font><img border=0 align=absmiddle src=/App_Resource/Images/Icons/exclamation_octagon_fram.png>' ELSE '<font color=green>已立项  </font><img border=0 align=absmiddle src=/App_Resource/Images/Icons/drop-yes.gif>'

                       END AS Status , case when (dbo.Fn_PV_isRelationToPerson(FlyPM_Contract.CT_ID) = 0) then '<font color=green>单承接人</font>'

                       when (dbo.Fn_PV_isRelationToPerson(FlyPM_Contract.CT_ID) = 1) then '<font color=green>已分解</font>'

                       when (dbo.Fn_PV_isRelationToPerson(FlyPM_Contract.CT_ID) = 2) then '<font color=red>多人未分解</font>' end AS isRelation

FROM         FlyPM_Contract LEFT OUTER JOIN

                          (SELECT     SUM(CG_GathingMoney) AS GathingMoneyCurYear, CT_ID

                            FROM          FlyPM_ContractGathering AS FlyPM_ContractGathering_1

                            GROUP BY CT_ID, YEAR(CG_GathingDate)

                            HAVING      (YEAR(CG_GathingDate) = YEAR(GETDATE()))) AS CurYearGathintTal ON FlyPM_Contract.CT_ID = CurYearGathintTal.CT_ID  LEFT OUTER JOIN

                          (SELECT     SUM(CG_GathingMoney) AS GathingTotal, CT_ID

                            FROM          FlyPM_ContractGathering

                            GROUP BY CT_ID) AS GathingSumTotal ON FlyPM_Contract.CT_ID = GathingSumTotal.CT_ID LEFT OUTER JOIN

                      view_AllUser ON FlyPM_Contract.CT_ConCreater = view_AllUser.PU_ID LEFT OUTER JOIN

                      FlyPM_Customer ON FlyPM_Contract.CT_ConFirstSide = FlyPM_Customer.CM_ID LEFT OUTER JOIN

                      CommonParameter AS CommonParameter_2 ON FlyPM_Contract.CT_ConClassify = CommonParameter_2.CP_ID LEFT OUTER JOIN

                      view_Department ON FlyPM_Contract.CT_ConMainDept = view_Department.PD_ID LEFT OUTER JOIN

                      CommonParameter AS CommonParameter_1 ON FlyPM_Contract.CT_ConType = CommonParameter_1.CP_ID LEFT OUTER JOIN

                      CommonParameter ON FlyPM_Contract.CT_ConStatus = CommonParameter.CP_ID LEFT OUTER JOIN

                      view_Department AS view_Department1 ON FlyPM_Contract.CT_ConCoDept = view_Department1.PD_ID LEFT OUTER JOIN

                      view_AllUser AS view_AllUser1 ON FlyPM_Contract.CT_ConPreson = view_AllUser1.PU_ID

WHERE     (FlyPM_Contract.bFlag = '0')
...全文
205 14 打赏 收藏 转发到动态 举报
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
發糞塗牆 2014-04-01
  • 打赏
  • 举报
 回复
瞄了一下打开过的执行计划，大量hash join，一般两种情况，1是的确有大量数据需要查询，按照你的说法是加载，那就要考虑是否真的需要一次性加载那么多？能否分页。2.where条件不充足，join中的关联列没有索引等
發糞塗牆 2014-04-01
  • 打赏
  • 举报
 回复
断网了……爱莫能助……
software_artisan 2014-04-01
  • 打赏
  • 举报
 回复 
SELECT
FlyPM_Contract.CT_ID,
FlyPM_Contract.CT_ConCode,
FlyPM_Contract.CT_ConName,
CommonParameter.CP_Name AS CT_ConStatus, 
CommonParameter_1.CP_Name AS CT_ConType,
FlyPM_Contract.CT_ConMoney,
FlyPM_Customer.sName AS CT_ConFirstSide,
FlyPM_Contract.CT_ConFirstLinkMan,
FlyPM_Contract.CT_ConFirstLinkTel,
view_Department.PD_Name AS CT_ConMainDept,
CommonParameter_2.CP_Name AS CT_ConClassify,
FlyPM_Contract.CT_ConInvest,
FlyPM_Contract.CT_ConScale,
FlyPM_Contract.CT_ConPrjAddress, 
dbo.GetPhaseNameListCon(FlyPM_Contract.CT_ID) AS CT_ConPhase,
FlyPM_Contract.CT_ConSignDate,
dbo.GetConCreaterNameListCon(FlyPM_Contract.CT_ID) AS CT_ConCreater, 
FlyPM_Contract.CT_ConCreateDate,
FlyPM_Contract.CT_ConFeeInfo,
FlyPM_Contract.CT_ConSummary,
FlyPM_Contract.CT_ConRemark,
FlyPM_Contract.bFlag,
FlyPM_Contract.sAttachID,
ISNULL(GathingSumTotal.GathingTotal, 0) AS GathingTotal,
ISNULL(GathingSumTotal.GathingTotal, 0) - ISNULL(CurYearGathintTal.GathingMoneyCurYear, 0) AS GathingMoneyHistoryYear,
ISNULL(CurYearGathintTal.GathingMoneyCurYear, 0) AS GathingMoneyCurYear,
FlyPM_Contract.CT_ConMoney - ISNULL(GathingSumTotal.GathingTotal, 0) AS ShengYu,
FlyPM_Contract.CT_ConStartDate,
FlyPM_Contract.CT_ConDemandDate,
FlyPM_Contract.CT_ConFactDate,
CASE WHEN (FlyPM_Contract.CT_ConMoney - ISNULL(GathingSumTotal.GathingTotal, 0)) > 0 THEN '<font color=red>合同欠款</font>'
     WHEN (FlyPM_Contract.CT_ConMoney - ISNULL(GathingSumTotal.GathingTotal, 0)) < 0 THEN '<font color=cyan>合同多收</font>'
     ELSE '<font color=green>收款完结</font>'
     END AS notice,
view_AllUser1.PU_Name AS CT_ConPreson,
dbo.GetCoDeptNameListCon(FlyPM_Contract.CT_ID) AS CT_ConCoDept,
FlyPM_Contract.CT_ConClassifyBenYuan,
CASE WHEN bIsStarted IS NULL THEN '<font color=red>未立项  </font><img border=0 align=absmiddle src=/App_Resource/Images/Icons/exclamation_octagon_fram.png>'
     ELSE '<font color=green>已立项  </font><img border=0 align=absmiddle src=/App_Resource/Images/Icons/drop-yes.gif>'
     END AS Status,
case when (dbo.Fn_PV_isRelationToPerson(FlyPM_Contract.CT_ID) = 0) then '<font color=green>单承接人</font>'
     when (dbo.Fn_PV_isRelationToPerson(FlyPM_Contract.CT_ID) = 1) then '<font color=green>已分解</font>'
     when (dbo.Fn_PV_isRelationToPerson(FlyPM_Contract.CT_ID) = 2) then '<font color=red>多人未分解</font>'
     end AS isRelation
FROM FlyPM_Contract
  LEFT OUTER JOIN(
    SELECT SUM(CG_GathingMoney) AS GathingMoneyCurYear, CT_ID
    FROM FlyPM_ContractGathering AS FlyPM_ContractGathering_1
    GROUP BY CT_ID, YEAR(CG_GathingDate)
    HAVING (YEAR(CG_GathingDate) = YEAR(GETDATE()))) AS CurYearGathintTal ON FlyPM_Contract.CT_ID = CurYearGathintTal.CT_ID
  LEFT OUTER JOIN(
    SELECT SUM(CG_GathingMoney) AS GathingTotal, CT_ID
    FROM FlyPM_ContractGathering
    GROUP BY CT_ID) AS GathingSumTotal ON FlyPM_Contract.CT_ID = GathingSumTotal.CT_ID
  LEFT OUTER JOIN view_AllUser ON FlyPM_Contract.CT_ConCreater = view_AllUser.PU_ID
  LEFT OUTER JOIN FlyPM_Customer ON FlyPM_Contract.CT_ConFirstSide = FlyPM_Customer.CM_ID
  LEFT OUTER JOIN CommonParameter AS CommonParameter_2 ON FlyPM_Contract.CT_ConClassify = CommonParameter_2.CP_ID
  LEFT OUTER JOIN view_Department ON FlyPM_Contract.CT_ConMainDept = view_Department.PD_ID
  LEFT OUTER JOIN CommonParameter AS CommonParameter_1 ON FlyPM_Contract.CT_ConType = CommonParameter_1.CP_ID
  LEFT OUTER JOIN CommonParameter ON FlyPM_Contract.CT_ConStatus = CommonParameter.CP_ID
  LEFT OUTER JOIN view_Department AS view_Department1 ON FlyPM_Contract.CT_ConCoDept = view_Department1.PD_ID
  LEFT OUTER JOIN view_AllUser AS view_AllUser1 ON FlyPM_Contract.CT_ConPreson = view_AllUser1.PU_ID
WHERE (FlyPM_Contract.bFlag = '0')
一堆的左连接。。。。。。能改成内连接么？
li_shiwei 2014-04-01
  • 打赏
  • 举报
 回复
引用 10 楼 DBA_Huangzj 的回复:
数据量很大吗？
数据是挺多的。加载页面时很慢，所以才让我优化。可是，唉，sql功夫不行啊。
發糞塗牆 2014-04-01
  • 打赏
  • 举报
 回复
数据量很大吗？
li_shiwei 2014-04-01
  • 打赏
  • 举报
 回复
引用 8 楼 u011212968 的回复:
引用 7 楼 DBA_Huangzj 的回复:
点一下红框部分再执行你的代码，图在底部

li_shiwei 2014-04-01
  • 打赏
  • 举报
 回复
引用 7 楼 DBA_Huangzj 的回复:
点一下红框部分再执行你的代码，图在底部
發糞塗牆 2014-04-01
  • 打赏
  • 举报
 回复
点一下红框部分再执行你的代码，图在底部
houyajin 2014-04-01
  • 打赏
  • 举报
 回复
他是要执行计划，，另外你这个表不能加个易读一点的表名么？
li_shiwei 2014-04-01
  • 打赏
  • 举报
 回复
引用 4 楼 DBA_Huangzj 的回复:
不是，把你的代码贴到查询窗口，然后按一下ctrl+m，然后再执行你的代码
我照你说的做了但是没有出来图啊。
發糞塗牆 2014-04-01
  • 打赏
  • 举报
 回复
不是，把你的代码贴到查询窗口，然后按一下ctrl+m，然后再执行你的代码
li_shiwei 2014-04-01
  • 打赏
  • 举报
 回复
引用 1 楼 DBA_Huangzj 的回复:
这。。。。ctrl+M，然后运行你这段代码，把生成的图贴上来，如果太复杂的话，找出百分比最高的那部分贴出来


是这个图吗？
--小F-- 2014-04-01
  • 打赏
  • 举报
 回复
我了个去 这代码。。
發糞塗牆 2014-04-01
  • 打赏
  • 举报
 回复
这。。。。ctrl+M，然后运行你这段代码，把生成的图贴上来，如果太复杂的话，找出百分比最高的那部分贴出来
MySQL版SQL优化
本课程通过Centos版的MySQL讲解了SQL优化的一些常见手段。其中讲解了MySQL的分层、存储引擎等底层逻辑，并讲解了常见的索引优化手段。在讲解时，先通过理论对先关的优化知识进行了铺垫，然后使用实际的案例详细的演示了每一次的优化操作。并且在课程的最后，还讲解了如何使用MySQL实现主从同步功能。本课程大致包含了以下几方面的内容：1.MySQL底层逻辑2.常见的优化手段3.常见的优化原则4.优化工具的使用5.主从同步相信通过本课程的学习，大家可以对MySQL的性能优化有一定的理解和认识。为后续高性能系统的开发打下良好的基础和准备。
ORACLE SQL性能优化系列
ORACLE SQL性能优化系列 1.  访问Table的方式 ORACLE 采用两种访问表中记录的方式: a.  全表扫描 全表扫描就是顺序地访问表中每条记录. ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描. b.  通过ROWID访问表 你可以采用基于ROWID的访问方式情况,提高访问表的效率,  ROWID包含了表中记录的物理位置信
ORACLE SQL语句优化技术分析
ORACLE SQL语句优化技术分析 一、问题的提出 在应用系统开发初期，由于开发数据库数据比较少，对于查询SQL语句，复杂视图的的编写等体会不出SQL语句各种写法的性能优劣，但是如果将应用系统提交实际应用后，随着数据库中数据的增加，系统的响应速度就成为目前系统需要解决的最主要的问题之一。系统优化中一个很重要的方面就是SQL语句的优化。对于海量数据，劣质SQL语句和优质SQL语句之间的...
ORACLE+SQL性能优化
1. 访问Table的方式 ORACLE 采用两种访问表中记录的方式: a. 全表扫描 全表扫描就是顺序地访问表中每条记录. ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描. b. 通过ROWID访问表 你可以采用基于ROWID的访问方式情况,提高访问表的效率, , ROWID包含了表中记录的物理位置信息..ORACLE采用索引(INDEX
ORACLE SQL语句优化技术
SQL 语句编写注意问题 下面就某些 SQL 语句的 where 子句编写中需要注意的问题作详细介绍。在这些 where子句中，即使某些列 存在索引，但是由于编写了劣质的 SQL，系统在运行该 SQL 语句时也不能使用该索引，而同样使用全表扫 描，这就造成了响应速度的极大降低。 IS NULL 或IS NOT NULL操作（判断字段是否为空）不能用 null作索引，任何包含 null值的列
疑难问题

22,209

社区成员

121,731

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章