[Solved] Inconsistent results with SQL Server Execution plan when using variables
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

Inconsistent results with SQL Server Execution plan when using variables

Database type:

I have a table with 5 million records as follows

Id          BasePrefix  DestPrefix  ExchangeSetId   ClassId
11643987    0257016         57016           1           3
11643988    0257016         57278           1           3
11643989    0257016         57279           1           3
11643990    0257016         57751           1           3

SQL Tuning adviser recomended the following index

CREATE NONCLUSTERED INDEX [ExchangeIdx] ON [dbo].[Exchanges]
(
    [ExchangeSetId] ASC,
    [BasePrefix] ASC,
    [DestPrefix] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

However given the following

DECLARE @exchangeSetID int = 1;
DECLARE @BasePrefix nvarchar( 10 ) = '0732056456';
DECLARE @DestPrefix nvarchar( 10 ) = '30336456';
DECLARE @BaseLeft nvarchar( 10 ) = left(@BasePrefix,4);

These 2 queries give me vastly different execution plans

Query 1

SELECT TOP 1 ClassId
  FROM Exchanges
  WHERE
  exchangeSetID = @exchangeSetID
   AND BasePrefix LIKE '0732' + '%'
   AND '0732056456' LIKE BasePrefix + '%'
   AND '30336456' LIKE DestPrefix + '%';

enter image description here

Query 2

SELECT TOP 1 ClassId
  FROM Exchanges
  WHERE
  exchangeSetID = @exchangeSetID
   AND BasePrefix LIKE @BaseLeft + '%'
   AND @BasePrefix LIKE BasePrefix + '%'
   AND @DestPrefix LIKE DestPrefix + '%';

enter image description here

The difference between the 2 queries are @BaseLeft and '0732' respectively

Basically, in the first example the index is used, and in the second, not so much

Is there any compelling reason why this should be so?

And if this is not just a fundamental flaw in my thinking, how could i pass a variable to the second query and make use of the index?

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid LIKE Searches With Leading Wildcard (query line: 7): The database will not use an index when using like searches with a leading wildcard (e.g. '%'). Although it's not always a satisfactory solution, please consider using prefix-match LIKE patterns (e.g. 'TERM%').
  2. Avoid LIKE Searches With Leading Wildcard (query line: 8): The database will not use an index when using like searches with a leading wildcard (e.g. '%'). Although it's not always a satisfactory solution, please consider using prefix-match LIKE patterns (e.g. 'TERM%').
  3. Avoid LIKE Searches With Leading Wildcard (query line: 9): The database will not use an index when using like searches with a leading wildcard (e.g. '%'). Although it's not always a satisfactory solution, please consider using prefix-match LIKE patterns (e.g. 'TERM%').
  4. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
  5. Use Numeric Column Types For Numeric Values (query line: 7): Referencing a numeric value (e.g. 0732) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
  6. Use Numeric Column Types For Numeric Values (query line: 8): Referencing a numeric value (e.g. 0732056456) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
  7. Use Numeric Column Types For Numeric Values (query line: 9): Referencing a numeric value (e.g. 30336456) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
Optimal indexes for this query:
CREATE INDEX exchanges_idx_exchangesetid ON Exchanges (exchangeSetID);
The optimized query:
SELECT
        TOP 1 Exchanges.ClassId 
    FROM
        Exchanges 
    WHERE
        Exchanges.exchangeSetID = @exchangeSetID 
        AND Exchanges.BasePrefix LIKE '0732' + '%' 
        AND '0732056456' LIKE Exchanges.BasePrefix + '%' 
        AND '30336456' LIKE Exchanges.DestPrefix + '%'

Related Articles



* original question posted on StackOverflow here.