如果查询在“IN CLAUSE”中包含批次ID，MS SQL Server是否会自动创建临时表

I have a big query to get multiple rows by id's like

我有一个很大的查询来获取id的多行

SELECT * 
FROM TABLE 
WHERE Id in (1001..10000)

This query runs very slow and it ends up with timeout exception. Temp fix for it is querying with limit, break this query into 10 parts per 1000 id's.

此查询运行速度非常慢，最终会出现超时异常。它的临时修复是查询限制，将此查询分解为每1000个ID的10个部分。

I heard that using temp tables may help in this case but also looks like ms sql server automatically doing it underneath.

我听说在这种情况下使用临时表可能有所帮助，但也看起来像ms sql server自动在下面执行它。

What is the best way to handle problems like this?

处理这类问题的最佳方法是什么？

1 个解决方案

#1

You could write the query as follows using a temporary table:

您可以使用临时表如下编写查询：

CREATE TABLE #ids(Id INT NOT NULL PRIMARY KEY);
INSERT INTO #ids(Id) VALUES (1001),(1002),/*add your individual Ids here*/,(10000);

SELECT
  t.*
FROM
  [Table] AS t
  INNER JOIN #ids AS ids ON
    ids.Id=t.Id;

DROP TABLE #ids;

My guess is that it will probably run faster than your original query. Lookup can be done directly using an index (if it exists on the [Table].Id column).

我的猜测是它可能比原始查询运行得更快。可以使用索引直接进行查找（如果它存在于[Table] .Id列中）。

Your original query translates to

您的原始查询转换为

SELECT * 
FROM [TABLE]
WHERE Id=1000 OR Id=1001 OR /*...*/ OR Id=10000;

This would require evalutation of the expression Id=1000 OR Id=1001 OR /*...*/ OR Id=10000 for every row in [Table] which probably takes longer than with a temporary table. The example with a temporary table takes each Id in #ids and looks for a corresponding Id in [Table] using an index.

这将需要对[表]中的每一行表达式Id = 1000 OR Id = 1001 OR /*...*/ OR Id = 10000进行评估，这可能需要比临时表更长的时间。带有临时表的示例获取#ids中的每个Id，并使用索引在[Table]中查找相应的Id。

This all assumes that there are gaps in the Ids between 1000 and 10000. Otherwise it would be easier to write

这一切都假设在1000和10000之间的Ids中存在间隙。否则它将更容易编写

SELECT * 
FROM [TABLE]
WHERE Id BETWEEN 1001 AND 10000;

This would also require an index on [Table].Id to speed it up.

这也需要[Table]上的索引.Id来加速它。

#1