怎么优化更新表的内容半天出不来的语句

Question

如下语句执行12分钟才出来:
      
UPDATE YearEndDates SET AvgDiluteClose = 
(     SELECT   AVG(ISNULL(P.[Close], 0.0) * ISNULL(P.CumulativeDilute, 1.0))
    FROM     PricesDaily P WITH (NOLOCK)
    WHERE    P.ASXCode = YearEndDates.ASXCode
    AND      P.[Date] > ISNULL(YearEndDates.PrevDate,'1900-01-01')
    AND      P.[Date] <= YearEndDates.[Date])  表YearEndDates 有4万条记录,主键和索引是ASXCode, Date ;表PricesDaily 有1600万条记录,主键和索引是ASXCode, Date;有什么方法能优化执行快点吗?因为还有其他的字段要update. update YearEndDates       
 set CumulativeDilute = P.CumulativeDilute,      
     ClosePrice = P.[Close]      
 from YearEndDates Y      
 inner join PricesMonthly P on Y.ASXCode = P.ASXCode 
 and datepart(year, Y.[Date]) = datepart(year, P.[Date])
 and datepart(month, Y.[Date]) = datepart(month, P.[Date])      
表PricesMonthly 有90万条记录,主键和索引是ASXCode, Date;  
语句执行都是很慢的.    
怎么优化更新表的内容半天出不来的语句

Answer 1

试试这个，我觉得单从执行计划来看貌似没啥问题UPDATE  YearEndDates
SET     YearEndDates.avgdiluteclose = AVG(ISNULL(p.[CLOSE], 0.0)
                                          * ISNULL(p.cumulativedilute, 1.0))
FROM    YearEndDates
        INNER JOIN PricesDaily p ON p.ASXCODE = YearEndDates.ASXCODE
WHERE   p.[date] > ISNULL(YearEndDates.prevdate, '1900-01-01')
        AND p.[date] <= YearEndDates.[date]

Answer 2

LZ的系统是SQL2000?
每次都对1600万的表PricesDaily做统计,即使有索引也是没用的..建议差异更新,例如第一次执行算得一个总和与记录笔数,下次再计算时,只需取上次统计时间以来产生的新纪录进行统计,然后[2个总和相加]除以[2个记录数相加],得到平均值.

Answer 3

是的,数据库是SQL2000!
每天都要执行一个存储过程象下面的操作语句:每天都要 truncate table YearEndDates ,
然后插入数据到YearEndDates表, 然后再update 这个表的其他字段:
(PricesDaily这种表1600万的记录,是巨大的) update YearEndDates     
    set SumOfDilute = (select power(10.0000, sum(log10(isnull(DilutionFactor, 1.0)))) 
from DilutionsHistory 
where ASXCode = YearEndDates.ASXCode 
and DateTo > isnull(YearEndDates.PrevDate, '1900-01-01') 
and DateTo < YearEndDates.[Date])      
      
      
  update YearEndDates       
    set CumulativeDilute = P.CumulativeDilute,      
        ClosePrice = P.[Close]      
    from YearEndDates Y      
    inner join PricesMonthly P on Y.ASXCode = P.ASXCode 
and datepart(year, Y.[Date]) = datepart(year, P.[Date])
and datepart(month, Y.[Date]) = datepart(month, P.[Date])      
      
  update YearEndDates      
    set AvgDiluteClose = (select avg(isnull(P.[Close], 0.0)*isnull(P.CumulativeDilute, 1.0)) 
from PricesDaily P where P.ASXCode = YearEndDates.ASXCode 
and P.[Date] > isnull(YearEndDates.PrevDate, '1900-01-01') and P.[Date] <= YearEndDates.[Date])        update YearEndDates      
   set MaxDiluteHigh = (select max(isnull(P.[High], 0.0)*isnull(P.CumulativeDilute, 1.0)) 
   from PricesDaily P where P.ASXCode = YearEndDates.ASXCode 
   and P.[Date] > isnull(YearEndDates.PrevDate, '1900-01-01') and P.[Date] <= YearEndDates.[Date] 
   and P.[High] > 0.0)      
      
      
  update YearEndDates      
    set MinDiluteLow = (select min(isnull(P.[Low], 0.0)*isnull(P.CumulativeDilute, 1.0)) 
from PricesDaily P where P.ASXCode = YearEndDates.ASXCode 
and P.[Date] > isnull(YearEndDates.PrevDate, '1900-01-01') and P.[Date] <= YearEndDates.[Date] 
    and P.[Low] >  0.0)                      
 

Answer 4

insert的过程中实现update逻辑行不行？也就是在select的过程中先把数据整理好再insert

Answer 5

贴出源代码给你看看:  truncate table YearEndDates       
      
  insert into YearEndDates (ASXCode, [Date], TrueBalDate, SortOrder, YearPart, ReportType)      
    select distinct ASXCode,[Date], [Date], 0, 1,      
           case      
             when isnull(Prelim, 'no') = 'no' then 'A'      
             else 'P'      
           end      
    from ASXFinancials            
Update T       
Set T.PrevDate = v.PrevDate,      
    T.SortOrder = v.SortOrder,      
    YearPart = case when v.prevdate is null then 1 else datediff(month, v.PrevDate, t.Date)/12.0 end      
from betest..YearEndDates T ,      
(
   select ASXCode,
   convert(varchar(10),Date,120) as Date,      
   convert(varchar(10),(select max(Date) from YearEndDates where Date<a.Date and ASXCode=a.ASXCode),120) as PrevDate,      
   (select count(*) from YearEndDates 
     where ASXCode=a.ASXCode and Date>=a.Date
    ) as SortOrder      
   from YearEndDates a
) V      
where V.ASXCode=T.ASXCode and V.Date = T.Date  ---------从下面开始执行就很慢----------
  update YearEndDates     
    set SumOfDilute = (select power(10.0000, sum(log10(isnull(DilutionFactor, 1.0)))) 
from DilutionsHistory 
where ASXCode = YearEndDates.ASXCode 
and DateTo > isnull(YearEndDates.PrevDate, '1900-01-01') 
and DateTo < YearEndDates.[Date])      
      
      
  update YearEndDates       
    set CumulativeDilute = P.CumulativeDilute,      
        ClosePrice = P.[Close]      
    from YearEndDates Y      
    inner join PricesMonthly P on Y.ASXCode = P.ASXCode 
and datepart(year, Y.[Date]) = datepart(year, P.[Date])
and datepart(month, Y.[Date]) = datepart(month, P.[Date])      
      
  update YearEndDates      
    set AvgDiluteClose = (select avg(isnull(P.[Close], 0.0)*isnull(P.CumulativeDilute, 1.0)) 
from PricesDaily P where P.ASXCode = YearEndDates.ASXCode 
and P.[Date] > isnull(YearEndDates.PrevDate, '1900-01-01') and P.[Date] <= YearEndDates.[Date])        update YearEndDates      
   set MaxDiluteHigh = (select max(isnull(P.[High], 0.0)*isnull(P.CumulativeDilute, 1.0)) 
   from PricesDaily P where P.ASXCode = YearEndDates.ASXCode 
   and P.[Date] > isnull(YearEndDates.PrevDate, '1900-01-01') and P.[Date] <= YearEndDates.[Date] 
   and P.[High] > 0.0)      
      
      
  update YearEndDates      
    set MinDiluteLow = (select min(isnull(P.[Low], 0.0)*isnull(P.CumulativeDilute, 1.0)) 
from PricesDaily P where P.ASXCode = YearEndDates.ASXCode 
and P.[Date] > isnull(YearEndDates.PrevDate, '1900-01-01') and P.[Date] <= YearEndDates.[Date] 
    and P.[Low] >  0.0)                      

Answer 6

你的这个：and datepart(year, Y.[Date]) = datepart(year, P.[Date])
 and datepart(month, Y.[Date]) = datepart(month, P.[Date])      会导致，你建的索引没什么用吧。另外，建议你把上面的代码，修改成这样，试试把inner join 改为inner hash join试试：UPDATE  YearEndDates
SET     YearEndDates.avgdiluteclose = AVG(ISNULL(p.[CLOSE], 0.0)
                                          * ISNULL(p.cumulativedilute, 1.0))
FROM    YearEndDates
INNER hash JOIN PricesDaily p ON p.ASXCODE = YearEndDates.ASXCODE
WHERE   p.[date] > ISNULL(YearEndDates.prevdate, '1900-01-01')
        AND p.[date] <= YearEndDates.[date]

Answer 7

如果可以的话，把你的慢的代码都改为通过inner hash join来实现，少用子查询。刚才上面那句先试一下，看能不能用。

Answer 8

加了哈希连接 ,速度更加慢!哦，不会吧，你就单个先运行一个语句试试，我记得昨天有个帖子，也是很慢，后来加了hash就飞快了

Answer 9

之前也遇到过类似的问题，只不过，我那个语句是select语句，不是update语句。一大段，几千行的代码，你可以先试试看到底是那个语句是最慢的，一个一个定位，然后解决。