SQL语句执行效率慢，求各位高手帮忙

zyq21859635 2012-10-10 08:08:59
小弟是新手，为了做报表，写了一段SQL语句，结果速度很慢，大约要1分钟，帮忙看看如何才能优化。


语句如下：
select a.ccode,a.ccode_name,b.deptno,b.cDepName,b.大区,sum(b.JinE) JinE from
(select ccode ccode,ccode_name ccode_name from UFDATA_101_2012.dbo.code
where ccode like '5100%' OR ccode like '5101%' OR ccode like '6601%' OR ccode like '6602%') a,
(select x.FeeAccCode,x.deptno,y.cDepName,case when y.cDepProp is null then '其它' else y.cDepProp end 大区,month(x.date) month,sum(x.JinE) JinE FROM
(select T1.PlanNo,case when left(T1.DeptNoU8_Fee,4)='6601' then '6602' when left(T1.DeptNoU8_Fee,4)='6602' then '6601' else left(T1.DeptNoU8_Fee,4) end deptno,T3.shdate date,T2.FeeAccCode,T2.JE JinE
from BSY_ACS_2012..c_YZK_SQ_Bill T1 left join BSY_ACS_2012..c_YZK_SQ_Bill_Detail T2 on T1.planid=T2.planid
inner join (select a.flowid,a.stepid,convert(varchar(12),a.updatetime,23) shdate from BSY_ACS_2012..c_workFlow_Step a where convert(varchar(20),a.updatetime,23)>='2012-10-01' and convert(varchar(20),a.updatetime,23)<='2012-10-31' and year(convert(varchar(20),a.updatetime,23))=2012 and a.stepid=(select max(stepid) from BSY_ACS_2012..c_workFlow_Step where flowid=a.flowid) and a.updatetime is not null) T3 on T1.FlowId=T3.FlowId
where T1.zjCode_FeeZC=T1.zjCode_FeeSR and T1.zjCode_FeeZC is not null and T1.zjCode_FeeSR is not null and T1.FlowStatus=2 and T1.FlowId is not null
and T1.JsTypeID<>106 and T1.DeptNoU8_Fee<>'' and T2.FeeAccCode is not null
union all
select T1.PlanNo,case when left(T1.DeptNoU8_Fee,4)='6601' then '6602' when left(T1.DeptNoU8_Fee,4)='6602' then '6601' else left(T1.DeptNoU8_Fee,4) end deptno,T3.shdate date,T2.FeeAccCode,T2.JE JinE
from BSY_ACS_2012..c_QKD_Bill T1 left join BSY_ACS_2012..c_QKD_Bill_Detail T2 on T1.planid=T2.planid
inner join (select a.flowid,a.stepid,convert(varchar(12),a.updatetime,23) shdate from BSY_ACS_2012..c_workFlow_Step a where convert(varchar(20),a.updatetime,23)>='2012-10-01' and convert(varchar(20),a.updatetime,23)<='2012-10-31' and year(convert(varchar(20),a.updatetime,23))=2012 and a.stepid=(select max(stepid) from BSY_ACS_2012..c_workFlow_Step where flowid=a.flowid) and a.updatetime is not null) T3 on T1.FlowId=T3.FlowId
where T1.zjCode_FeeZC=T1.zjCode_FeeSR and T1.zjCode_FeeZC is not null and T1.zjCode_FeeSR is not null and T1.FlowStatus=2 and T1.FlowId is not null
and T1.JsTypeID<>106 and T1.DeptNoU8_Fee<>'' and T2.FeeAccCode is not null
) x LEFT JOIN UFDATA_101_2012.dbo.department y on x.deptno=y.cdepcode
group by x.deptno,y.cDepName,y.cDepProp,x.FeeAccCode,month(x.date)
) b
where b.FeeAccCode like a.ccode+'%'
group by a.ccode,a.ccode_name,b.deptno,b.cDepName,b.大区
order by a.ccode,a.ccode_name,b.deptno,b.cDepName
...全文
151 13 打赏 收藏 转发到动态 举报
写回复
用AI写文章
13 条回复
切换为时间正序
请发表友善的回复…
发表回复
DBA_磊仔 2012-10-11
  • 打赏
  • 举报
 回复 


创建一个索引

USE BSY_ACS_2012

go

create index idx_updatetime 

on c_workFlow_Step(updatetime)

include(flowid,stepid)

go
zyq21859635 2012-10-11
  • 打赏
  • 举报
 回复
数据库是别人开发的，我主要是取数做个报表，尽量不对原系统进行修改，原有系统设计的有点不太合理。
zyq21859635 2012-10-11
  • 打赏
  • 举报
 回复
参照tanleittl的方法进行了修改后，现在效率有所提升，20秒内就能完成。测试了下速度慢主要在下面这个子查询上，若将时间段去掉，就非常快了，不到1秒。是否能有方法解决?
select a.flowid,a.stepid from BSY_ACS_2012..c_workFlow_Step a where a.updatetime>='2012-10-01' and a.updatetime<='2012-11-01' and a.stepid=(select max(stepid) from BSY_ACS_2012..c_workFlow_Step where flowid=a.flowid)
让你望见影子的墙 2012-10-11
  • 打赏
  • 举报
 回复
1、首先要理情业务逻辑
2、如果有必要的话，需要修改表，增加适当的冗余
3、设计良好的索引
4、注意一下良好sql的书写方法
DBA_磊仔 2012-10-11
  • 打赏
  • 举报
 回复 


--帮你重写了

;WITH 

CET0 AS

(select T1.PlanNo, case when left(T1.DeptNoU8_Fee, 4) = '6601' then '6602' 

																						  when left(T1.DeptNoU8_Fee, 4) = '6602' then '6601' 

																							 else left(T1.DeptNoU8_Fee, 4) end deptno, 

        convert(varchar(12), T3.updatetime, 23) date, T2.FeeAccCode, T2.JE JinE  

	from BSY_ACS_2012..c_YZK_SQ_Bill T1 

        inner join BSY_ACS_2012..c_YZK_SQ_Bill_Detail T2 on T1.planid = T2.planid 

        cross apply 

								(select top(1) a.updatetime from BSY_ACS_2012..c_workFlow_Step a 

         where a.updatetime > = '2012-10-01' and a.updatetime <'2012-11-01'

												and T1.FlowId = a.FlowId order by stepid desc

         ) T3

 where T1.zjCode_FeeZC = T1.zjCode_FeeSR and T1.FlowStatus = 2

			and T1.JsTypeID <> 106 and T1.DeptNoU8_Fee <> '' and T2.FeeAccCode is not null 

						 

union all



	select T1.PlanNo, case when left(T1.DeptNoU8_Fee, 4) = '6601' then '6602' 

																							 when left(T1.DeptNoU8_Fee, 4) = '6602' then '6601' 

                        else left(T1.DeptNoU8_Fee, 4) end deptno, 

								convert(varchar(12), T3.updatetime, 23) date, T2.FeeAccCode, T2.JE JinE  

 from BSY_ACS_2012..c_QKD_Bill T1 

								inner join BSY_ACS_2012..c_QKD_Bill_Detail T2 on T1.planid = T2.planid 

								cross apply 

								(select top(1) a.updatetime from BSY_ACS_2012..c_workFlow_Step a 

									where a.updatetime > = '2012-10-01' and a.updatetime <'2012-11-01'

												and T1.FlowId = a.FlowId order by stepid desc) T3

		where T1.zjCode_FeeZC = T1.zjCode_FeeSR and T1.FlowStatus = 2

						  and T1.JsTypeID <> 106 and T1.DeptNoU8_Fee <> '' and T2.FeeAccCode is not null  

						) 

, CET1 AS

(

select x.FeeAccCode, x.deptno, y.cDepName, 

							case when y.cDepProp is null then '其它' else y.cDepProp end 大区, 

						 month(x.date) month, sum(x.JinE) JinE 

FROM CET0 x LEFT JOIN UFDATA_101_2012.dbo.department y on x.deptno = y.cdepcode

group by x.deptno, y.cDepName, y.cDepProp, x.FeeAccCode, month(x.date)

), 

CET2 AS

(

		select ccode ccode, ccode_name ccode_name 

		from UFDATA_101_2012.dbo.code  

		where ccode like '5100%' OR ccode like '5101%' OR ccode like '6601%' OR ccode like '6602%'

)

select a.ccode, a.ccode_name, b.deptno, b.cDepName, b.大区, sum(b.JinE) JinE 

from CET2 a, CET1 b

where b.FeeAccCode like a.ccode+'%'

group by a.ccode, a.ccode_name, b.deptno, b.cDepName, b.大区

order by a.ccode, a.ccode_name, b.deptno, b.cDepName
zyq21859635 2012-10-11
  • 打赏
  • 举报
 回复
非常感谢tanleittl，创建索引后快多了。
_小黑_ 2012-10-11
  • 打赏
  • 举报
 回复
用存储过程啊
DBA_磊仔 2012-10-10
  • 打赏
  • 举报
 回复 
/*首先表明，你这样嵌套是非常非常要不得的，最起码为了可读性应该改为CET公用表达式

;WITH CET1 AS (),CET2 () 这样

如果数据量大，说不定还要使用临时表，并为临时表创建索引

这一方面我因为没有办法测试，所以就不做了，

这里指出你语法上面的一些性能

*/

select a.ccode,a.ccode_name,b.deptno,b.cDepName,b.大区,sum(b.JinE) JinE 

from 

(select ccode ccode,ccode_name ccode_name 

	  from UFDATA_101_2012.dbo.code  

	where ccode like '5100%' OR ccode like '5101%' OR ccode like '6601%' OR ccode like '6602%') a,

(select x.FeeAccCode,x.deptno,y.cDepName,case when y.cDepProp is null then '其它' else y.cDepProp end 大区,

	month(x.date) month,sum(x.JinE) JinE 

FROM (select T1.PlanNo,case when left(T1.DeptNoU8_Fee,4)='6601' then '6602' 

		when left(T1.DeptNoU8_Fee,4)='6602' then '6601' else left(T1.DeptNoU8_Fee,4) end deptno,

		T3.shdate date,T2.FeeAccCode,T2.JE JinE  

	  from BSY_ACS_2012..c_YZK_SQ_Bill T1 

		--left join BSY_ACS_2012..c_YZK_SQ_Bill_Detail T2 on T1.planid=T2.planid

		inner join BSY_ACS_2012..c_YZK_SQ_Bill_Detail T2 on T1.planid=T2.planid --改为inner 原因下面有说

		inner join (select a.flowid,a.stepid,convert(varchar(12),a.updatetime,23) shdate 

					from BSY_ACS_2012..c_workFlow_Step a 

					where 

					/*该处写法不可以利用索引，会导致表扫描

					convert(varchar(20),a.updatetime,23)>='2012-10-01' 

					and convert(varchar(20),a.updatetime,23)<='2012-10-31'

					等效改为可索引查找的方式 

					*/					

					a.updatetime >='2012-10-01' 

					and a.updatetime <'2012-11-01'

					--and year(convert(varchar(20),a.updatetime,23))=2012  --这一条件完全包含在上面的条件里，所以不需要

					and a.stepid=(select max(stepid) 

									from BSY_ACS_2012..c_workFlow_Step  --这一句要确保表 BSY_ACS_2012..c_workFlow_Step 上有列flowid, stepid的索引

									where flowid=a.flowid) 

					and a.updatetime is not null) T3 on T1.FlowId=T3.FlowId

where T1.zjCode_FeeZC=T1.zjCode_FeeSR and T1.zjCode_FeeZC is not null 

and T1.zjCode_FeeSR is not null and T1.FlowStatus=2 and T1.FlowId is not null

and T1.JsTypeID<>106 and T1.DeptNoU8_Fee<>'' and T2.FeeAccCode is not null --既然T2表要not null 上面就不该用left join 关联T2表

 

union all

select T1.PlanNo,case when left(T1.DeptNoU8_Fee,4)='6601' then '6602' 

					  when left(T1.DeptNoU8_Fee,4)='6602' then '6601' else left(T1.DeptNoU8_Fee,4) end deptno,

	   T3.shdate date,T2.FeeAccCode,T2.JE JinE  

from BSY_ACS_2012..c_QKD_Bill T1 

	--left join BSY_ACS_2012..c_QKD_Bill_Detail T2 on T1.planid=T2.planid

	inner join BSY_ACS_2012..c_QKD_Bill_Detail T2 on T1.planid=T2.planid --该处修改理由同上

	inner join (select a.flowid,a.stepid,convert(varchar(12),a.updatetime,23) shdate 

				from BSY_ACS_2012..c_workFlow_Step a 

				where 

				/* 该处修改理由同上

				convert(varchar(20),a.updatetime,23)>='2012-10-01' and 

				convert(varchar(20),a.updatetime,23)<='2012-10-31' and year(convert(varchar(20),a.updatetime,23))=2012 */

				a.updatetime >='2012-10-01' 

					and a.updatetime <'2012-11-01'

				and a.stepid=(select max(stepid) 

								from BSY_ACS_2012..c_workFlow_Step 

								where flowid=a.flowid) and a.updatetime is not null) T3 on T1.FlowId=T3.FlowId

where T1.zjCode_FeeZC=T1.zjCode_FeeSR and T1.zjCode_FeeZC is not null and T1.zjCode_FeeSR is not null and T1.FlowStatus=2 and T1.FlowId is not null

and T1.JsTypeID<>106 and T1.DeptNoU8_Fee<>'' and T2.FeeAccCode is not null  

) x LEFT JOIN UFDATA_101_2012.dbo.department y on x.deptno=y.cdepcode

group by x.deptno,y.cDepName,y.cDepProp,x.FeeAccCode,month(x.date)

) b

where b.FeeAccCode like a.ccode+'%'

group by a.ccode,a.ccode_name,b.deptno,b.cDepName,b.大区

order by a.ccode,a.ccode_name,b.deptno,b.cDepName
SQL77 2012-10-10
  • 打赏
  • 举报
 回复
改过程吧。。把那些子查询弄成一个一个临时表。

同楼上的看着头晕。
發糞塗牆 2012-10-10
  • 打赏
  • 举报
 回复
1、过多的like和<>
2、where中的列、on中的列你的表有索引吗？
3、where条件的列，顺序应该尽可能按照复合索引（如果有）的顺序来，我看你很多都是is null 在前，=2这些在后，筛选能力有限。
4、像这种：YEAR(CONVERT(VARCHAR(20), a.updatetime, 23)) = 2012用不到索引的，
5、实在看不下去了，有些表没必要过早的子查询。
發糞塗牆 2012-10-10
  • 打赏
  • 举报
 回复
晕，你还跨库的.....瞄了一下也复杂了吧。
發糞塗牆 2012-10-10
  • 打赏
  • 举报
 回复
先占个位置，那么大一坨，不可能快得了
数据库执行SQL语句跟踪
不管你是刚学习软件开发的初学者，还是在IT职场打拼多年的职场老鸟，都免不了要同各种数据库打交道，你可能会碰到以下几个问题：1、感觉自己写的sql语句没有错，但是执行结果总是不对；2、某个功能执行的语句知道有...
SQl语句学习专题(转)
晴天 发表于 2004-10-26 11:16:00 ...SQL语句集锦 --语 句功 能 --数据操作 SELECT--从数据库表中检索数据行和列 INSERT--向数据库表添加新数据行 DELETE--从数据库表中删除数据行 UPDATE...
[分享]SQl语句学习专题
SQL语句集锦--语 句 功 能--数据操作SELECT --从数据库表中检索数据行和列INSERT --向数据库表添加新数据行DELETE --从数据库表中删除数据行UPDATE --更新数据库表中的数据--数据定义CREATE TABLE --创建一个...
基础类 - SQL语句
基础类 - SQL语句 <br />转载请标明出处： http://blog.csdn.net/hz/archive/2006/03/01/613376.asp 本文来自 CSDN 博客。x sql server 怎么跨库查询？ 如何判断字符的大小写 ? 如何...
SQL语句的强大综合集锦
PREPARE --为动态执行准备SQL 语句 EXECUTE --动态地执行SQL 语句 DESCRIBE --描述准备好的查询   ---局部变量 declare @id char(10) --set @id = '10010001' select @id = '...
疑难问题

22,210

社区成员

121,729

社区内容

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

加载中

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

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

+ 用AI写文章