March 23, 2009 at 6:28 am
Hi Guys,
I have an online processing system which displays top level management information to users via the web, this information is an aggregate of backgroup ETL application which is running, so it constantly changes realtime.
The problem now is this table has grown to around 900m rows, most of the data are aggregates built up over time, but there isn’t any requirement to keep this going. Problem is deleting off the tables takes an absolute age to do, and has been left like this for some time, I am just thinking moving forwards.
What’s the best way to remove data off this table for now.
If a job is scheduled to auto delete it, what can happen is web queries running which will block the delete. Other thing is with the amount of data, I dont know how long the delete will take, if its taking too long, they we will need to cancel it, which will need time to rollback, so ideally I dont want to get into messy situations.
I am looking for ideas here.
Schema
========
CREATE TABLE [dbo].[TB_Sales_Profile](
[transactiondate] [datetime] NOT NULL,
[clientURN] [int] NULL,
[personurn] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[transactiontotal] [int] NOT NULL,
[transactioncost] [dbo].[numeric(10,2)] NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [INDEX_TB_Sales_Profile] ON [dbo].[TB_Sales_Profile]
(
[transactiondate] ASC,
[clientURN] ASC,
[personurn] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [INDEX_TB_Sales_Profile_REVERSE] ON [dbo].[TB_Sales_Profile]
(
[clientURN] ASC,
[personurn] ASC,
[transactiondate] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
Schema
March 23, 2009 at 7:17 am
You may want to work with a delete loop of smaller chuncks of your data.
Keep in mind your logfile may grow during this operation.
I don't know where I got this from, but the template still serves ..
-- Here's an example that you should be able to customize for your needs:
Declare @BatchSize int
Set @BatchSize = 5000 -- Modify as needed !!!
Set nocount on
declare @RowsDeleted bigint
Declare @MyRowcount bigint
set @RowsDeleted = 0
while 0 = 0
begin
DELETE top ( @BatchSize )
FROM
WHERE
set @MyRowcount = @@rowcount
if @MyRowcount = 0 break
select @RowsDeleted = @RowsDeleted + @MyRowcount
-- % = modulo
if 0.000 = @RowsDeleted % 100000.000 print convert(varchar(26), @RowsDeleted) + ' - ' + convert(varchar(26), getdate(),121)
end
Print '[' + cast(@RowsDeleted as varchar(15)) + '] Rows deleted.'
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 23, 2009 at 9:12 am
Toss an id on it or use the date and delete in 1 mm record chunks and loop and go into simple recovery mode if you can. I have do this on a smaller scale. The other option is just have it do the top million or two every day over time.
Don't forget to update your statistics on the table after each chunk and/or day.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply