SQL server query plan

Question

I have 3 tables as listed below

CREATE TABLE dbo.RootTransaction
    (
    TransactionID int CONSTRAINT [PK_RootTransaction] PRIMARY KEY NONCLUSTERED (TransactionID ASC)
    )

GO
----------------------------------------------------------------------------------------------------

CREATE TABLE [dbo].[OrderDetails](
    [OrderID] int identity(1,1) not null,
    TransactionID int,
    OrderDate datetime,
    [Status] varchar(50)
 CONSTRAINT [PK_OrderDetails] PRIMARY KEY CLUSTERED ([OrderID] ASC),
 CONSTRAINT [FK_TransactionID] FOREIGN KEY ([TransactionID]) REFERENCES [dbo].[RootTransaction] ([TransactionID]),
) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [ix_OrderDetails_TransactionID]
    ON [dbo].[OrderDetails](TransactionID ASC, [OrderID] ASC);

GO
----------------------------------------------------------------------------------------------------
CREATE TABLE dbo.OrderItems
(
    ItemID int identity(1,1) not null,
    [OrderID] int,
    [Name]  VARCHAR (50)  NOT NULL,
    [Code]  VARCHAR (9)   NULL, 
    CONSTRAINT [PK_OrderItems] PRIMARY KEY NONCLUSTERED ([ItemID] ASC),
    CONSTRAINT [FK_OrderID] FOREIGN KEY ([OrderID]) REFERENCES [dbo].[OrderDetails] ([OrderID])
)
Go
CREATE CLUSTERED INDEX OrderItems
    ON [dbo].OrderItems([OrderID] ASC, ItemID ASC) WITH (FILLFACTOR = 90);
GO
CREATE NONCLUSTERED INDEX [IX_Code]
    ON [dbo].[OrderItems]([Code] ASC) WITH (FILLFACTOR = 90)
----------------------------------------------------------------------------------------------------

Populated sample data in each table
select COUNT(*) from RootTransaction -- 45851
select COUNT(*) from [OrderDetails] -- 50201
select COUNT(*) from OrderItems --63850
-- Query 1
SELECT  o.TransactionID 
FROM [OrderDetails] o
JOIN dbo.OrderItems i ON o.OrderID = i.OrderID
WHERE i.Code like '1067461841%'


declare @SearchKeyword  varchar(200) = '1067461841'

-- Query 2
SELECT  o.TransactionID 
FROM [OrderDetails] o
JOIN dbo.OrderItems i ON o.OrderID = i.OrderID
WHERE i.Code like @SearchKeyword + '%'

When running above 2 queries, I could see Query 1 use index seek on OrderDetails, OrderItems which is expected, However in query 2, query plan use index seek on OrderItems but index scan on OrderDetails. Only difference in two queries is using direct value vs variable in LIKE and both returns same result. why the query execution plan change between using direct value vs variable?

Parameter sniffing maybe? Type both queries with `OPTION(RECOMPILE)`. — Felix Pamittan, Feb 18 '15 at 01:06

score 1 · Answer 1 · answered Feb 18 '15 at 01:32

I believe the issue is most likely explained through parameter sniffing. SQL Server often identifies and caches query plans for commonly used queries. As part of this caching, it "sniffs" the parameters you use on the most common queries to optimize the creation of the plan.

Query 1 shows a direct string, so SQL creates a specific plan. Query 2 uses an intermediate variable, which is one of the techniques that actually prevents parameter sniffing (often used to provide more predictable performance to stored procs or queries where the parameters have significant variance. These are considered 2 completely different queries to SQL despite the obvious similarities. The observed differences are essentially just optimization.

Furthermore, if your tables had different distributions of row counts, you'd likely potential differences from those 2 scenarios based on existing indexes and potential optimizations. On my server with no sample data loaded, the query 1 and query 2 had same execution plans since the optimizer couldn't find any better paths for the parameters.

For more info: http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx

score 0 · Answer 2 · answered Feb 18 '15 at 02:58

0

Below queries show similar plan though WHERE clause is different.

select Code from OrderItems WHERE Code like '6662225%'

declare @SearchKeyword  varchar(200) = '6662225'

select Code from OrderItems WHERE Code like @SearchKeyword + '%'

answered Feb 18 '15 at 02:58

gopinath s

612
6
5

score 0 · Answer 3 · edited May 23 '17 at 12:12

0

The following post/answers offer a good explanation as to why performance is better with hard coded constants than variables, along with a few suggestions you could possibly try out:

Alternative to using local variables in a where clause

edited May 23 '17 at 12:12

Community

1
1

answered Feb 18 '15 at 05:14

Peter Barton

577
1
4
12

SQL server query plan

3 Answers3