带有超大 IN 子句的 SQL Server 查询会导致事件监视器中出现大量查询

Question

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

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

我最初为他们编写了这个查询，但他们已经将 IN 子句增加到 700 多个条目。 CTE 看起来没有必要，因为为简单起见，我省略了所有选择列及其 substring() 转换。

重点在 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 UNION ALL 会更好吗？

问题2 :当这个查询运行时，它会创建一个包含 49 个“线程”的 Session_ID(49 个进程都具有相同的 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 标准化 table 和商店 SourceId和 TargetId INT 值代替，VARCHAR 值存储在 Source和 Target表。然后您可以编写一个只执行整数比较而不是字符串比较的查询，这应该快得多。