performance - 查询多个表并合并结果的有效方法

Question

我正在运行查询以从大约 30 个表中提取时间序列数据。我正在对数据进行下采样以减少数据点的数量和查询时间。每个表包含数百万行和十几列。我只对几列感兴趣，我计算一个移动平均值并将下采样到 1 小时增量。但是我看到，当我添加表时，开始有大量时间来运行查询。这是我希望能够经常运行以更新报告的东西，所以我需要它运行得更快。

我尝试的是嵌套派生表和“交叉应用”函数来聚合表中的结果。

有没有更好的方法来减少查询时间？

目前从这 3 个表中返回 735 行（15 列）。

Select * from
(select
        min([theDate]) as theDate, 
        min([theTime]) as theTime, 
        max([Value]) as maxValue, 
        max([rolling_avg]) as maxDM,
        timeHour as timeHour

from( select [theDate], [theTime], [Value],
       avg(windowAvg) over(order by theDate DESC, theTime rows between 90 preceding and current row) as rolling_avg,
       datepart(hh,theTime) as timeHour
  from (select [theDate], [theTime], [Value], sum([Value]) as windowAvg
          from [Data].[dbo].[tOne]
          Where ([theDate] > convert(DAte,DATEADD(month, -1, GETDATE())))
          group by theDate, theTime, Value 

          )tOneTemp   
          )tOneTempTwo
          group by theDate, timeHour) tOne 
Cross Apply (select
        min([theDate]) as theDate, 
        min([theTime]) as theTime, 
        max([Value]) as maxValue, 
        max([rolling_avg]) as maxDM,
        timeHour as timeHour

from( select [theDate], [theTime], [Value],
       avg(windowAvg) over(order by theDate DESC, theTime rows between 90 preceding and current row) as rolling_avg,
       datepart(hh,theTime) as timeHour
  from (select [theDate], [theTime], [Value], sum([Value]) as windowAvg
          from [Data].[dbo].[tTwo]
          Where ([theDate] > convert(DAte,DATEADD(month, -1, GETDATE())))
          group by theDate, theTime, Value 

          )tTwoTemp   
          )tTwoTempTwo
          group by theDate, timeHour) tTwo

Cross Apply (select
        min([theDate]) as theDate, 
        min([theTime]) as theTime, 
        max([Value]) as maxValue, 
        max([rolling_avg]) as maxDM,
        timeHour as timeHour

from( select [theDate], [theTime], [Value],
       avg(windowAvg) over(order by theDate DESC, theTime rows between 90 preceding and current row) as rolling_avg,
       datepart(hh,theTime) as timeHour
  from (select [theDate], [theTime], [Value], sum([Value]) as windowAvg
          from [Data].[dbo].[tThree]
          Where ([theDate] > convert(DAte,DATEADD(month, -1, GETDATE())))
          group by theDate, theTime, Value 

          )tThreeTemp   
          )tThreeTempTwo
          group by theDate, timeHour) tThree

where tOne.timeHour = tTwo.timeHour and tOne.theDate = tTwo.theDate and tOne.timeHour = tThree.timeHour and tOne.theDate = tThree.theDate
 order by tOne.theDate DESC, tOne.theTime DESC  

标签： performancetsqlmultiple-tablescross-apply