求一条SQL语句的优化

msd88 2008-03-23 01:52:49

SQL语句如下，有什么办法效率可以高一点？BillFlow表记录数在20万条以上，UserInfo表记录数在5万条左右



Update tmpRep_Area Set AreaBilled=IsNull(a.AreaBilled,0),AreaNoBill=IsNull(c.AreaNoBill,0),AreaIsCardBilled=d.AreaIsCardBilled,AreaNoCardBilled=e.AreaNoCardBilled

From tmpRep_Area a,

	(Select Bill.AreaCode,Count(Bill.AddrCode) As AreaBilled

		From (Select b.AreaCode,a.AddrCode From BillFlow a

				Left Join UserInfo b On b.AddrCode=a.AddrCode

				Left Join AreaInfo c On c.AreaCode=b.AreaCode

			Where Convert(Char(6),BillDate,112)=@RepDate 

				And DataFlag=0 And b.UserStatus<>'封表' And b.UserStatus<>'注销'

				And a.ID Not In(Select BillFlowID From BillFlowCancel Where Convert(Char(6),CancelDate,112)=@RepDate)

			Group By b.AreaCode,a.AddrCode) Bill

		Group By Bill.AreaCode) b,

	(Select AreaCode,Count(AddrCode) As AreaNoBill From UserInfo

		Where UserStatus<>'封表' And UserStatus<>'注销'

			And Convert(Char(6),CreatDate,112)<=@RepDate

			And AddrCode 

				Not In (Select AddrCode From BillFlow 

					Where Convert(Char(6),BillDate,112)=@RepDate 

					And DataFlag=0

					And id Not In(Select BillFlowID From BillFlowCancel 

						Where Convert(Char(6),CancelDate,112)=@RepDate))

		Group By AreaCode) c,

	(Select AreaCode,Count(AddrCode) As AreaIsCardBilled From UserInfo

		Where UserStatus<>'封表' And UserStatus<>'注销' 

			And Convert(Char(6),CardCreatDate,112)<=@RepDate 

			And IsUseCard=1 And AddrCode

				 In (Select AddrCode From BillFlow 

					Where Convert(Char(6),BillDate,112)=@RepDate 

					And DataFlag=0 

					And id Not In(Select BillFlowID From BillFlowCancel

						Where Convert(Char(6),CancelDate,112)=@RepDate))

		Group By AreaCode) d,

	(Select AreaCode,Count(AddrCode) As AreaNoCardBilled From UserInfo

		Where UserStatus<>'封表' And UserStatus<>'注销'

			And Convert(Char(6),CreatDate,112)<=@RepDate

			And (IsUseCard=0 Or IsUseCard=1 And Convert(Char(6),CardCreatDate,112)>=@RepDate)

			And AddrCode

				In (Select AddrCode From BillFlow 

					Where Convert(Char(6),BillDate,112)=@RepDate 

					And DataFlag=0 

					And id Not In(Select BillFlowID From BillFlowCancel

						Where Convert(Char(6),CancelDate,112)=@RepDate))

		Group By AreaCode) e

Where a.AreaCode=b.AreaCode And c.AreaCode=a.AreaCode And a.AreaCode=d.AreaCode And a.AreaCode=e.AreaCode

...全文

71 点赞收藏 9

写回复

9 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

BILearner 2008-03-24

加索引根本没用,这个是SQL不是ORACLE,在他语句里面根本用不上索引,除了最后几个CODE还能用的上索引,日期根本用不上索引!
你还是试试把重复的语句用临时表代替吧,这样可能会减少几次查询时间!

回复赞

hery2002 2008-03-23

建议拆分开来写,加几个临时表减少数据,这样估计会快些,
只写在一条T-SQL里面,关联太多,本身效率就降低了～

回复赞

nextflying 2008-03-23

加个索引试试覆盖索引？

回复赞

msd88 2008-03-23

语句中有一部份是相同，有没有方法让这个相同的语句只执行一次查询

In (Select AddrCode From BillFlow
Where Convert(Char(6),BillDate,112)=@RepDate
And DataFlag=0
And id Not In(Select BillFlowID From BillFlowCancel
Where Convert(Char(6),CancelDate,112)=@RepDate))

回复赞

msd88 2008-03-23

TO：qianjin036a 是写错了，谢谢！！

回复赞

msd88 2008-03-23

happyflystone 无枪狙击手:复合索引加了，主要是想把语句优化一下

另外还有一个SQL语句，这两天写SQL语句写的我头晕眼花，还望各位给予帮助



CREATE PROCEDURE Rep_AddhsCount

@RepDate Char(6)

 AS



TRUNCATE TABLE tmpRep_Area



Insert Into tmpRep_Area(RepDate,AreaCode,AreaStationID,AreaStationName,AreaCount,AreaDisable,AreaWriteOff,AreaNoDwell,BillerCode,BillerName,CreatCount,AreaIsCard,AreaNoCard)

SELECT @RepDate,a.AreaCode,

	a.AreaStationID,

	d.StationName As AreaStationName,

	COUNT(b.AddrCode) AS AreaCount,

	IsNull(e.AreaDisable,0) As AreaDisable,	--封表户

	IsNull(f.AreaWriteOff,0) As AreaWriteOff,	--注销户

	IsNull(g.AreaNoDwell,0) As AreaNoDwell,	--无人户

	a.AreaBiller,

	c.BillerName,

	IsNull(h.CreatCount,0) As CreatCount,

	IsNull(i.AreaIsCard,0) As AreaIsCard,

	IsNull(j.AreaNoCard,0) As AreaNoCard

FROM AreaInfo a

Left Outer Join UserInfo b On b.AreaCode=a.AreaCode

Left Outer Join BillUser c On c.BillerCode=a.AreaBiller

Left Outer Join StationInfo d ON a.AreaStationID = d.StationID

Left Outer Join (Select AreaCode,Count(addrcode) As AreaDisable From UserInfo Where UserStatus='封表' And Convert(char(6),CreatDate,112)<=@RepDate Group By AreaCode) e On e.AreaCode=a.AreaCode	--封表户

Left Outer Join (Select AreaCode,Count(addrcode) As AreaWriteOff From UserInfo Where UserStatus='注销' And Convert(char(6),CreatDate,112)<=@RepDate Group By AreaCode) f On f.AreaCode=a.AreaCode	--注销户

Left Outer Join (Select AreaCode,Count(addrcode) As AreaNoDwell From UserInfo Where UserStatus='无人' And Convert(char(6),CreatDate,112)<=@RepDate Group By AreaCode) g On f.AreaCode=a.AreaCode	--无人户

Left Outer Join (Select AreaCode,Count(addrcode) As CreatCount From UserInfo Where Convert(Char(6),CreatDate,112)=@RepDate Group By AreaCode) h On h.AreaCode=a.AreaCode	--新建户数

Left Outer Join (Select AreaCode,Count(addrcode) As AreaIsCard From UserInfo Where Convert(Char(6),CardCreatDate,112)<=@RepDate And IsUseCard=1 And UserStatus<>'封表' And UserStatus<>'注销' Group By AreaCode) i On i.AreaCode=a.AreaCode	--用卡户数，不包括封表户和注销户

Left Outer Join (Select AreaCode,Count(addrcode) As AreaNoCard From UserInfo Where Convert(char(6),CreatDate,112)<=@RepDate And IsUseCard=0 And UserStatus<>'封表' And UserStatus<>'注销' Group By AreaCode) j On j.AreaCode=a.AreaCode	--无卡户数，不包括封表户和注销户

Where Convert(char(6),b.CreatDate,112)<=@RepDate

Group By a.AreaCode,a.AreaStationID,d.StationName,e.AreaDisable,f.AreaWriteOff,g.AreaNoDwell,a.AreaBiller,c.BillerName,h.CreatCount,i.AreaIsCard,j.AreaNoCard

回复赞

-晴天 2008-03-23

另外,你语句中的 b 表起什么作用?

Update tmpRep_Area Set AreaBilled=IsNull(a.AreaBilled,0),AreaNoBill

是不是该为

Update tmpRep_Area Set AreaBilled=IsNull(b.AreaBilled,0),AreaNoBill

回复赞

-晴天 2008-03-23

最简单的优化方法,拆成多条语句.

自己比较一下分成四条语句对四个列分别更新所花的时间与整体更新所花的时候哪个更长.

回复赞