时间戳

首页 > 解决方案 > 如何提高 SQL 内连接性能？

sql - 如何提高 SQL 内连接性能？

问题描述

如何提高此查询性能第二个表 CustomerAccountBrand 内连接需要很长时间。我添加了未使用的非聚集索引。这是在能够连接之后拆分两个内部连接吗？请任何人帮助获取该数据。 

SELECT DISTINCT 
    RA.AccountNumber, 
    RA.ShipTo,
    RA.SystemCode,
    CAB.BrandCode
FROM dbo.CustomerAccountRelatedAccounts RA  -- Views
INNER JOIN dbo.CustomerAccount CA
    ON RA.RelatedAccountNumber = CA.AccountNumber
    AND RA.RelatedShipTo = CA.ShipTo
    AND RA.RelatedSystemCode = CA.SystemCode 
INNER JOIN dbo.CustomerAccountBrand CAB   ---- Taking long time 4:30 mins
    ON  CA.AccountNumber = CAB.AccountNumber 
    AND CA.ShipTo = CAB.ShipTo 
    AND CA.SystemCode = CAB.SystemCode

ALTER VIEW [dbo].[CustomerAccountRelatedAccounts]
AS
SELECT        
    ca.AccountNumber, ca.ShipTo, ca.SystemCode, cafg.AccountNumber AS RelatedAccountNumber, cafg.ShipTo AS RelatedShipTo, 
    cafg.SystemCode AS RelatedSystemCode
FROM dbo.CustomerAccount AS ca 
    LEFT OUTER JOIN dbo.CustomerAccount AS cafg 
        ON ca.FinancialGroup = cafg.FinancialGroup 
            AND ca.NationalAccount = cafg.NationalAccount
            AND cafg.IsActive = 1
WHERE CA.IsActive = 1

标签： sqlsql-serversql-server-2012

解决方案

根据我的经验，当查询变得更复杂时，SQL 服务器查询优化器通常无法选择正确的连接算法（例如，与您的视图连接意味着没有可随时连接的索引）。如果这就是这里发生的情况，那么简单的解决方法是添加一个连接提示以将其转换为哈希连接：

SELECT DISTINCT 
    RA.AccountNumber, 
    RA.ShipTo,
    RA.SystemCode,
    CAB.BrandCode
FROM dbo.CustomerAccountRelatedAccounts RA  -- Views
INNER JOIN dbo.CustomerAccount CA
    ON RA.RelatedAccountNumber = CA.AccountNumber
    AND RA.RelatedShipTo = CA.ShipTo
    AND RA.RelatedSystemCode = CA.SystemCode 
INNER HASH JOIN dbo.CustomerAccountBrand CAB   ---- Note the "HASH" keyword
    ON  CA.AccountNumber = CAB.AccountNumber 
    AND CA.ShipTo = CAB.ShipTo 
    AND CA.SystemCode = CAB.SystemCode

推荐阅读