我有以下查询:
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 分钟”。
有关优化此查询的任何提示吗?
Thanks!
Indexes:Anywhere but where 子句列(ReceivedTime、OSType、Notify)。
关于执行计划,这是一个很大的事情,我也不太熟悉。我应该把它粘贴到这里吗? :)
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
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)