CODEWITHSUNDEEP
sqlsql-server
gopinath s
gopinath s

Reputation: 612

SQL server query plan

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?

Upvotes: 3

Views: 89

Answers (3)

Peter Barton
Peter Barton

Reputation: 587

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

Upvotes: 0

gopinath s
gopinath s

Reputation: 612

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 + '%'

Upvotes: 0

Jason W
Jason W

Reputation: 13179

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

Upvotes: 1

Related Questions