January 13, 2011 at 6:55 am
I have one table which contains 75 million records. The table structure are as follows:
---------------------------------------------------------------------------------------------------
/****** Object: Table [dbo].[BarcodeActivity] Script Date: 01/13/2011 19:08:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BarcodeActivity](
[PKID] [int] IDENTITY(1,1) NOT NULL,
[Barcode] [varchar](100) NULL,
[OrderPkid] [int] NULL,
[CustomerId] [int] NULL,
[PostDate] [datetime] NULL,
[ActivityType] [varchar](3) NULL,
[ActivityDateTime] [datetime] NULL,
[Pieces] [int] NULL,
[Manual] [varchar](1) NULL,
[WorkerId] [int] NULL,
[Location] [varchar](3) NULL,
[Destination] [varchar](3) NULL,
[SyncId] [int] NULL,
[Pallet] [varchar](100) NULL,
[Description] [varchar](10) NULL,
[Cancelled] [varchar](1) NULL,
[Created] [datetime] NULL,
[CreatedBy] [varchar](50) NULL,
[ExportBatchId] [int] NULL,
[LastUpdatedBy] [varchar](50) NULL,
[LastUpdatedDateTime] [datetime] NULL,
CONSTRAINT [PK_BarcodeActivity] PRIMARY KEY CLUSTERED
(
[PKID] 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
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'CustId needed for scans that may not be tied to an order, like a receive scan or dock scan.
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BarcodeActivity', @level2type=N'COLUMN',@level2name=N'CustomerId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Pieces should always be 1 unless we allow the driver to put in a number of manual pieces.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BarcodeActivity', @level2type=N'COLUMN',@level2name=N'Pieces'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Location needed - either branch or customer location. Use 3 digit identitfier' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BarcodeActivity', @level2type=N'COLUMN',@level2name=N'Location'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Destination needed for Dock scans (could be our branch but not necessarily)
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BarcodeActivity', @level2type=N'COLUMN',@level2name=N'Destination'
GO
ALTER TABLE [dbo].[BarcodeActivity] WITH CHECK ADD CONSTRAINT [FK_BarcodeActivity_Orders] FOREIGN KEY([OrderPkid])
REFERENCES [dbo].[Orders] ([PKID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[BarcodeActivity] CHECK CONSTRAINT [FK_BarcodeActivity_Orders]
GO---------------------------------------------------------------------------------------------------
On this table I have created 8 indexes which are listed below:
/****** Object: Index [PK_BarcodeActivity] Script Date: 01/13/2011 19:15:56 ******/
ALTER TABLE [dbo].[BarcodeActivity] ADD CONSTRAINT [PK_BarcodeActivity] PRIMARY KEY CLUSTERED
(
[PKID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_OrderPkid] Script Date: 01/13/2011 19:16:12 ******/
CREATE NONCLUSTERED INDEX [IX_OrderPkid] ON [dbo].[BarcodeActivity]
(
[OrderPkid] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_BarcodeActivity_Cancelled] Script Date: 01/13/2011 19:16:26 ******/
CREATE NONCLUSTERED INDEX [IX_BarcodeActivity_Cancelled] ON [dbo].[BarcodeActivity]
(
[Cancelled] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_BarcodeActivity_Barcode] Script Date: 01/13/2011 19:16:54 ******/
CREATE NONCLUSTERED INDEX [IX_BarcodeActivity_Barcode] ON [dbo].[BarcodeActivity]
(
[Barcode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_ActivityType_Barcode_Order] Script Date: 01/13/2011 19:17:06 ******/
CREATE NONCLUSTERED INDEX [IX_ActivityType_Barcode_Order] ON [dbo].[BarcodeActivity]
(
[ActivityType] ASC,
[OrderPkid] ASC
)
INCLUDE ( [Barcode],
[Pieces]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_ActivityType_Barcode] Script Date: 01/13/2011 19:17:16 ******/
CREATE NONCLUSTERED INDEX [IX_ActivityType_Barcode] ON [dbo].[BarcodeActivity]
(
[CustomerId] DESC,
[ActivityType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_ActivityType] Script Date: 01/13/2011 19:17:28 ******/
CREATE NONCLUSTERED INDEX [IX_ActivityType] ON [dbo].[BarcodeActivity]
(
[ActivityType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_ActivityDateTime] Script Date: 01/13/2011 19:17:39 ******/
CREATE NONCLUSTERED INDEX [IX_ActivityDateTime] ON [dbo].[BarcodeActivity]
(
[ActivityDateTime] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO-------------------------------------------------------------------------------------------------------------------------
Now on this table if I execute the below mentioned SQL, it takes 30 second to execute and will bring approx 4500 records.
SELECT DISTINCT
b.[OrderPkid],b.[Barcode],
COALESCE(imp.[ActivityType],'') AS 'Expected', COALESCE(imp.[Pieces],0) AS 'ImpPieces',
rs.[ActivityDateTime] AS 'RSActTime',COALESCE(rs.[Pieces],0) AS 'RSPieces',COALESCE(rs.[Description],'') AS 'RSDesc',
ls.[ActivityDateTime] AS 'LSActTime',COALESCE(ls.[Pieces],0) AS 'LSPieces',COALESCE(ls.[Description],'') AS 'LSDesc',
ds.[ActivityDateTime] AS 'DSActTime',COALESCE(ds.[Pieces],0) AS 'DSPieces',COALESCE(ds.[Description],'') AS 'DSDesc'
FROM [BarcodeActivity] b
LEFT JOIN [BarcodeActivity] imp ON
((b.[OrderPkid] = imp.[OrderPkid] AND b.[Barcode] = imp.[Barcode]) AND imp.[ActivityType] = 'IMP')
LEFT JOIN [BarcodeActivity] rs ON
(((b.[OrderPkid] = rs.[OrderPkid] AND b.[Barcode] = rs.[Barcode]) AND
(((rs.[ActivityType] = 'RS' OR rs.[ActivityType] = 'RSP') OR rs.[ActivityType] = 'PRS') OR
rs.[ActivityType] = 'MSR')) AND rs.[Cancelled] <> 'Y')
LEFT JOIN [BarcodeActivity] ls ON
(((b.[OrderPkid] = ls.[OrderPkid] AND b.[Barcode] = ls.[Barcode]) AND
(((ls.[ActivityType] = 'LS' OR ls.[ActivityType] = 'LSP') OR ls.[ActivityType] = 'PLS') OR
ls.[ActivityType] = 'MSL')) AND ls.[Cancelled] <> 'Y')
LEFT JOIN [BarcodeActivity] ds ON
(((b.[OrderPkid] = ds.[OrderPkid] AND b.[Barcode] = ds.[Barcode]) AND
((((ds.[ActivityType] = 'DS' OR ds.[ActivityType] = 'DSP') OR ds.[ActivityType] = 'PDS') OR
ds.[ActivityType] = 'MSD') OR ds.[ActivityType] = 'MIS')) AND ds.[Cancelled] <> 'Y')
WHERE b.[ActivityType] IN ('IMP','RS','RSP','PRS','LS','LSP','PLS','DS','DSP','PDS','MSR','MSL','MSD','MIS') AND
b.[OrderPkid] IN
(SELECT o.[PKID]
FROM [Orders] o
WHERE DATEADD(dd, 0, DATEDIFF(dd, 0,o.[PostDate])) = '13-Jan-2011' AND o.[CustomerId] = 171184)
------------------------------------------------------------------------------------------------------------------------------
Buf if I just remove the below mentioned 3 fields from select statement then execution time is reduced from 30 to 10 seconds.
COALESCE(rs.[Description],'') AS 'RSDesc', COALESCE(ls.[Description],'') AS 'LSDesc', COALESCE(ds.[Description],'') AS 'DSDesc'
But still I need these 3 fields and need to reduce execution time from 30 seconds to minimum 15 seconds.
Please suggest.
I am using SQL Server 2008 on Window Server 2008.
Thanks in advance.
Regards,
--------------------------------------------------------------------------------
Mayank Parmar
Software Engineer
Clarion Technologies
SEI CMMI Level 3 Company
8th Floor, 803, GNFC info Tower,
SG Highway, Ahmedabad - 380 054,
Gujarat, India.
www.clariontechnologies.co.in
Email: mayank.parmar@clariontechnologies.co.in
MSN : mayank.parmar@clariontechnologies.co.in
Mobile: +91 9727748789
--------------------------------------------------------------------------------
January 13, 2011 at 7:39 am
you've got a few things killing you, without the actual execution plan , these are jsut guesses:
the WHERE statement on ORDERS is not SARG-able.
instead of doing WHERE DATEADD(dd, 0, DATEDIFF(dd, 0,o.[PostDate])) = The hardcodeddate,
change it to this:
WHERE o.[PostDate] BETWEEN @HardCodedDate and DATEADD(ms,-3,@HardCodedDate +1)
if you are going to select the descriptions, you might want to alter one of the indexes to INCLUDE Desrip to speed things up.
all the OR statements are requring table scans on each of the tables...
I'd try putting an index on [BarcodeActivity].[ActivityType], and changing the joins to be CTE's, then joining only on two fields:
DECLARE @HardCodedDate datetime,
@CustomerId int
SET @HardCodedDate = '13-Jan-2011'
SET @CustomerId = 171184
;WITH imp AS
(
SELECT [OrderPkid],[Barcode],[ActivityType]
FROM [BarcodeActivity]
WHERE [ActivityType] = 'IMP'
AND [CustomerId] = @CustomerId
),
rs AS
(
SELECT [OrderPkid],[Barcode],[ActivityType]
FROM [BarcodeActivity]
WHERE [ActivityType] IN('RS','RSP','PRS','MSR')
AND [CustomerId] = @CustomerId
AND [Cancelled] <> 'Y'
),
ls AS
(
SELECT [OrderPkid],[Barcode],[ActivityType]
FROM [BarcodeActivity]
WHERE [ActivityType] IN('LS','LSP','PLS','MSL')
AND [CustomerId] = @CustomerId
AND [Cancelled] <> 'Y'
),
ds AS
(
SELECT [OrderPkid],[Barcode],[ActivityType]
FROM [BarcodeActivity]
WHERE [ActivityType] IN('DS','DSP','PDS','MSD','MIS')
AND [CustomerId] = @CustomerId
AND [Cancelled] <> 'Y'
)
SELECT
b.[OrderPkid],
b.[Barcode],
COALESCE(imp.[ActivityType],'') AS 'Expected',
COALESCE(imp.[Pieces],0) AS 'ImpPieces',
rs.[ActivityDateTime] AS 'RSActTime',
COALESCE(rs.[Pieces],0) AS 'RSPieces',
COALESCE(rs.[Description],'') AS 'RSDesc',
ls.[ActivityDateTime] AS 'LSActTime',
COALESCE(ls.[Pieces],0) AS 'LSPieces',
COALESCE(ls.[Description],'') AS 'LSDesc',
ds.[ActivityDateTime] AS 'DSActTime',
COALESCE(ds.[Pieces],0) AS 'DSPieces',
COALESCE(ds.[Description],'') AS 'DSDesc'
FROM [BarcodeActivity] b
LEFT JOIN [BarcodeActivity] imp
ON b.[OrderPkid] = imp.[OrderPkid]
AND b.[Barcode] = imp.[Barcode]
LEFT JOIN [BarcodeActivity] rs
ON b.[OrderPkid] = rs.[OrderPkid]
AND b.[Barcode] = rs.[Barcode]
LEFT JOIN [BarcodeActivity] ls
ON b.[OrderPkid] = ls.[OrderPkid]
AND b.[Barcode] = ls.[Barcode]
LEFT JOIN [BarcodeActivity] ds
ON b.[OrderPkid] = ds.[OrderPkid]
AND b.[Barcode] = ds.[Barcode]
WHERE b.[ActivityType] IN ('IMP','RS','RSP','PRS','LS','LSP','PLS','DS','DSP','PDS','MSR','MSL','MSD','MIS')
AND o.[PostDate] BETWEEN @HardCodedDate and DATEADD(ms,-3,@HardCodedDate +1)
AND [CustomerId] = @CustomerId
Lowell
January 13, 2011 at 7:42 am
adding the same WHERE statement for the ORDERPK to each of the CTE's might speed things up also; you be filtering to jsut the rows for the order in question....
Lowell
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply