sql-server - 在大型数据集中查找给定查找日期的最后一次已知事件

Question

我有一个特定的设计问题已经困扰了我很长一段时间。我有一个大型实时 GPS 位置日志表，其中包含数千个 Assets 的点条目。每项 Assets 每天有数百个点进入数据库。我有一个索引 IX(asset asc,EventTime asc) 来加速对目标 Assets 的点查询。我有一个 LastKnownLocation 表，用于将每项 Assets 与其最近的点相关联。这只为我提供了 CURRENT 最后已知的查找。我的问题是，有没有人知道一种有效的方法，可以在一次给定许多 Assets 的特定查找日期的情况下查询位置日志表以获取最后已知位置？“问:2012 年 7 月 1 日结束时我的所有 Assets 在哪里”

顺便说一句，由于每个 Assets 都使用自己的内部单调事件时间戳报告其点，因此与每个 Assets 相关的表的 LocationLog.LocationLogID auto inc 主键存在隐含的单调关系。这就是我可以使用 MAX 聚合的原因。

SELECT MAX(LocationLog.LocationLogID) FROM LocationLog 
WHERE LocationLog.fk_AssetID IN 
(
    //LIST OF required assets for report
) 
AND LocationLog.EventTime <= '2012/07/01 23:59:59'
GROUP BY LocationLog.fk_AssetID

问题是数据库索引 IX 可以快速访问单个 Assets 的所有点。然后这些点在索引中按事件时间排序，因此 dbengine 可能会在索引中的事件时间内进行数据扫描，以查找日期 <= 查找日期的最大 LocationLogID。 Assets 在查找日期之前报告的时间越长，查找匹配项的扫描时间就越长。

因为我的位置日志有 90 多万行，并且像这样增加 1000 项 Assets 查询需要 50 秒。

在给定查找日期的情况下查找最后一次已知的事件一定是众所周知的设计模式，但它避开了我的搜索。

PS:正在运行 MSSQL2000，但正在迁移到 Postgres

Answer 1

这可能有助于消除 IN(...)。

尝试类似的东西

--
--  index on AssetID, EventTime desc
--
select
      AssetID
    , LocationID
    , EventTime
from LocationLog as a
join (
        select AssetID_1
        union
        select AssetID_2
        union
        select AssetID_3
        -- etc, list of assets needed in report
      ) as b on b.AssetID = a.AssetID
where a.EventTime = (select max(xx.EventTime) 
                       from LocationLog as xx 
                      where xx.AssetID = a.AssetID
                        and xx.EventTime <= '2012-08-28 12:05:00')
;