November 30, 2010 at 7:45 am
[font="Verdana"]Hi All,
We have one huge table with us which has millions of records of which we need to DELETE some records, i.e. out of approx 53 million records we are going to delete approx 27 million records. Previously we used to delete these records logically and update the IsDeleted flag to 1 [DELETED]. Now as table size is highly increasing we are planning to remove such records physically from the table, it’s a business need. That is apart.
The main goal here is to remove 27 million, and that we have planned to do through SSIS package. Going forward we have to remove these records in multiple batches with 25K records each at one go.
Hopes I have explained the whole stuff clearly. Please do let me know, does anybody want more information on this.
Any help is appreciated.
Thanks in advance,
-- Mahesh Bote
[/font]
MH-09-AM-8694
December 2, 2010 at 5:55 am
This was removed by the editor as SPAM
December 3, 2010 at 5:13 am
Are you sure you need an SSIS package?
Could you use something like this in T-SQL (the variable's not strictly necessary, just makes it easier to change):
DECLARE @batchSize INT
SET @batchSize=25000
WHILE 1=1
BEGIN
DELETE TOP (@batchSize) FROM MyTableName
WHERE IsDeleted = 1
IF @@ROWCOUNT < @batchSize BREAK
END
December 3, 2010 at 5:27 am
If it does need to happen in SSIS, delete via the Execute SQL Task in Control Flow. Don't use the OLE DB SQL Component in the data flow task. As was previously mentioned, that works on a row-by-row basis and is incredibly slow.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply