Rewrite Query for tuning.

From: Meher Malakapalli (mmalakapalli_at_cohesioninc.com)
Date: 04/21/04

Next message: Vinodk: "Re: Index Tuning Wizard problem"
Previous message: Michael, Cheng [MSFT]: "RE: Retrieving the server version in an ODS library..."
Next in thread: Ray Mond: "Re: Rewrite Query for tuning."
Reply: Ray Mond: "Re: Rewrite Query for tuning."
Messages sorted by: [ date ] [ thread ]

Date: Tue, 20 Apr 2004 21:30:48 -0700

I have the following Query which I am trying to tune for the past 3 days but
it does not seem to help me. The Query returns rows. I looked at the Query
plan and added some appropriate indexes which turned the scans into seeks.
However the time it takes to return the data does not come down. It takes 8
mts to return the data and even after adding the indexes I observe that the
time is > 6 mts. I am wondering if there is a need to rewrite this query to
bring the cost down. Can any
MVPs please suggest a way of rewriting the Query so that the indexes are
used and the the query cost is less.

Here is the Query:

SELECT Comp.Pseudo_Doms_ID,
tblSales.CompanyID,
tblSales.MonthOfSale,
DATEPART(Month, tblSales.MonthOfSale),
DATEPART(Year, tblSales.MonthOfSale),
tblProductLine.ProductLineID,
tblProductLine.ProductLineCode,
tblProductLine.ProductLineName,
SUM (tblSales.Quantity),
SUM (tblSales.NetSale)
FROM tblProduct
INNER JOIN tblProductLine
ON tblProduct.ProductLineID = tblProductLine.ProductLineID
INNER JOIN tblSales
ON tblProduct.ProductID = tblSales.ProductID
INNER JOIN tblBenefitRebateDetail
ON tblSales.CompanyID = tblBenefitRebateDetail.CompanyID
AND tblProductLine.ProductLineID =
tblBenefitRebateDetail.SourceProductLineID
INNER JOIN tblCompany Comp
ON tblSales.CompanyID = Comp.CompanyID
WHERE tblSales.MonthofSale <= '04/19/2004'
GROUP BY tblSales.CompanyID,
tblProductLine.ProductLineID,
tblProductLine.ProductLineCode,
tblProductLine.ProductLineName,
tblSales.MonthOfSale,
Comp.Pseudo_Doms_ID

The total count of all the rows in each table is as follows:

tblSales -->5251344
tblBenefitRebateDetail -->114553
tblCompany-->94562
tblProduct -->37904
tblProductLine-->175

The DDL is as follows:

CREATE TABLE [dbo].[tblProductLine] (
[ProductLineID] [int] IDENTITY (1, 1) NOT NULL ,
[ProductLineCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[ProductLineName] [varchar] (400) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[RegionalBusinessUnitID] [int] NOT NULL ,
[ProductLineFriendlyName] [varchar] (400) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[InsertDate] [datetime] NOT NULL ,
[InsertUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UpdateDate] [datetime] NULL ,
[UpdateUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecordStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblProductLine] WITH NOCHECK ADD
CONSTRAINT [PK_tblProductLine] PRIMARY KEY CLUSTERED
(
[ProductLineID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblProductLine] WITH NOCHECK ADD
CONSTRAINT [DF_tblProductLine_RegionalBusinessUnitID] DEFAULT (1) FOR
[RegionalBusinessUnitID]
GO

CREATE UNIQUE INDEX [IX_ProductLineCode] ON
[dbo].[tblProductLine]([ProductLineCode]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblProduct] (
[ProductID] [int] IDENTITY (1, 1) NOT NULL ,
[ProductCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ProductName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProductFriendlyName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ProductLineID] [int] NOT NULL ,
[NDP] [money] NULL ,
[NDPOverride] [money] NULL ,
[NDPCalc] [money] NULL ,
[SourceID] [int] NOT NULL ,
[ChannelTypeID] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProductTypeID] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SNRequired] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Excluded] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[InsertDate] [datetime] NULL ,
[UpdateDate] [datetime] NULL ,
[InsertUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UpdateUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecordStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblProduct] WITH NOCHECK ADD
CONSTRAINT [PK_tblProduct] PRIMARY KEY CLUSTERED
(
[ProductID],
[ProductLineID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblProduct] WITH NOCHECK ADD
CONSTRAINT [DF_tblProduct_ProductTypeID1] DEFAULT ('Y') FOR
[ChannelTypeID],
CONSTRAINT [DF_tblProduct_ProductTypeID_1] DEFAULT ('Y') FOR
[ProductTypeID],
CONSTRAINT [DF_tblProduct_SNRequired_1] DEFAULT ('Y') FOR [SNRequired],
CONSTRAINT [DF_tblProduct_Excluded_1] DEFAULT ('Y') FOR [Excluded]
GO

CREATE UNIQUE INDEX [tblProduct11] ON [dbo].[tblProduct]([ProductCode],
[ProductID], [ProductLineID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [tblProduct8] ON [dbo].[tblProduct]([ProductCode],
[RecordStatus], [ProductID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [tblProduct7] ON [dbo].[tblProduct]([SourceID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_tblProduct] ON [dbo].[tblProduct]([ProductID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE UNIQUE INDEX [IX_tblProduct_ProductCode] ON
[dbo].[tblProduct]([ProductCode]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [tblProduct12] ON [dbo].[tblProduct]([ProductLineID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblSales] (
[RowID] [int] IDENTITY (1, 1) NOT NULL ,
[Pseudo_Doms_ID] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[CompanyID] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
[MonthOfSale] [datetime] NOT NULL ,
[BatchID] [int] NULL ,
[SourceID] [int] NULL ,
[ReferenceID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Quantity] [int] NOT NULL ,
[NetSale] [money] NULL ,
[CycleDate] [datetime] NULL ,
[InsertDate] [datetime] NULL ,
[UpdateDate] [datetime] NULL ,
[InsertUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UpdateUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecordStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblSales] WITH NOCHECK ADD
CONSTRAINT [PK_tblSales] PRIMARY KEY CLUSTERED
(
[RowID],
[ProductID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblSales] WITH NOCHECK ADD
CONSTRAINT [DF_tblSales_SourceID] DEFAULT (5) FOR [SourceID]
GO

CREATE INDEX [tblSales2] ON [dbo].[tblSales]([SourceID], [CycleDate]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_Sales_ProductID] ON [dbo].[tblSales]([ProductID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [tblSales6] ON [dbo].[tblSales]([ProductID], [CompanyID],
[MonthOfSale]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblBenefitRebateDetail] (
[RowID] [int] IDENTITY (1, 1) NOT NULL ,
[Pseudo_Doms_ID] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[CompanyID] [int] NOT NULL ,
[IncentivePlanXrefCycle] [datetime] NOT NULL ,
[IncentivePlanXrefCycleMonth] [smallint] NOT NULL ,
[IncentivePlanXrefCycleYear] [smallint] NOT NULL ,
[ProgramID] [int] NOT NULL ,
[ProgramName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[TrackXrefID] [int] NOT NULL ,
[TrackXrefName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[IncentivePlanXrefID] [int] NOT NULL ,
[IncentivePlanXrefName] [varchar] (180) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RegionalBusinessUnitID] [int] NOT NULL ,
[SourceProductLineID] [int] NOT NULL ,
[AdjustmentDescription] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PossibleRebatePercentages] [varchar] (1024) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[RebateActivity] [money] NOT NULL ,
[RebateAmount] [money] NOT NULL ,
[CheckNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CheckDate] [datetime] NULL ,
[CheckStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PartnerCheckStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[RebateType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DisplayDetailID] [smallint] NULL ,
[UpdateDate] [datetime] NULL ,
[UpdateUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InsertDate] [datetime] NOT NULL ,
[InsertUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RecordStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX [tblBenefitRebateDetail1] ON
[dbo].[tblBenefitRebateDetail]([RebateAmount]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO

ALTER TABLE [dbo].[tblBenefitRebateDetail] WITH NOCHECK ADD
CONSTRAINT [DF_tblBenefitRebateDetail_PartnerCheckStatus] DEFAULT ('O') FOR
[PartnerCheckStatus],
CONSTRAINT [DF_tblBenefitRebateDetail_DisplayDetailID] DEFAULT (0) FOR
[DisplayDetailID]
GO

CREATE INDEX [tblBenefitRebateDetail2] ON
[dbo].[tblBenefitRebateDetail]([IncentivePlanXrefCycle],
[IncentivePlanXrefID], [RebateType], [Pseudo_Doms_ID], [CompanyID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_tblBenefitRebateDetail] ON
[dbo].[tblBenefitRebateDetail]([CompanyID], [SourceProductLineID]) WITH
FILLFACTOR = 90 ON [PRIMARY]

CREATE TABLE [dbo].[tblCompany] (
[CompanyID] [int] IDENTITY (1, 1) NOT NULL ,
[Pseudo_doms_id] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[CompanyName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CompanyNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OutletHQID] [int] NULL ,
[CompanyClassID] [int] NULL ,
[CountryID] [int] NOT NULL ,
[InsertDate] [datetime] NULL ,
[UpdateDate] [datetime] NULL ,
[InsertUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UpdateUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecordStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblCompany] WITH NOCHECK ADD
CONSTRAINT [PK_tblCompany] PRIMARY KEY CLUSTERED
(
[CompanyID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblCompany] WITH NOCHECK ADD
CONSTRAINT [DF_tblCompany_CountryID] DEFAULT (1) FOR [CountryID]
GO

CREATE UNIQUE INDEX [IDX_Pseudo_Doms_ID] ON
[dbo].[tblCompany]([Pseudo_doms_id]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [tblCompany8] ON [dbo].[tblCompany]([Pseudo_doms_id],
[CompanyID], [RecordStatus]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [tblCompany3] ON [dbo].[tblCompany]([CompanyID],
[Pseudo_doms_id]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

Any help in rewriting or tuning this is highly appreciated.

Thanks

Next message: Vinodk: "Re: Index Tuning Wizard problem"
Previous message: Michael, Cheng [MSFT]: "RE: Retrieving the server version in an ODS library..."
Next in thread: Ray Mond: "Re: Rewrite Query for tuning."
Reply: Ray Mond: "Re: Rewrite Query for tuning."
Messages sorted by: [ date ] [ thread ]

Relevant Pages

Query Performance
... I have the following Query which I am trying to tune it for the last week ... CONSTRAINT PRIMARY KEY CLUSTERED ...
(microsoft.public.dotnet.academic)
RE: udf in computed column
... > EmployeeTypeId INT NOT NULL, ... > CONSTRAINT PK_EmployeeTypes PRIMARY KEY NONCLUSTERED ... > EmployeeId INT NOT NULL, ... > Created DATETIME NOT NULL DEFAULT), ...
(microsoft.public.sqlserver.programming)
Holidays
... HolidayDescriptionId INT NOT NULL, ... Modified DATETIME NOT NULL DEFAULT), ... CONSTRAINT U_HolidayDescriptions_HolidayDescription UNIQUE NONCLUSTERED ... AbsenceTypeId INT NOT NULL, ...
(microsoft.public.sqlserver.programming)
Re: Holidays
... EmployeeTypeId INT NOT NULL, ... CONSTRAINT PK_EmployeeTypes PRIMARY KEY NONCLUSTERED ... EmployeeId INT NOT NULL, ... Created DATETIME NOT NULL DEFAULT), ...
(microsoft.public.sqlserver.programming)
Re: order by?
... query, and the ... >use tempdb ... >create table a (a int not null, b datetime not null) ...
(microsoft.public.sqlserver.programming)