替换SQL Server查询子查询以提升性能的方案咨询

我正在尝试优化SQL Server中的以下查询性能：

WITH s
AS (
    SELECT src.[ITEMID] AS [Item Code]
        ,po.[ORDERACCOUNT] AS [Order Account]
        ,vpsj.[INVOICEACCOUNT] AS [Invoice Account]
        ,vpsj.[PURCHID] AS [PO Number]
        ,src.[INVENTDIMID] AS [Inventory Dimension Id]
        ,po.[PURCHREQLINEREFID] AS [Reference Id]
        ,po.[PURCHPOOLID] AS [Purchase Pool Code]
        ,po.[ITEMBUYERGROUPID] AS [Inventory Buyer Group Code]
        ,cast(0 AS [bigint]) AS [Category Id]
        ,(
            SELECT TOP (1) lt.LEDGERDIMENSION
            FROM [dbo].[EALedgerTransactions] lt
            WHERE lt.[PARTITION] = src.[PARTITION]
                AND lt.[VOUCHERDATAAREAID] = src.[COMPANYCODE]
                AND lt.[VOUCHER] = src.[COSTLEDGERVOUCHER]
                AND lt.[POSTINGTYPE] IN (82,83)
            ) AS [Ledger Dimension Id]
        ,(
            SELECT TOP (1) lt.MAINACCOUNTID
            FROM [dbo].[EALedgerTransactions] lt
            WHERE lt.[PARTITION] = src.[PARTITION]
                AND lt.[VOUCHERDATAAREAID] = src.[COMPANYCODE]
                AND lt.[VOUCHER] = src.[COSTLEDGERVOUCHER]
                AND lt.[POSTINGTYPE] IN (82,83)
            ) AS [Main Account Id]
        ,src.[RECORDID] AS [Record Id]
        ,src.[COMPANYCODE] AS [Company Code]
        ,dateadd(day, datediff(day, 0, po.[CREATEDDATEANDTIME] - getutcdate() + getutcdate()), 0) AS [PO Date],
        po.[CURRENCYCODE] AS [Currency Code]
        ,src.[SOURCEDOCUMENTLINE] AS [Source Document Line Id]
        ,src.[ACCOUNTINGDATE] AS [Transaction Date]
        ,vpsj.[PACKINGSLIPID] AS [Product Receipt]
        ,cast(NULL AS [nvarchar](20)) AS [Invoice Number]
        ,src.[PURCHUNIT] AS [Purchase Unit]
        ,cast(NULL AS [nvarchar](20)) AS [Inventory Unit]
        ,po.[POAMOUNT] / nullif(po.[POQUANTITY], 0.0) AS [Purchase Price]
        ,src.[QTY] AS [Receipt Quantity]
        ,src.[INVENTQTY] AS [Receipt Inventory Quantity]
        ,src.[VALUEMST] AS [Receipt Amount Master]
        ,(
            SELECT sum(QTY) AS QTY
            FROM [dbo].[EAVendorPackingslipTransactions] pst
            WHERE pst.[PARTITION] = src.[PARTITION]
                AND pst.[COMPANYCODE] = src.[COMPANYCODE]
                AND pst.[COSTLEDGERVOUCHER] = src.[COSTLEDGERVOUCHER]
            ) AS [Voucher Quantity]
        ,(
            SELECT sum(ACCOUNTINGCURRENCYAMOUNT) AS ACCOUNTINGCURRENCYAMOUNT
            FROM [dbo].[EALedgerTransactions] lt
            WHERE lt.[PARTITION] = src.[PARTITION]
                AND lt.[VOUCHERDATAAREAID] = src.[COMPANYCODE]
                AND lt.[VOUCHER] = src.[COSTLEDGERVOUCHER]
                AND lt.[POSTINGTYPE] IN (82,83)
            ) AS [GRNI Amount Master]
        ,(
            SELECT sum(TRANSACTIONCURRENCYAMOUNT) AS TRANSACTIONCURRENCYAMOUNT
            FROM [dbo].[EALedgerTransactions] lt
            WHERE lt.[PARTITION] = src.[PARTITION]
                AND lt.[VOUCHERDATAAREAID] = src.[COMPANYCODE]
                AND lt.[VOUCHER] = src.[COSTLEDGERVOUCHER]
                AND lt.[POSTINGTYPE] IN (82,83)
            ) AS [GRNI Amount]
    FROM [dbo].[EAVendorPackingslipTransactions] src
    LEFT JOIN (
        SELECT [RECORDID]
            ,[INVOICEACCOUNT]
            ,[COMPANYCODE]
            ,[PURCHID]
            ,[PACKINGSLIPID]
            ,[ACCOUNTINGDATE]
            ,[LEDGERVOUCHER]
            ,[COSTLEDGERVOUCHER]
            ,rank() OVER (
                PARTITION BY [RECORDID] ORDER BY [VENDPACKINGSLIPVERSION_RECORDID] DESC
                ) AS [RANK]
        FROM [dbo].[EAVendorPackingslipJournals] vpsj
        ) vpsj ON vpsj.[RECORDID] = src.[VENDPACKINGSLIPJOUR]
        AND vpsj.[RANK] = 1
    LEFT JOIN [dbo].[EAPurchaseOrders] po ON po.[PARTITION] = src.[PARTITION]
        AND po.[COMPANYCODE] = src.[COMPANYCODE]
        AND po.[INVENTORYTRANSACTIONID] = src.[INVENTTRANSID]
    )

问题出在查询中的多个子查询上，我尝试用连接替换这些子查询，但始终无法得到预期结果。求替换子查询或其他优化的思路。

执行计划图示：

优化思路 #

1. 合并重复子查询，改用预聚合CTE

原查询多次重复访问EALedgerTransactions和EAVendorPackingslipTransactions表，可通过预聚合CTE减少表扫描次数，同时保证逻辑与原查询一致：

WITH LedgerAgg AS (
    SELECT 
        lt.[PARTITION],
        lt.[VOUCHERDATAAREAID],
        lt.[VOUCHER],
        -- 用FIRST_VALUE模拟原TOP(1)逻辑（无排序时取任意第一条）
        FIRST_VALUE(lt.LEDGERDIMENSION) OVER (PARTITION BY lt.[PARTITION], lt.[VOUCHERDATAAREAID], lt.[VOUCHER] ORDER BY (SELECT NULL)) AS [Ledger Dimension Id],
        FIRST_VALUE(lt.MAINACCOUNTID) OVER (PARTITION BY lt.[PARTITION], lt.[VOUCHERDATAAREAID], lt.[VOUCHER] ORDER BY (SELECT NULL)) AS [Main Account Id],
        SUM(lt.ACCOUNTINGCURRENCYAMOUNT) AS [GRNI Amount Master],
        SUM(lt.TRANSACTIONCURRENCYAMOUNT) AS [GRNI Amount]
    FROM [dbo].[EALedgerTransactions] lt
    WHERE lt.[POSTINGTYPE] IN (82,83)
    GROUP BY lt.[PARTITION], lt.[VOUCHERDATAAREAID], lt.[VOUCHER]
),
PackingSlipAgg AS (
    SELECT 
        pst.[PARTITION],
        pst.[COMPANYCODE],
        pst.[COSTLEDGERVOUCHER],
        SUM(pst.QTY) AS [Voucher Quantity]
    FROM [dbo].[EAVendorPackingslipTransactions] pst
    GROUP BY pst.[PARTITION], pst.[COMPANYCODE], pst.[COSTLEDGERVOUCHER]
),
s AS (
    SELECT 
        src.[ITEMID] AS [Item Code],
        po.[ORDERACCOUNT] AS [Order Account],
        vpsj.[INVOICEACCOUNT] AS [Invoice Account],
        vpsj.[PURCHID] AS [PO Number],
        src.[INVENTDIMID] AS [Inventory Dimension Id],
        po.[PURCHREQLINEREFID] AS [Reference Id],
        po.[PURCHPOOLID] AS [Purchase Pool Code],
        po.[ITEMBUYERGROUPID] AS [Inventory Buyer Group Code],
        CAST(0 AS BIGINT) AS [Category Id],
        la.[Ledger Dimension Id],
        la.[Main Account Id],
        src.[RECORDID] AS [Record Id],
        src.[COMPANYCODE] AS [Company Code],
        -- 简化日期计算逻辑，与原代码效果一致
        CAST(po.[CREATEDDATEANDTIME] AS DATE) AS [PO Date],
        po.[CURRENCYCODE] AS [Currency Code],
        src.[SOURCEDOCUMENTLINE] AS [Source Document Line Id],
        src.[ACCOUNTINGDATE] AS [Transaction Date],
        vpsj.[PACKINGSLIPID] AS [Product Receipt],
        CAST(NULL AS NVARCHAR(20)) AS [Invoice Number],
        src.[PURCHUNIT] AS [Purchase Unit],
        CAST(NULL AS NVARCHAR(20)) AS [Inventory Unit],
        po.[POAMOUNT] / NULLIF(po.[POQUANTITY], 0.0) AS [Purchase Price],
        src.[QTY] AS [Receipt Quantity],
        src.[INVENTQTY] AS [Receipt Inventory Quantity],
        src.[VALUEMST] AS [Receipt Amount Master],
        psa.[Voucher Quantity],
        la.[GRNI Amount Master],
        la.[GRNI Amount]
    FROM [dbo].[EAVendorPackingslipTransactions] src
    LEFT JOIN (
        SELECT 
            [RECORDID],
            [INVOICEACCOUNT],
            [COMPANYCODE],
            [PURCHID],
            [PACKINGSLIPID],
            [ACCOUNTINGDATE],
            [LEDGERVOUCHER],
            [COSTLEDGERVOUCHER],
            RANK() OVER (
                PARTITION BY [RECORDID] ORDER BY [VENDPACKINGSLIPVERSION_RECORDID] DESC
            ) AS [RANK]
        FROM [dbo].[EAVendorPackingslipJournals] vpsj
    ) vpsj ON vpsj.[RECORDID] = src.[VENDPACKINGSLIPJOUR] AND vpsj.[RANK] = 1
    LEFT JOIN [dbo].[EAPurchaseOrders] po ON po.[PARTITION] = src.[PARTITION]
        AND po.[COMPANYCODE] = src.[COMPANYCODE]
        AND po.[INVENTORYTRANSACTIONID] = src.[INVENTTRANSID]
    -- 关联预聚合的台账数据
    LEFT JOIN LedgerAgg la 
        ON la.[PARTITION] = src.[PARTITION]
        AND la.[VOUCHERDATAAREAID] = src.[COMPANYCODE]
        AND la.[VOUCHER] = src.[COSTLEDGERVOUCHER]
    -- 关联预聚合的装箱单数量数据
    LEFT JOIN PackingSlipAgg psa
        ON psa.[PARTITION] = src.[PARTITION]
        AND psa.[COMPANYCODE] = src.[COMPANYCODE]
        AND psa.[COSTLEDGERVOUCHER] = src.[COSTLEDGERVOUCHER]
)
SELECT * FROM s;

2. 添加针对性覆盖索引

根据查询的筛选、关联和字段需求，创建以下覆盖索引可大幅提升性能：

• EALedgerTransactions：CREATE NONCLUSTERED INDEX IX_EALedgerTransactions_Partition_Voucher ON [dbo].[EALedgerTransactions] ([PARTITION], [VOUCHERDATAAREAID], [VOUCHER], [POSTINGTYPE]) INCLUDE ([LEDGERDIMENSION], [MAINACCOUNTID], [ACCOUNTINGCURRENCYAMOUNT], [TRANSACTIONCURRENCYAMOUNT]);
• EAVendorPackingslipTransactions：CREATE NONCLUSTERED INDEX IX_EAVendorPackingslipTransactions_Partition_CostVoucher ON [dbo].[EAVendorPackingslipTransactions] ([PARTITION], [COMPANYCODE], [COSTLEDGERVOUCHER]) INCLUDE ([QTY]);
• EAVendorPackingslipJournals：CREATE NONCLUSTERED INDEX IX_EAVendorPackingslipJournals_RecordId_Version ON [dbo].[EAVendorPackingslipJournals] ([RECORDID], [VENDPACKINGSLIPVERSION_RECORDID]) INCLUDE ([INVOICEACCOUNT], [COMPANYCODE], [PURCHID], [PACKINGSLIPID], [ACCOUNTINGDATE], [LEDGERVOUCHER], [COSTLEDGERVOUCHER]);
• EAPurchaseOrders：CREATE NONCLUSTERED INDEX IX_EAPurchaseOrders_Partition_TransId ON [dbo].[EAPurchaseOrders] ([PARTITION], [COMPANYCODE], [INVENTORYTRANSACTIONID]) INCLUDE ([ORDERACCOUNT], [PURCHREQLINEREFID], [PURCHPOOLID], [ITEMBUYERGROUPID], [CREATEDDATEANDTIME], [CURRENCYCODE], [POAMOUNT], [POQUANTITY]);

3. 验证连接类型必要性

检查vpsj和po的LEFT JOIN是否为业务必需：如果src对应的记录必然存在匹配的台账或采购单，可改为INNER JOIN，减少NULL值处理开销。

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