SQL Server中子查询与分组连接的性能对比及选型咨询

本人并非数据库专家，特此向社区咨询以下两个SQL查询的差异及性能选型问题：

查询1 #

DECLARE @today date = '2024-08-01'
SELECT (
        SELECT COALESCE(SUM([trx].[Amount]), 0.0)
        FROM [Transactions] AS [trx]
        LEFT JOIN [Invoices] AS [i] ON [i].[Id] = [trx].[InvoiceId]
        WHERE [p].[Id] = [trx].[PurchaseId] AND [trx].[Status] = 0 AND [trx].[Type] IN (1, 2) AND [trx].[PurchaseId] IS NOT NULL AND [i].[DueDate] < @today AND [i].[Flag] = CAST(0 AS bit)
    ) - (
        SELECT COALESCE(SUM([pp].[PaymentAmount]), 0.0)
        FROM [Transactions] AS [trx]
        LEFT JOIN [Invoices] AS [i] ON [i].[Id] = [trx].[InvoiceId]
        INNER JOIN [PaymentParts] AS [pp] ON [pp].[TransactionId] = [trx].[Id]
        WHERE [p].[Id] = [trx].[PurchaseId] AND [trx].[Status] = 0 AND [trx].[Type] IN (1, 2) AND [trx].[PurchaseId] IS NOT NULL AND [i].[DueDate] < @today AND [i].[Flag] = CAST(0 AS bit)
    )
FROM [Purchases] AS [p]

查询1的I/O统计 #

(862419 rows affected)
Table 'Workfile'. Scan count 19, logical reads 106192, physical reads 12151, page server reads 0, read-ahead reads 94041, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Transactions'. Scan count 2, logical reads 639166, physical reads 0, page server reads 0, read-ahead reads 318062, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Invoices'. Scan count 2, logical reads 117262, physical reads 3, page server reads 0, read-ahead reads 85754, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Purchases'. Scan count 1, logical reads 3973, physical reads 2, page server reads 0, read-ahead reads 4060, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'PaymentParts'. Scan count 1, logical reads 193222, physical reads 4, page server reads 0, read-ahead reads 193671, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
(1 row affected)

查询2 #

DECLARE @today date = '2024-08-01'
SELECT (
    SELECT SUM([trx].[UnpaidAmount])
    FROM ( 
        SELECT [trx].[Amount] - SUM([pp].[PaymentAmount]) AS [UnpaidAmount]
        FROM [Transactions] AS [trx]
        LEFT JOIN [Invoices] AS [i] ON [i].[Id] = [trx].[InvoiceId]
        LEFT JOIN [PaymentParts] AS [pp] ON [pp].[TransactionId] = [trx].[Id]
        WHERE [p].[Id] = [trx].[PurchaseId] AND [trx].[Status] = 0 AND [trx].[Type] IN (1, 2) AND [trx].[PurchaseId] IS NOT NULL AND [i].[DueDate] < @today AND [i].[Flag] = CAST(0 AS bit)
        GROUP BY [trx].[Id], [trx].[Amount]
    ) AS [trx]
)
FROM [Purchases] AS [p]

查询2的I/O统计 #

Warning: Null value is eliminated by an aggregate or other SET operation.
(862419 rows affected)
Table 'Workfile'. Scan count 39, logical reads 345144, physical reads 40885, page server reads 0, read-ahead reads 304259, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Transactions'. Scan count 1, logical reads 319583, physical reads 6090, page server reads 0, read-ahead reads 300961, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Invoices'. Scan count 1, logical reads 58631, physical reads 0, page server reads 0, read-ahead reads 1454, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Purchases'. Scan count 1, logical reads 3973, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'PaymentParts'. Scan count 1, logical reads 193222, physical reads 0, page server reads 0, read-ahead reads 192870, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
(1 row affected)

执行计划差异总结 #

• 查询1对[Transactions]和[Invoices]表执行两次连接操作
• 查询2执行聚合操作，并将大量数据加载至[tempdb]

二者响应时间相近，想请教从专业角度看哪个查询性能更优，应选用哪一个？

专业分析与选型建议 #

1. 逻辑正确性对比 #

首先注意查询2的SUM([pp].[PaymentAmount])在没有对应PaymentParts记录时会返回NULL，导致[trx].[Amount] - NULL结果为NULL，外层SUM会忽略这些NULL值；而查询1用COALESCE将无数据的情况转为0，最终结果会包含所有符合条件的交易（即使无付款记录也会计算Amount）。如果业务要求无付款的交易也要计入未付金额，查询2的结果会不准确，需要修改为[trx].[Amount] - COALESCE(SUM([pp].[PaymentAmount]), 0)才能和查询1逻辑一致。

2. 性能维度对比 #

内存与tempdb压力

查询2的Workfile逻辑读（345144）是查询1（106192）的3倍多，物理读也更高，说明它在tempdb中生成的中间结果集更大，会占用更多磁盘IO和内存资源。如果服务器tempdb配置不足（比如单数据文件、磁盘性能差），高并发场景下查询2的性能会快速下降，甚至引发阻塞。

基表IO效率

查询1对Transactions和Invoices做了两次扫描，但总逻辑读是639166+117262=756428，查询2是319583+58631=378214，看起来查询2的基表IO更少，但这是因为它把聚合压力转移到了tempdb。实际运行中，如果基表有合适的索引（比如Transactions(PurchaseId, Status, Type)包含Amount，Invoices(Id, DueDate, Flag)），查询1的两次扫描可以被索引覆盖，IO开销会显著降低；而查询2的分组聚合即使有索引，也需要在内存或tempdb中处理分组，压力依然存在。

并发稳定性

查询1的执行计划更简单，没有复杂的中间聚合，对tempdb依赖低，在多用户并发查询时，资源竞争更小，性能波动更平缓；查询2依赖tempdb的Workfile，当tempdb出现瓶颈时，响应时间会大幅波动。

3. 选型建议 #

• 如果逻辑正确性优先：先修正查询2的NULL处理问题，确保和业务需求匹配。
• 如果服务器资源有限（尤其是tempdb性能一般）：优先选择查询1，它对tempdb的压力小，并发场景下更稳定。
• 如果基表索引不完善，且单查询响应时间要求极高：可以优化查询2，比如添加Transactions(Id, Amount)和PaymentParts(TransactionId)的索引，减少分组聚合的开销，同时修正NULL值问题。
• 长期来看，建议优化索引：为Transactions创建包含PurchaseId, Status, Type, Amount, InvoiceId的覆盖索引，为Invoices创建Id, DueDate, Flag的索引，为PaymentParts创建TransactionId, PaymentAmount的覆盖索引，这会大幅降低两个查询的IO开销。

内容的提问来源于stack exchange，提问作者Alex Pykavy