Sql-server – Force SQL Engine to prepare Non-Null plan only for a query that use variable

execution-planoptimizationperformancequery-performancesql server

I have following two query approaches. The Approach 1 works in 20 seconds. The Approach 2 did not complete even after hours.

Approach 1

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

Approach 2

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

Note: The above two are simplified queries. The actual queries can be seen in the execution plan below.

Execution Plans

Actual Execution Plan – Approach 1
https://www.brentozar.com/pastetheplan/?id=rJlcWTk3m

Estimated Plan – Approach 2:
https://www.brentozar.com/pastetheplan/?id=SJddO3en7

Note: The plan for Approach 2 uses Nested Loop join instead of Hash Match join

Question

I know that this can be fixed by adding OPTION (RECOMPILE). If I understand it correctly, the slowness is happening since SQL Server is creating a plan to accommodate NULL possibility of the variable.

In my case, the variable will be Non-Null before the query is executed (guaranteed)

  1. In SQL Server 2016, what are the possibilities to tell the SQL engine that the variable will be non-null and prepare a plan only for such condition, without using OPTION (RECOMPILE)?

  2. Is the slowness called parameter sniffing? (There is no parameter here. It is a local variable in a stand alone query)

Note: I am trying to see options to get the query running faster without using RECOMPILE.

References:

  1. SQL Server Query: Fast with Literal but Slow with Variable
  2. Improving query performance with OPTION (RECOMPILE), Constant Folding and avoiding Parameter Sniffing issues
  3. Parameter Sniffing vs VARIABLES vs Recompile vs OPTIMIZE FOR UNKNOWN
  4. Local Variables vs. Parameterized Stored Procedures in SQL Server

Best Answer

Following are the three ways that can fix the issue. More approaches are welcome.

Solution 1

Instead of a variable(@DelimitingCharacter), use a temporary table.

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

--And do a join

  INNER JOIN #VariableHoldingTable V
    ON 1 = 1

Execution Plan: https://www.brentozar.com/pastetheplan/?id=rJpehe5hQ

Solution 2

CREATE CLUSTERED INDEX CX_DXID on #MultipleDiagnosisDXs(DXID)

Execution Plan: https://www.brentozar.com/pastetheplan/?id=B1nKne9nX

Solution 3

OPTION (RECOMPILE)

Execution Plan: https://www.brentozar.com/pastetheplan/?id=BkRgalqnX

Related Question