TSQL查询优化：需返回符合日期范围的客户信息而非全部交易

修改后的查询代码 #

方案一：使用EXISTS子查询（推荐） #

该方案通过EXISTS快速判断客户是否存在符合日期范围的交易，避免全量JOIN后再去重，性能更优：

SELECT DISTINCT
    p.RelatedNameId AS CustomerID,
    p.RelatedName AS CustomerName,
    tt.ParticluarType AS Type,
    pr.Sign AS [Sign],
    pr.ReportingName AS ReportingName
FROM ABC.dbo.vwPrimary p
JOIN AFGPurchase.IvL.Account a 
    ON p.RelatedNameId = a.ReportingEntityId
JOIN AFGPurchase.IvL.TaxTreatment tt 
    ON a.TaxTreatmentId = tt.TaxTreatmentId
JOIN AFGPurchase.IvL.Position pos 
    ON a.AccountId = pos.AccountId
JOIN AFGPurchase.IvL.Product pr 
    ON pos.ProductID = pr.ProductId
WHERE 
    tt.RegistrationType LIKE 'NON%'
    AND pr.Sign = 'XYZ2'
    AND pos.Quantity <> 0
    AND EXISTS (
        SELECT 1
        FROM AFGPurchase.IvL.Transaction t
        WHERE t.PositionId = pos.PositionId
          AND t.EffectiveDate BETWEEN '2021-12-31' AND '2022-12-31'
    )

方案二：预筛选交易表 #

先提取符合日期范围的PositionId集合，再关联其他表，适合Transaction表数据量极大的场景：

SELECT DISTINCT
    p.RelatedNameId AS CustomerID,
    p.RelatedName AS CustomerName,
    tt.ParticluarType AS Type,
    pr.Sign AS [Sign],
    pr.ReportingName AS ReportingName
FROM ABC.dbo.vwPrimary p
JOIN AFGPurchase.IvL.Account a 
    ON p.RelatedNameId = a.ReportingEntityId
JOIN AFGPurchase.IvL.TaxTreatment tt 
    ON a.TaxTreatmentId = tt.TaxTreatmentId
JOIN AFGPurchase.IvL.Position pos 
    ON a.AccountId = pos.AccountId
JOIN AFGPurchase.IvL.Product pr 
    ON pos.ProductID = pr.ProductId
JOIN (
    SELECT DISTINCT PositionId
    FROM AFGPurchase.IvL.Transaction
    WHERE EffectiveDate BETWEEN '2021-12-31' AND '2022-12-31'
) t 
    ON pos.PositionId = t.PositionId
WHERE 
    tt.RegistrationType LIKE 'NON%'
    AND pr.Sign = 'XYZ2'
    AND pos.Quantity <> 0

性能优化建议 #

• 索引优化：
  • 给AFGPurchase.IvL.Transaction创建复合索引：

    CREATE NONCLUSTERED INDEX IX_Transaction_EffectiveDate_PositionId 
    ON AFGPurchase.IvL.Transaction (EffectiveDate) 
    INCLUDE (PositionId);
    

    该索引能快速定位符合日期范围的交易对应的PositionId，避免全表扫描。
  • 检查并确保以下JOIN字段存在索引：
    • AFGPurchase.IvL.Account的ReportingEntityId、TaxTreatmentId
    • AFGPurchase.IvL.Position的AccountId、ProductID
    • vwPrimary视图底层表的RelatedNameId
• 简化查询逻辑：
  • 使用DISTINCT确保每个客户仅返回一条记录，避免重复数据。
  • 用表别名替代冗长的表名，提升代码可读性的同时不影响执行效率。
• 数据类型检查：
  • 确保EffectiveDate是日期/时间类型，避免字符串与日期的隐式转换导致索引失效。
• 执行计划分析：
  • 运行查询时查看执行计划，定位全表扫描、键查找等耗时环节，针对性调整索引或查询逻辑。

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