Index recommendation

Last Post 29 May 2009 10:08 AM by trans53. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
trans53
New Member
New Member

--
29 May 2009 06:09 AM
Hi all, sorry for this long description.

i am creating a purge process for the old records and i have a question regarding clustered index for the table below.
it seems that

CREATE TABLE [dbo].[OldRecords](
[ID] [int] NOT NULL,
[OrderID] [char](12) NOT NULL,
[IsDeleted] [tinyint] NOT NULL CONSTRAINT [DF_OldRecords_IsDeleted] DEFAULT ((0)),
[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_OldRecords_CreateDate] DEFAULT (getdate()),
[DeletedDate] [datetime] NULL,
CONSTRAINT [PK_OldRecords] PRIMARY KEY CLUSTERED
(
[ID] ASC
))

Also, there are 2 non-clustered indexes defined on this table which are used:

One is defined based on IsDeleted column and another on DeletedDate.


The way the purge works is basically every day it grabs old id and orderid from the original table based on the delivery date for an order and inserts these records into OldRecords table.
So, yesterday for example it can insert records like:

ID OrderID IsDeleted CreateDate DeletedDate
1 DUF0 0 2009-05-27 15:26:58.280 NULL
4 LDYX0 0 2009-05-27 15:26:58.280 NULL

and today it will insert this data:


2 LDYWF 0 2009-05-28 15:26:58.280 NULL
3 DYUV 0 2009-05-28 15:26:58.280 NULL
5 LE2UX 0 2009-05-28 15:26:58.280 NULL

and the final result will be like:

ID OrderID IsDeleted CreateDate DeletedDate
1 DUF0 0 2009-05-27 15:26:58.280 NULL
2 LDYWF 0 2009-05-28 15:26:58.280 NULL
3 DYUV 0 2009-05-28 15:26:58.280 NULL
4 LDYX0 0 2009-05-27 15:26:58.280 NULL
5 LE2UX 0 2009-05-28 15:26:58.280 NULL

Here is where i am not sure if the Clustered index defined on OldRecords table is the correct way to do so since records (can be many of them) will be inserted randomly and will cause page splits.

The next step after the records inserted will be:


DECLARE @rowcount INT
@count INT
SET @rowcount = 5000

DECLARE @DelOldRecords Table (ID INT PRIMARY KEY,
OrderID CHAR(12))

WHILE 1 = 1

BEGIN

INSERT INTO @DelOldRecords
(ID,
OrderID)
SELECT TOP (@rowcount) TMP.ID, TMP.OrderID
FROM dbo.OldRecords TMP
WHERE TMP.IsDeleted = 0
ORDER BY TMP.ID -- I am doing this in order to get the oldest records first

-- delete records based on the ID column

DELETE O FROM dbo.Order O INNER JOIN @DelOldRecords TV ON O.ID = TV.ID


-- or delete records based on the

DELETE O FROM Authorize O INNER JOIN @DelOldRecords TV ON O.OrderID = TV.OrderID -- how bad this join will perform since there is no index for OrderID in table variable?


UPDATE TMP
SET TMP.IsDeleted = 1,
TMP.DeletedDate = GETDATE()
FROM dbo.OldRecords TMP
INNER JOIN @DelOldRecords TV
ON TMP.ID = TV.ID
AND TMP.IsDeleted = 0

SELECT @count = @@ROWCOUNT

DELETE FROM @DelOldRecords

IF @count < @rowcount BREAK
END

The OldRecords table also will be purged based on DeletedDate < getdate - 2 month


Can you please recommend the best way to handle indexes in this case?

Thanks
trans53
New Member
New Member

--
29 May 2009 10:08 AM
Sounds like a plan. Thank you
You are not authorized to post a reply.

Acceptable Use Policy