我在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);
这是它的执行计划:
![enter image description here](https://istack.dev59.com/hUdt8.webp)
PriorityId
设为主键。这将强制对表变量建立索引,可能会改变执行计划。 - Gordon Linoff