SQL Server查询优化? [英] SQL Server query optimization?

问题描述

我有以下查询:

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分钟".
关于优化此查询的任何提示?

...and for my surprise, it does not return the results in an acceptable time. First times I runned it in production environment, was like 3 seconds of execution. Now, it runs until timeout (inside C# application, 30 seconds).
The main purpose is: "give me the most recent locations from any tracker and info about it's users's devices, looking 2 minutes backwards".
Any hints about optimizing this query?

谢谢！

索引: 除where子句列之外的任何地方(ReceivedTime，OSType，Notify).

Indexes: Anywhere but where clause columns (ReceivedTime, OSType, Notify).

关于执行计划，这是一件大事，我不太熟悉.我应该在这里粘贴吗? :)

About the execution plan, it's a huge thing and it's not too familiar to me. Should I paste it here? :)

推荐答案

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

这篇关于SQL Server查询优化?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！