试图优化一个查询，选择“近似最接近的记录”

Question

我有一张表，其中包含大量的数据，我们特别关注date字段。原因是数据量增加了30倍，旧的方式很快就会崩溃。查询我希望你可以帮我优化需求：

• 采取日期的列表（由CTE基于表值函数生成）
• 检索单个记录为每个日期的
  • 基于的“最接近的”一些定义

例如，当前表包含以5秒的数据（+/-一点）的间隔。我需要对该表格进行采样并获得最接近30秒间隔的记录。

我现在拥有的作品很好。我只是很好奇，如果有更好的方法来优化它。如果我可以在Linq To SQL中使用它，那也会很整洁。鉴于日期值的数量（大约200万行），我甚至对索引建议感兴趣。

declare @st datetime ; set @st = '2012-01-31 05:05:00'; 
declare @end datetime ; set @end = '2012-01-31 05:10:00'; 

select distinct 
    log.* -- id, 
from 
    dbo.fn_GenerateDateSteps(@st, @end, 30) as d 
     inner join lotsOfLogData log on l.Id = (
      select top 1 e.[Id] 
      from 
       lotsOfLogData as log -- contains data in 5 second intervals 
      where 
       log.stationId = 1000 
       -- search for dates in a certain range 
       AND utcTime between DateAdd(s, -10, dt) AND DateAdd(s, 5, dt) 
      order by 
       -- get the 'closest'. this can change a little, but will always 
       -- be based on a difference between the date 
       abs(datediff(s, dt, UtcTime)) 
     ) 
    -- updated the query to be correct. stadionId should be inside the subquery 

lotsOfLogData的表结构如下。站点ID（可能是50）相对较少，但每个站点都有很多记录。当我们查询时，我们知道电台ID。

create table ##lotsOfLogData (
    Id   bigint  identity(1,1) not null 
, StationId int   not null 
, UtcTime  datetime not null 
    -- 20 other fields, used for other calculations 
) 

fn_GenerateDateSteps返回这样的数据集，为参数给出：

[DT] 
2012-01-31 05:05:00.000 
2012-01-31 05:05:30.000 
2012-01-31 05:06:00.000 
2012-01-31 05:06:30.000 (and so on, every 30 seconds) 

我有一个临时表做到了这一点为好，以这种方式，但说出来一点点有点贵。

declare @dates table (dt datetime, ClosestId bigint); 
insert into @dates (dt) select dt from dbo.fn_GenerateDateSteps(@st, @end, 30) 
update @dates set closestId = (-- same subquery as above) 
select * from lotsOfLogData inner join @dates on Id = ClosestId 

编辑：搞掂

了200K +行与现在的工作。我尝试了两种方式，并且使用适当的索引（id/time + includes（..所有列...）工作得很好。然而，我最终使用了一个简单的（和现有的） 。在[ID +时间]指标的更广泛的理解查询是为什么我在一个结算也许还有更好的方式来做到这一点，但我不能看到它：d

-- subtree cost (crossapply) : .0808 
-- subtree cost (id based) : .0797 

-- see above query for what i ended up with 

Answer 1

你可以尝试

• 改变inner join到cross apply。
• 将where log.stationid移动到子选择。

SQL语句

SELECT DISTINCT log.* -- id, 
FROM dbo.fn_GenerateDateSteps(@st, @end, 30) AS d 
     CROSS APPLY (
      SELECT TOP 1 log.* 
      FROM lotsOfLogData AS log -- contains data in 5 second intervals 
      WHERE -- search for dates in a certain range 
        utcTime between DATEADD(s, -10, d.dt) AND DATEADD(s, 5, d.dt) 
        AND log.stationid = 1000 
      ORDER BY 
        -- get the 'closest'. this can change a little, but will always 
        -- be based on a difference between the date 
        ABS(DATEDIFF(s, d.dt, UtcTime)) 
     ) log 

Answer 2

只是一些想法...不会真的叫这个答案，但它太大了评论框

首先，我会看看执行计划的查询，如果你还没有这样做。

更深奥的：你是否可以选择将日期表示为原始值（如自定义好的时间以来代表秒/分钟的整数）？尽管我相信SQL Server将日期作为数值存储在引擎盖下，但对基元的操作可能会稍微快一点，因为它会消除对DateAdd()和DateDiff()的重复调用。

This (fairly old) article举例说明SQL Server如何实际存储日期。也许你可以把日期保留为DATETIME，但用基本的数学运算。

无论数据类型如何，我都会在日期列上尝试使用聚簇索引，因为看起来您的搜索可能受益于聚簇索引提供的物理排序，尤其是在搜索范围较窄的情况下。再次，执行计划可能会很有启发性。

我还可以看到用于表示数据的星型模式，其中包含日期泛化的日期维度。然后你可以反对一般化。即使不使用概括，实际的日期数量也会减少，因为所有具有相同日期的事实都可能指向维度中的同一记录，因此日期只需要评估一次。

最后，SQL性能调优向导（我相信它是在2005年，我知道它是在2008年）对您的查询有什么建议？我不建议盲目地实施它的建议，但我经常在它推荐的东西中找到好主意。