SQL IN查询优化

Question

我在SQL Server中有这个查询，但由于IN子句的原因，查询时间很长。我应该如何进行优化？

DECLARE @PriorityIn TABLE ([PriorityId] [BIGINT] NOT NULL);
DECLARE @GatewayId BIGINT = 4;

INSERT INTO @PriorityIn
    SELECT PriorityId 
    FROM PriorityProviders 
    WHERE ProviderId = @GatewayId AND Type = 0

SELECT COUNT(*) 
FROM MessageIn 
WHERE ScheduleDate < GetDate() 
  AND CurrentStatus IN (0, 3, 6) 
  AND ((ProviderId IS NULL 
        AND PriorityId IN (SELECT PriorityId FROM @PriorityIn)) 
       OR ProviderId = @GatewayId);

当我看到这个查询时，它运行非常快：

SELECT COUNT(*) 
FROM MessageIn 
WHERE ScheduleDate < GetDate() 
  AND CurrentStatus IN (0, 3, 6) 
  AND ((ProviderId IS NULL AND PriorityId IN (1, 2)) 
       OR ProviderId = @GatewayId);

这是它的执行计划: 我该如何解决这个问题？

- mayur Rathod

---

尝试将PriorityId设为主键。这将强制对表变量建立索引，可能会改变执行计划。 - Gordon Linoff

是的，在 MessageIn 表中的 PriorityId 列中已经存在索引。在 PriorityProvider 表中只有 2 条记录。但是在 MessageIn 表中有 800 万条记录。 - mayur Rathod

为什么要这样做（使用表变量来存储“PriorityID”），当“PriorityID”的唯一可能值是4？这可能是一个XY问题。 - Tab Alleman

@TabAlleman 4 是提供者 ID，我根据它获取优先级 ID。 - mayur Rathod

我看到了，当我快速浏览时，“providerId”和“priorityId”看起来很相似。 - Tab Alleman

Answer 1

由于缺乏基数估计（没有统计数据）、序列化修改等原因，表变量几乎总是比临时表执行效率差。许多博客都有相关说明，这是其中之一，而这里是Brent Ozar谈到的一些差异。

因此，首先将其更改为临时表并查看是否有所帮助。在@PriorityIn中的行数越多，它就越可能有所帮助。

我还会将@GatewayId更改为INT，除非ProviderId也是BIGINT。否则就没有隐式转换的理由。这也适用于PriorityId。

此外，这篇关于厨房水槽查询和性能的好博客文章也可以加快速度。

- S3S

---

当我使用临时表时，响应时间为11秒，但是在使用表变量时，响应时间只有4秒。 - mayur Rathod

当您使用表变量时，它需要4秒钟，那么当您使用静态值时呢？您能够粘贴执行计划吗：https://www.brentozar.com/pastetheplan/ - S3S

执行计划已添加到问题本身。 - mayur Rathod

图片并没有太大帮助，因为我们无法点击它们并查看谓词、行等内容。您能使用这个链接吗？https://www.brentozar.com/pastetheplan/ - S3S

这是我在过去两天里看到的。顺便说一下，感谢您的快速回答。 - mayur Rathod

Answer 2

我刚刚将我的查询更改为以下内容，现在我得到了快速响应，就像之前花费4秒钟，现在只需要1秒钟，两个结果都是MessageIn表中的800万条记录。 但仍在寻找更好的结果。

DECLARE @GatewayId INT = 4;
DECLARE @PriorityIn TABLE(
    [PriorityId] [INT] NOT NULL
);

INSERT INTO @PriorityIn
SELECT PriorityId FROM  PriorityProviders WHERE ProviderId = @GatewayId AND Type = 0


SELECT count(Id) From MessageIn m
LEFT JOIN @PriorityIn o on m.PriorityId = o.PriorityId
WHERE ScheduleDate < GetDate() AND CurrentStatus in (0,3,6) AND 
((ProviderId is null AND o.PriorityId is not null) 
OR ProviderId = @GatewayId)

- mayur Rathod