求一条SQL语句的优化

[msd88]

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

[-狙击手-]

太长，加复合索引先看看效果

[-晴天]

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

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

[-晴天]

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

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

是不是该为

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

[msd88]

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

[msd88]

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

[msd88]

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

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

[nextflying]

加个索引试试 覆盖索引？

[hery2002]

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

[BILearner]

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