主页 / dba / 问题 / 331316
Accepted
Skary
Asked: 2023-09-18 21:58:33 +0800 CST

SQL Server 查询的性能调整，使用 LEFT OUTER JOIN 与 INNER JOIN

  • 772

在我的场景中，我需要优化一个存储过程，我用它从交换数据库将数据导入数据库。我真的很困惑，因为 INNER JOIN 的解决方案低于 LEFT OUTER JOIN 解决方案，基本上看来我检查“关系”存在的方式导致了巨大的减速。

更清楚地说，我有一个带有几个表格的 Final_DB，一个用于文章 (tbl_ana_Articles)，一个用于文章属性，又名特征 (tbl_ana_Characteristics)，以及几个其他表。在另一个 Exchange_DB 中，我获取文章和属性/特征之间的关系（它用于定期更新 Final_DB 中的关系）。

由交换数据库提供的具有关系的表需要先取消透视，然后才能有用（当我得到它时，它不是交叉联接表，在取消透视后它变成交叉联接）。

所以基本上我以这种方式编写查询：

WITH ExchangeArticleCode_CharacteristicCode AS (
  SELECT [ACODAR], SUBSTRING([TNCAR],5,2) AS [TNCAR] , [TVALOR]
  FROM [EXCHANGE_DB].[dbo].[ANART00F]
  UNPIVOT
  (
   [TVALOR]
   FOR [TNCAR] in ([ACAR01], ACAR02, ACAR03, ACAR04 , ACAR05 , ACAR06 , ACAR07 , ACAR08 , ACAR09 , ACAR10 , ACAR11 , ACAR12 , ACAR13 , ACAR14 , ACAR15 , ACAR16 , ACAR17 , ACAR18 , ACAR19 , ACAR20)
  ) AS [UNPIVOT]
  WHERE [TVALOR] IS NOT NULL AND [TVALOR] != ''
)

SELECT Characteristic.[ID]        AS [ID_CHARACTERISTIC]
       ,Article.[ID]               AS [ID_ARTICLE]
       ,Characteristic.[ID_FILTER] AS [ID_FILTER]
FROM ExchangeArticleCode_CharacteristicCode
LEFT OUTER JOIN [dbo].[tbl_ana_Articles] AS Article 
 ON (ExchangeArticleCode_CharacteristicCode.ACODAR collate Latin1_General_CI_AS) = Article.CODE
LEFT OUTER JOIN [dbo].[tbl_ana_Characteristics] AS Characteristic 
 ON (ExchangeArticleCode_CharacteristicCode.TNCAR collate Latin1_General_CI_AS) + '_' + (ExchangeArticleCode_CharacteristicCode.TVALOR collate Latin1_General_CI_AS) = Characteristic.ID_ERP
WHERE Characteristic.[IS_ACTIVE] = 1

这个解决方案的速度令人惊讶，但有问题，有时交换数据库中有垃圾，因此左连接无法匹配代码，并且在结果表中我得到一些 NULL。如果我尝试防止 NULL，将 LEFT OUTER JOIN 替换为 INNER JOIN 或在 where 条件中添加检查（IS NOT NULL），则查询执行起来会变得非常缓慢且繁重。我不清楚为什么以及如何避免这种情况。

这里是快速查询的执行计划： https://www.brentozar.com/pastetheplan/ ?id=ryMBHCryp

这里是慢查询的执行计划： https://www.brentozar.com/pastetheplan/ ?id=SywALRS16

公平地说，在我看来，慢速查询有一个昂贵的嵌套循环，但是为什么 INNER JOIN 在这样的嵌套循环中进行转换？

sql-server

2 个回答

  1. Best Answer

    问题

    此代码有一些问题可以通过临时表轻松解决。特别是，在运行时构建多列和值连接键通常会导致奇怪的计划选择和糟糕的基数估计。

    我还对您的子句进行了轻微调整，where以查找其中至少包含单个字符的行。不需要检查非空字符串和非空字符串，因为空值无法与值匹配。

    您可能还会发现临时表上的聚集索引很有用，无论是在上面ACODAR, TNCAR_TVALOR还是相反TNCAR_TVALOR, ACODAR。

    WITH 
    ExchangeArticleCode_CharacteristicCode AS 
(
    SELECT 
        ACODAR = 
            ACODAR COLLATE Latin1_General_CI_AS, 
        TNCAR = 
            SUBSTRING(TNCAR, 5, 2), 
        TVALOR,
        TNCAR_TVALOR = 
            SUBSTRING(TNCAR, 5, 2) + 
            '_' + 
            TVALOR COLLATE Latin1_General_CI_AS
    FROM EXCHANGE_DB.dbo.ANART00F
    UNPIVOT
    (
        TVALOR
        FOR TNCAR IN 
            (
                ACAR01, 
                ACAR02, 
                ACAR03, 
                ACAR04, 
                ACAR05, 
                ACAR06, 
                ACAR07, 
                ACAR08, 
                ACAR09, 
                ACAR10, 
                ACAR11, 
                ACAR12, 
                ACAR13, 
                ACAR14, 
                ACAR15, 
                ACAR16, 
                ACAR17, 
                ACAR18, 
                ACAR19, 
                ACAR20
            )
    ) AS [UNPIVOT]
    WHERE TVALOR LIKE '_%'
)
SELECT
    *
INTO #ExchangeArticleCode_CharacteristicCode
FROM ExchangeArticleCode_CharacteristicCode;

SELECT 
    ID_CHARACTERISTIC = 
        Characteristic.ID,
    ID_ARTICLE = 
        Article.ID,
    ID_FILTER = 
        Characteristic.ID_FILTER
FROM #ExchangeArticleCode_CharacteristicCode AS ExchangeArticleCode_CharacteristicCode
LEFT OUTER JOIN dbo.tbl_ana_Articles AS Article 
  ON ExchangeArticleCode_CharacteristicCode.ACODAR = Article.CODE
LEFT OUTER JOIN dbo.tbl_ana_Characteristics AS Characteristic 
  ON ExchangeArticleCode_CharacteristicCode.TNCAR_TVALOR = Characteristic.ID_ERP
WHERE Characteristic.[IS_ACTIVE] = 1;
    • 2

  2. WHERE [TVALOR] IS NOT NULL AND [TVALOR] != ''未透视表中的条件 比 SQLServer 估计的选择性要高得多。

    在快速计划中，它估计将从 423640 行中选择 216056 行，但实际上只生成 11944 行。

    使用 LEFT JOIN，Sqlserver 从 ExchangeArticleCode_CharacteristicCode（这是 LEFT JOIN 中的左表）开始，生成 11944 行，然后将它们与其他大表匹配，但这不会增加行数，因为显然这些行不会增加行数。不匹配多于一篇文章或特征。

    当您切换到INNER JOIN（或添加 NOT NULL 检查，这告诉优化器可以将 LEFT JOIN 视为 INNER JOIN）时，Sqlserver 可以将连接重新排序为它认为最有效的方式。

    它假设取消透视 ANART00F 表将使行成倍增加，因此将其推迟到最后。

    它正确地假设 ANART00F 和 Articles 表之间的联接将是高效的（它生成 21182 行），但随后它将它们与 Characteristic 表联接起来。由于联接条件需要未透视的列，因此这实际上是无条件的完全联接并产生 1.15 亿行，然后将结果向上透视为 20 亿行。只有这样，sqlserver 才会应用 where 条件，将这 20 亿减少到 11934（而 sqlserver 预计结果集会减少到 1.65 亿）。

    要纠正此问题，您可以尝试更新所有涉及的表的统计信息，但我怀疑即使通过更新的统计信息也无法正确猜测您尝试选择的记录和逆透视的具体值。

    您可以尝试的另一个解决方案是强制SET FORCEPLAN查询优化器按照您指定的顺序连接表

    SET FORCEPLAN ON;

--  your query here

SET FORCEPLAN OFF;
    • 1

相关问题