I have following two query approaches. The Approach 1 works in 20 seconds. The Approach 2 did not complete even after hours.
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
Note: The above two are simplified queries. The actual queries can be seen in the execution plan below.
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)
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)?
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.
- SQL Server Query: Fast with Literal but Slow with Variable
- Improving query performance with OPTION (RECOMPILE), Constant Folding and avoiding Parameter Sniffing issues
- Parameter Sniffing vs VARIABLES vs Recompile vs OPTIMIZE FOR UNKNOWN
- Local Variables vs. Parameterized Stored Procedures in SQL Server