SQL查询参数过多导致查询速度变慢。

Question

我正在执行一系列带有“IN”子句的查询，其中有许多指定的值，就像这样：

SELECT 
    [time_taken], [distance], [from_location_geocode_id],
    [to_location_geocode_id] 
FROM 
    [Travel_Matrix] 
WHERE 
    [from_location_geocode_id] IN (@param1, @param2, @param3, @param4, @param5) 
    AND [to_location_geocode_id] IN (@param1, @param2, @param3, @param4, @param5)

示例显示了5个参数，但实际上可能有数百个参数。

对于少量参数（大约400个以下），SQL Server使用一个带有多个“计算标量”操作的执行计划，然后将其连接、排序和合并以返回结果。

对于大量参数（超过400个），它使用更快的“哈希匹配（右半连接）”方法。

不过，我希望它在查询有50个参数时尽早使用第二种执行计划，因为我的测试表明，查询有50到400个参数的速度很慢。

我已经尝试使用各种查询的“OPTION”值，但无法让它使用第二种执行计划，我知道这将更有效率。

我将感激任何能够提供正确提示的人，以便查询以第二种执行计划的方式执行。

谢谢。

- Pete

---

通常，Sql-Server优化器很聪明。强制它按你的方式操作很少是一个好主意。 - Zohar Peled

如果您的“IN”子句有大量参数，建议将这些参数存储在临时表中，并通过连接来使用它们，以获得更好的性能。 - Alex

尝试将这些变量插入到临时表或表变量中，然后再从中获取。 - Arunprasanth K V

Answer 1

我认为在IN子句中使用400个参数太多了。最好将这些值存储在一个临时表中，并在其上进行JOIN操作，可能需要在临时表的列上创建索引以加快速度。

- koolkoda

---

那就是答案。可悲的是，因为我所知道的任何ORM都不支持这个。 - TomTom

是的，我也尝试过那种方法。然而，我发现使用IN查询比临时表方法更快，只要它使用第二个执行计划。实际上，第二个执行计划正在创建一个临时表并将其连接起来，但是所有操作都在内部完成，这可能使其更快。 - Pete

@TomTom，大多数ORM都允许使用存储过程或运行原始定制的SQL查询。在这种情况下非常有用... - Shahar Gvirtz

是的，但如果条件中有超过X个元素，Entity Framework也可以将IN子句转换为使用临时表。那将非常有用。编写存储过程有很多缺点，然后使用任意数量的参数调用它是另一个问题。解决方案非常简单-但没有ORM这样做。 - TomTom

这应该是数据库的自动优化，不应该依赖于客户端。 - Kiruahxh

Answer 2

从性能角度考虑，IN子句并不好，可以尝试以下方式：

DECLARE @Tmp TABLE(Id INT)
INSERT INTO @Tmp(Id) VALUES(@param1), (@param2), (@param3), (@param4), (@param5)

SELECT 
   [time_taken], [distance], [from_location_geocode_id],
   [to_location_geocode_id] 
FROM 
[Travel_Matrix] 
WHERE 
EXISTS (SELECT 1 FROM @Tmp Where @Tmp.Id=[from_location_geocode_id])
AND EXISTS (SELECT 1 FROM @Tmp Where @Tmp.Id=[to_location_geocode_id])

- Jaydip Jadhav

Answer 3

你也可以使用这些参数创建过滤索引，即使你已经有了覆盖所有列值的索引。使用过滤索引，你的查询将会更快。但是插入操作会稍微慢一些，而且过滤索引只适用于特定的目的。

Ex:
create table test
(
id int
)

insert into test
select top 100* from numbers
where n<=1100

现在，如果我们的查询总是带有大参数，比如 id in (2,100,45,98...)

如果我们创建一个如下的过滤索引

create index on dbo.test(id)
where id in (2,958,100)

我们的查询将使用该索引，速度会更快，当然也有一些限制，比如between查询、大小写查询、插入较慢。但我建议测试这个选项并使其覆盖。

更新：
进一步的统计数据对于估算行值非常重要，如果您没有一个以fromlcoationid和tolcoationid作为关键列的索引，SQL将不会创建多列统计信息。因此，另一个选项是创建多列统计信息，如果您不想采用过滤索引方法...

create statistics test1 on dbo.test(fromlocationid,tolcoationid)
where fromlocationid in (@param1,.....) and tolocationid in (@param1,@param2...)

我唯一看到的问题是，过滤后的统计数据更新频率相对于常规统计数据会较低。 因此，根据您的需求，您可能希望通过作业手动更新它们。

- TheGameiswar

---

谢谢你的建议。然而当我尝试时，发现创建筛选索引花费了很长时间——我的数据库有更多行——除非我在你的建议中遗漏了什么。 - Pete

是的，建立索引需要时间，它就像普通索引一样，但尺寸更小，并且始终仅存储where子句中的那些值。在索引创建期间，您可能还想查看等待统计信息。 - TheGameiswar