CODEWITHSUNDEEP
sql-serversql-server-2008
blogbydev
blogbydev

Reputation: 1485

Different execution plan for a different parameter value

Query 1 runs fast and uses Parallel execution plan

SELECT c.[Date]
FROM Table1 c
left join Table2 bec on bec.Col1ID = c.Col1ID and bec.Active = 1
WHERE c.Active = 1
AND (c.Col2ID not in (3,4,5,6,7,8,9,10) or c.Col2ID is null) 
and (c.[Date] >= '06/12/2014 02:30:00.000 PM')

Query 2 takes longer and uses Normal(Serial) execution plan

SELECT c.[Date]
FROM Table1 c
left join Table2 bec on bec.Col1ID = c.Col1ID and bec.Active = 1
WHERE c.Active = 1
AND (c.Col2ID not in (3,4,5,6,7,8,9,10) or c.Col2ID is null) 
and (c.[Date] >= '06/15/2014 02:30:00.000 PM')

Question:

  1. Query2 is trying to get a subset data of Query1, so Query2 should be faster.
  2. Both the Queries differ only by the parameter value, so why completely different execution plans.

Info about the Server: This is running in SQL Server 2008

Table Structure is as follows:

TABLE Table1(
       Col1Id [int] IDENTITY(1,1) NOT NULL,
       Col2Id [int] NULL,
       Col3 [int] NOT NULL,
       Col4 [int] NULL,
       Active [bit] NOT NULL
       [Date] [datetime] NOT NULL)

Index on Table1 Non Clustered on (Active, Date)

TABLE Table2(
       [Col] [int] NOT NULL,
       Col1ID [int] NOT NULL,
       [Col2] [int] NOT NULL,
       [Col3] [datetime] NOT NULL,
       [Col4] [int] NOT NULL,
       [Col5] [datetime] NULL,
       [Col6] [int] NULL,
       [Active] [bit] NULL)

Index on Table2 Non Clustered on [Active] Included (Col, Col1Id) Clustered on (Col, Col1ID)

Any help on this is welcome.

Upvotes: 0

Views: 84

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46203

Try adding included columns to the Table1 index and create a new index on Table2. Also, make sure statistics are up-to-date.

CREATE INDEX idx_Table1_Active_Date ON dbo.Table1 (Active, Date) INCLUDE (Col1Id, Col2Id);
CREATE INDEX idx_Table2_Col1ID_Active ON dbo.Table2 (Col1ID, Active);

Upvotes: 1

Related Questions