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

Approach 1

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

Approach 2

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

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

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


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.


  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
  • I am the only one using this dev server now. I am not running any other queries. Also, the behavior is repeated test after test.
    – LCJ
    Oct 25, 2018 at 21:32
  • I tried sp_executesql - but that didn't resolve the issue. It is still not completed even after long time.
    – LCJ
    Oct 26, 2018 at 18:26

2 Answers 2


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

--And do a join

  INNER JOIN #VariableHoldingTable V
    ON 1 = 1

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

Solution 2


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

Solution 3


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

CREATE TABLE #MultipleDiagnosisDXs (DXID INT Not null)

Notice not null here.After populating create index.

Create Clustered index IX_DXID on #MultipleDiagnosisDXs(DXID)

In query correct,


No need of order by clause

Above change is must.

You should once try EXEC sys.sp_executesql .

You should also try Row_Number once,with this you do not need second Select statement and no need of temp table.

Do not use "*".

Just try With (Nolock) hint: If it help then we discuss more about it.It is not always considered to be best practice, but try once.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.