lundi 29 février 2016

Optimization for Date Correlation doesn’t change plan

I have a reporting requirement from the following tables. I created a new database with these tables and imported data from the live database for reporting purpose.

The report parameter is a date range. I read the following and found that DATE_CORRELATION_OPTIMIZATION can be used to make the query work faster by utilizing seek instead of scan. I made the required settings – still the query is using same old plan and same execution time. What additional changes need to be made to make the query utilize the date correlation?

Note: I am using SQL Server 2005

REFERENCES

  1. Optimizing Queries That Access Correlated datetime Columns
  2. The Query Optimizer: Date Correlation Optimisation

SQL

--Database change made for date correlation
ALTER DATABASE BISourcingTest
   SET DATE_CORRELATION_OPTIMIZATION ON;
GO

--Settings made
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
GO

--Test Setting
IF (  (sessionproperty('ANSI_NULLS') = 1) AND
      (sessionproperty('ANSI_PADDING') = 1) AND 
      (sessionproperty('ANSI_WARNINGS') = 1) AND 
      (sessionproperty('ARITHABORT') = 1) AND 
      (sessionproperty('CONCAT_NULL_YIELDS_NULL') = 1) AND 
      (sessionproperty('QUOTED_IDENTIFIER') = 1) AND 
      (sessionproperty('NUMERIC_ROUNDABORT') = 0)  
    )
   PRINT 'Everything is set'
ELSE
   PRINT 'Different Setting'

--Query
SELECT C.ContainerID, C.CreatedOnDate,OLIC.OrderID
FROM ContainersTest C
INNER JOIN OrderLineItemContainers OLIC
    ON OLIC.ContainerID = C.ContainerID
WHERE C.CreatedOnDate > '1/1/2015'
AND C.CreatedOnDate < '2/01/2015'

TABLES

CREATE TABLE [dbo].[ContainersTest](
    [ContainerID] [varchar](20) NOT NULL,
    [Weight] [decimal](9, 2) NOT NULL DEFAULT ((0)),
    [CreatedOnDate] [datetime] NOT NULL DEFAULT (getdate()),
 CONSTRAINT [XPKContainersTest] PRIMARY KEY CLUSTERED 
(
    [CreatedOnDate] ASC,
    [ContainerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[OrderLineItemContainers](
    [OrderID] [int] NOT NULL,
    [LineItemID] [int] NOT NULL,
    [ContainerID] [varchar](20) NOT NULL,
    [CreatedOnDate] [datetime] NOT NULL DEFAULT (getdate()),
 CONSTRAINT [PK_POLineItemContainers] PRIMARY KEY CLUSTERED 
(
    [OrderID] ASC,
    [LineItemID] ASC,
    [ContainerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_OrderLineItemContainers] UNIQUE NONCLUSTERED 
(
    [ContainerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[OrderLineItemContainers]  WITH CHECK ADD  CONSTRAINT [FK_POLineItemContainers_Containers] FOREIGN KEY([ContainerID])
REFERENCES [dbo].[Containers] ([ContainerID])
GO
ALTER TABLE [dbo].[OrderLineItemContainers] CHECK CONSTRAINT [FK_POLineItemContainers_Containers]

Plan enter image description here

--

Aucun commentaire:

Enregistrer un commentaire