sql-server - 尝试优化选择 'approximate closest record' 的查询

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 + include(..all columns...) 工作得很好。但是，我最终得到了我开始的查询，使用了更简单的(和现有的) [id+time] 上的索引。更广泛可以理解的查询是我选择该查询的原因。也许还有更好的方法来做到这一点，但我看不到它:D

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

-- see above query for what i ended up with

Answer 1

你可以试试

• 将内部联接更改为交叉应用。
• 将 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