主页 / dba / 问题 / 221041
Accepted
LCJ
Asked: 2018-10-26 13:24:00 +0800 CST

强制 SQL 引擎只为使用变量的查询准备非空计划

  • 772

我有以下两种查询方法。方法 1 在 20 秒内起作用。方法 2 甚至在数小时后仍未完成。

方法一

SELECT *,
        SUBSTRING(IDAndCode,CHARINDEX ('$', IDAndCode)+1, LEN(IDAndCode) - 2) AS ICDCode
From dbo.MyTable
WHERE F.Fact_Diagnosis_BK LIKE 'SKI-CE:'+'%' 
GO

方法二

DECLARE @DelimitingCharacter CHAR(1)
SET @DelimitingCharacter = '$'; 

SELECT *,
        SUBSTRING(IDAndCode,CHARINDEX (@DelimitingCharacter, IDAndCode)+1, LEN(IDAndCode) - 2) AS ICDCode
From dbo.MyTable
WHERE F.Fact_Diagnosis_BK LIKE 'SKI-CE:'+'%' 
GO

注意：以上两个是简化查询。实际查询可以在下面的执行计划中看到。

执行计划

实际执行计划——方法 1 https://www.brentozar.com/pastetheplan/?id=rJlcWTk3m

预计计划 - 方法 2： https ://www.brentozar.com/pastetheplan/?id=SJddO3en7

注意：方法2的计划使用Nested Loopjoin而不是Hash Matchjoin

问题

我知道这可以通过添加来解决OPTION (RECOMPILE)。如果我理解正确的话，缓慢的发生是因为 SQL Server 正在创建一个计划来适应NULL变量的可能性。

在我的例子中，变量将Non-Null在执行查询之前（保证）

  1. 在 SQL Server 2016 中，在不使用 OPTION (RECOMPILE) 的情况下，有什么可能告诉 SQL 引擎变量将为非空值并仅针对这种情况准备计划？

  2. 这种慢叫参数嗅探吗？（这里没有参数，是单机查询中的局部变量）

注意：我正在尝试查看选项以在不使用 RECOMPILE 的情况下使查询运行得更快。

参考资料：

  1. SQL Server 查询：文字快，变量慢
  2. 使用选项（重新编译）、常量折叠和避免参数嗅探问题提高查询性能
  3. 参数嗅探 vs 变量 vs 重新编译 vs 未知优化
  4. SQL Server 中的局部变量与参数化存储过程
sql-server performance

2 个回答

  1. Best Answer

    以下是可以解决此问题的三种方法。欢迎更多的方法。

    解决方案 1

    使用临时表代替变量（@DelimitingCharacter）。

    CREATE TABLE #VariableHoldingTable (DelimitingCharacter CHAR(1) NOT NULL)
INSERT INTO #VariableHoldingTable
SELECT '<'

    --并加入

      INNER JOIN #VariableHoldingTable V
    ON 1 = 1

    执行计划：https ://www.brentozar.com/pastetheplan/?id=rJpehe5hQ

    方案二

    CREATE CLUSTERED INDEX CX_DXID on #MultipleDiagnosisDXs(DXID)

    执行计划：https ://www.brentozar.com/pastetheplan/?id=B1nKne9nX

    解决方案 3

    OPTION (RECOMPILE)

    执行计划：https ://www.brentozar.com/pastetheplan/?id=BkRgalqnX

    • 1 
  2. CREATE TABLE #MultipleDiagnosisDXs (DXID INT Not null)

    注意not null这里。填充创建索引后。

    Create Clustered index IX_DXID on #MultipleDiagnosisDXs(DXID)

    在查询正确时，

    WHERE EXISTS (SELECT DXID FROM #MultipleDiagnosisDXs A where A.DXID=B.DXID)

    不需要order by条款

    以上改变是必须的。

    你应该尝试一次EXEC sys.sp_executesql。

    您也应该尝试Row_Number一次，这样您就不需要第二个 Select 语句，也不需要临时表。

    不使用 ”*”。

    只需尝试With (Nolock)提示：如果它有帮助，那么我们将对此进行更多讨论。它并不总是被认为是最佳实践，但请尝试一次。

    • 0

相关问题