deletescript for cleanup

twinsen.be

Hi everyone,

I'm writing a small script to clean up our database. We have a couple of databases which contain many gigabytes of data.

The script fills a few temp tables, with price-id's which can be deleted (based on a few rules). Then it deletes all related data first, before actually deleting the price records themselves.

This works fine, except for performance issues. After 12 hours, I had to cancel the running script as it was taking too many resources.

My question is, how can I increase performance Should I add 'commit' after deleting data in each table Would it help to make it a stored procedure

Script looks kinda like this:

SELECT priceId INTO #tobedeleted FROM prices WHERE ... (few rules)

DELETE FROM price_product WHERE priceId IN ... #tobedeleted

....

....

Vidhya Sagar


Delete from table where condition is a logged operation, so it depends on how much row in the table and how much role its going to delete, with respect to that the time will be taken.

To minimise the time check the recovery model of the table if its full or bulk logged then change it to simple and do the above operation. Once done you can change the recovery model as earlier.

Barry Andrew

twinsen.be wrote:

Hi everyone, I'm writing a small script to clean up our database. We have a couple of databases which contain many gigabytes of data. The script fills a few temp tables, with price-id's which can be deleted (based on a few rules). Then it deletes all related data first, before actually deleting the price records themselves. This works fine, except for performance issues. After 12 hours, I had to cancel the running script as it was taking too many resources. My question is, how can I increase performance Should I add 'commit' after deleting data in each table Would it help to make it a stored procedure Script looks kinda like this: SELECT priceId INTO #tobedeleted FROM prices WHERE ... (few rules) DELETE FROM price_product WHERE priceId IN ... #tobedeleted .... ....

Hi twinsen!

You will most definately get a massive performance increase if you re-write this as a stored procedure. You will be getting pre-compiled execution to name but one of the advantages, some of which are highlighted here;

First create your delete;
CREATE PROCEDURE stp_MyDelete
AS
DELETE FROM price_product WHERE priceId IN ... #tobedeleted;
GO

Then create your cleanup one, which will call the delete...

CRAETE PROCEDURE stp_MyCleanup
AS
SELECT
PriceId INTO #tobedeleted FROM prices WHERE ... (few rules);
GO
EXEC stp_MyDelete; GO

Now all you have to do is either call "EXEC stp_MyCleanup" or why not create this as a job and set up some alerts so you can monitor it

There is a lot you can do with sp's and they are actually a lot of fun once you get used to them.

hth
Barry

Florian Reischl

Hi twinsen

We also have databases with more than 100 GB (one > 1 TB). Due to the size of your databases you should work in chunks. If you want to delete 1 mio records in one step you work into risk to get huge LOCKS on your database.

My solution for cleanup scripts in our system was to work with two temp tables. First get a big chunk (e.g. 500.000) records which shuold be deleted into a temp table to avoid always to search from main tables by many criteria. After that you can delete in 10.000 record steps with a second temp table which gets its IDs from the main temp table. You should avoid big LOCKING problems and can work with transactions.

Here a small pseudy example:

Code Block

WHILE EXISTS (SELECT TOP 1 * FROM MainTable WHERE HasToBeDeleted = 1)

BEGIN

INSERT INTO #ManyToBeDeleted

-- The value 500.000 is just an excample, you have to evaluate

-- the highest value to avoid locks

SELECT TOP 500000 priceid FROM MainTable WHERE HasToBeDeleted = 1

WHILE EXISTS (SELECT TOP 1 * FROM #ManyToBeDeleted)

BEGIN

BEGIN TRANSACTION

-- 10.000 is just an example, evaluate your best chunk size

INSERT INTO #ToBeDeletedChunk

SELECT TOP 10000 priceid FROM #ManyToBeDeleted

DELETE OT FROM OtherTables OT JOIN #ToBeDeletedChunk TBDC ON OT.priceid = TBDC.priceid

DELETE MT FROM MainTable MT JOIN #ToBeDeletedChunk TBDC ON MT.priceid = TBDC.priceid

DELETE MTBD FROM #ManyToBeDeleted MTBD JOIN #ToBeDeletedChunk TBDC ON MTBD.priceid = TBDC.priceid

DELETE FROM #ToBeDeletedChunk

COMMIT TRANSACTION

END

END

You should also add indexes to the both temp tables.

Regards

Flo

twinsen.be

All good answers, but Florian's example is the best Thx!

Madhu K Nair

Barry Andrew wrote:

twinsen.be wrote: Hi everyone, I'm writing a small script to clean up our database. We have a couple of databases which contain many gigabytes of data. The script fills a few temp tables, with price-id's which can be deleted (based on a few rules). Then it deletes all related data first, before actually deleting the price records themselves. This works fine, except for performance issues. After 12 hours, I had to cancel the running script as it was taking too many resources. My question is, how can I increase performance Should I add 'commit' after deleting data in each table Would it help to make it a stored procedure Script looks kinda like this: SELECT priceId INTO #tobedeleted FROM prices WHERE ... (few rules) DELETE FROM price_product WHERE priceId IN ... #tobedeleted .... .... Hi twinsen! You will most definately get a massive performance increase if you re-write this as a stored procedure. You will be getting pre-compiled execution to name but one of the advantages, some of which are highlighted here; First create your delete; CREATE PROCEDURE stp_MyDelete AS DELETE FROM price_product WHERE priceId IN ... #tobedeleted; GO Then create your cleanup one, which will call the delete... CRAETE PROCEDURE stp_MyCleanup AS SELECT PriceId INTO #tobedeleted FROM prices WHERE ... (few rules); GO EXEC stp_MyDelete; GO Now all you have to do is either call "EXEC stp_MyCleanup" or why not create this as a job and set up some alerts so you can monitor it There is a lot you can do with sp's and they are actually a lot of fun once you get used to them. hth Barry

Barry,

I think you have mistaken... in the code mentioned , the scope of #tobedeleted table is only to stp_MyCleanup sp. stp_MyDelete will not be able to refer that.. it may be typo.. check the steps u mentioned

Madhu