SQL Server查询优化？

Question

我有以下查询：

SELECT
  tl.*, d.*
FROM
  TrackerLocations AS tl
  inner join Trackers t on tl.TrackerId = t.TrackerId
  inner join Devices d on t.UserId = d.UserId
WHERE
  tl.ReceivedTime = (SELECT MAX(tl2.ReceivedTime) FROM TrackerLocations tl2 WHERE tl2.TrackerId = tl.TrackerId)
  and tl.ReceivedTime >= DATEADD (MINUTE,-2,GETUTCDATE())
  and d.OSType <> 3
  and d.Notify = 1

并且出乎意料的是，它不能在可接受的时间内返回结果。第一次在生产环境中运行时，执行时间约为3秒，现在已运行到超时（在C#应用程序内，30秒）。 主要目的是：“给我任何跟踪器的最新位置和有关用户设备的信息，并向后查看2分钟”。 有没有优化此查询的任何提示？

谢谢！

索引： 除了where子句列（ReceivedTime、OSType、Notify）以外的任何地方。

关于执行计划，这是一个很大的东西，对我来说并不太熟悉。我应该在这里粘贴它吗？ :)

- Cesar

---

使用公共表达式来限制被连接的行数。 - Charles380

你有哪些指标？执行计划是什么样子的？ - Martin Smith

Answer 1

SELECT tl.*,d.*
 FROM
    TrackerLocations AS tl
    inner join Trackers t on tl.TrackerId = t.TrackerId
    inner join Devices d on t.UserId = d.UserId
 WHERE
    tl.ReceivedTime = (SELECT MAX(tl2.ReceivedTime) 
                         FROM TrackerLocations tl2 
                        WHERE tl2.TrackerId = tl.TrackerId 
                          and tl2.ReceivedTime >= @searchTerm)
    and d.RegistrationId <> ''
    and d.OSType <> 3
    and d.Notify = 1

- Ahmed Alejo

---

太好了！使用ReceivedTime >= ...限制“select max”子句似乎可以让这个内部查询变得不那么痛苦。谢谢！ - Cesar

Answer 2

我建议在 (TrackerId, ReceivedTime) 和可能的 ReceivedTime 上尝试索引。但是，如果没有分析和实际执行计划，这只是一次瞎猜。

- Michael Goldshteyn

Answer 3

因为我不知道这个查询的执行计划是什么样子的，所以我只能根据自己的感觉来帮助加快结果的速度。

1. 你是否有主键和外键约束
2. 这些键是否被索引了

除此之外，我会尝试使用CTE来限制被连接在一起的结果，类似于(未经测试):

;WITH cte_loc AS
(
    SELECT
        tl.*
    FROM
        TrackerLocations tl            
    WHERE
        tl.ReceivedTime >= DATEADD (MINUTE,-2,GETUTCDATE())
),cte_loc2 AS
(
    SELECT
        TrackerId
        ,MAX(ReceivedTime) AS MaxReceivedTime
    FROM
        cte_loc
    GROUP BY
        TrackerId
),cte_dev AS
(
    SELECT
        tl.TrackerId,
        d.*
    FROM
        cte_loc2 tl 
            INNER JOIN Trackers t ON tl.TrackerId = t.TrackerId
            INNER JOIN Devices d ON t.UserId = d.UserId             
    WHERE
        d.OSType <> 3 AND d.Notify = 1
)

SELECT
    tl.*, d.*
FROM
    cte_loc AS tl      
        INNER JOIN cte_loc2 tl2 ON tl.TrackerId = tl2.TrackerId
                               AND tl.ReceivedTime = tl2.MaxReceivedTime
        LEFT JOIN cte_dev d ON tl.TrackerId = d.TrackerId

- Charles380

Answer 4

在TrackerLocations.TrackerId和Trackers.TrackerId上创建索引。此外，对于第二个JOIN，还需对Devices.UserId和Trackers.UserId进行索引。最后，还需要查看在WHERE子句中为TrackerLocations.ReceivedTime创建索引。

通常来说，优化这种查询的第一步是检查是否在连接条件上有索引。此外，如果有某种过滤器（WHERE子句），那么可以在那里添加索引。

很难准确地说您需要在哪里进行优化，而不查看查询执行计划。许多因素会影响它，包括每个表中有多少记录。不要过度创建索引，因为它们会影响性能，例如插入新数据时。最好的建议就是尝试使用不同的索引，并查看执行计划是否给出了一些提示。

- JohnD

---

谢谢John和Michael，我稍后会尝试首先在ReceivedTime上建立索引，并在此发布结果！ - Cesar

Answer 5

由于您正在寻找过去2分钟内最新的ReceivedTime，因此您需要按照降序在ReceivedTime上创建索引。 降序部分很重要。 这也有助于MAX函数。 您还可以尝试在(ReceivedTime DESC，TrackerID)上创建索引。

- David Nhim