A sql question about table purging

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

A sql question about table purging

Author

Topic

Albert.Wang.Ca
Starting Member

3 Posts

Posted - 2007-06-08 : 15:01:08

I have a table with columns "UserID, Description, ReceivedDate", there would be 10,000 users using this table, at end of the year I want to purge the table and leave the last record inputed for each user, Is there any better way to do it? currently I go through each user one by one, it works but quite slow. Any suggestion is much appreciated!

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-08 : 15:24:49

Is there any unique column in the table?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Albert.Wang.Ca
Starting Member

3 Posts

Posted - 2007-06-08 : 17:09:20

No unique column, thanks

Albert.Wang.Ca
Starting Member

3 Posts

Posted - 2007-06-08 : 17:14:34

Somebody helped me with the following statement, it's still a bit slow for total 200,000 records in that table for 8,000 users, but not too bad. :)

DELETE TransactionHistory
FROM TransactionHistory LEFT OUTER JOIN
(SELECT UserID, MAX(ReceivedDate) AS LastDate FROM TransactionHistory GROUP BY UserID)AS Temp ON Services.UserID= Temp.UserID
WHERE Services.ReceivedDate <> Temp.LastDate

Kristen
Test

22859 Posts

Posted - 2007-06-09 : 04:27:30

Probably no faster but you should be able to do:


DELETE D
FROM TransactionHistory AS D
     JOIN TransactionHistory AS TH2
          ON TH2.UserID = D.UserID
         AND TH2.ReceivedDate > D.ReceivedDate

an alternative (particularly if the number of records to KEEP is less than, say, 10% of the total us to save the "records to keep" to a temporary table, TRUNCATE the live table, then insert-back the records from Temp Table


BEGIN TRANSACTION
SELECT TH1.*
INTO #TEMP
FROM TransactionHistory AS TH1
     JOIN
     (
          SELECT UserID, MAX(ReceivedDate) AS LastDate
          FROM   TransactionHistory
          GROUP BY UserID
     ) AS TEMP
          ON TEMP.UserID = TH1.UserID
         AND TEMP.LastDate = TH1.ReceivedDate
IF @@ERROR <> 0 GOTO MyError

TRUNCATE TABLE TransactionHistory
IF @@ERROR <> 0 GOTO MyError

INSERT INTO TransactionHistory
SELECT * FROM #TEMP
IF @@ERROR <> 0 GOTO MyError

COMMIT
GOTO MyExit

MyError:
ROLLBACK

MyExit:
PRINT 'Done'

Backup First!!!

Kristen

IronTiger
Starting Member

4 Posts

Posted - 2009-01-17 : 17:22:18

The fastest way to save records in a big table like this is to create a temp table to store the records to be saved.

INSERT INTO TEMPTBL (
SELECT Records you want to keep)

TRUNCATE TransactionHistory

INSERT INTO TransactionHistory (
SELECT * from TEMPTBL )

This is much more efficient as you won't have to log all of the deletes. This also assumes that you are deleting a lot more records than you are keeping. As all of the inserts will need to be logged.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources