具有非常大的 IN 子句的 SQL Server 查询会导致活动监视器中出现大量查询

Question

SQL Server 2014 数据库。包含 2 亿行的表。

带有 HUGE IN 子句的非常大的查询。

我最初为他们编写了这个查询，但他们已将 IN 子句增加到 700 多个条目。 CTE 看起来是不必要的，因为为了简单起见，我省略了所有选择列及其子字符串 （） 转换。

重点是 IN 子句。 700+对这些。

WITH cte AS (
           SELECT *      
           FROM AODS-DB1B
           WHERE  
           Source+'-'+Target
    IN
    (
    'ACY-DTW',
    'ACY-ATL',
    'ACY-ORD',
    :
    : 700+ of these pairs
    :
    'HTS-PGD',
    'PIE-BMI',
    'PGD-HTS'
    )
    )
    SELECT *
    FROM cte
    order by Source, Target, YEAR, QUARTER

运行时，此查询会将 CPU 触发到 100% 数小时 - 这并不意外。

所涉及的所有列都有索引。

问题 1：除了巨大的 IN 子句之外，是否有更好或更有效的方法来完成此查询？ 700 个联合 ALL 会更好吗？

问题 2：当此查询运行时，它会创建一个包含 49 个"线程"（49 个具有相同Session_ID的进程）的Session_ID。 它们中的每一个都是此查询的实例，其"命令"是此查询文本。

其中21人被停职，其中 14 个正在运行，并且其中 14 个可运行。

随着任务的运行，这种情况会迅速变化。

这到底是怎么回事？ 此 SQL Server 是否将查询分解为多个部分以对其进行处理？

Answer 1

我建议您将 700+ 字符串存储在永久表中，因为在脚本中存储如此多的元数据通常被认为是不好的做法。您可以像这样创建表：

CREATE TABLE dbo.LookUp(Source varchar(250), Target varchar(250))
CREATE INDEX IX_Lookup_Source_Target on dbo.Lookup(Source,Target)
INSERT INTO dbo.Lookup (Source,Target)
SELECT 'ACY','DTW'
UNION
SELECT 'ACY','ATL'
.......

然后，您可以简单地加入此表：

SELECT * FROM [AODS-DB1B] a
INNER JOIN dbo.Lookup lt ON lt.Source = a.Source 
                         AND lt.Target=a.Target
ORDER BY Source, Target, YEAR, QUARTER

但是，更好的方法是规范化AODS-DB1B表并存储SourceId和TargetId INT 值，将 VARCHAR 值存储在Source和Target表中。然后，您可以编写一个仅执行整数比较而不是字符串比较的查询，这应该要快得多。

Answer 2

将所有代码放入一个临时表中（如果合适，也可以永久）.....

SELECT *      
FROM AODS-DB1B
INNER JOIN NEW_TABLE ON Source+'-'+Target = NEWTABLE.Code
WHERE  
...
...

Answer 3

您可以使用

所有这些值创建一个临时表，然后JOIN到该表，这将使该过程更快

Answer 4

我喜欢Jaco的答案

在源、目标上有一个索引

可能值得一试

where ( source = 'ACY' and target in ('DTW', 'ATL', 'ORD') )
   or ( source = 'HTS' and target in ('PGD') )