delete statement takes time

[washaw]

Hello,

I am doing some old data archival, and one part of this process is deleting data from the tables after archiving 'em

I am facing one problem, deleting data from the tables is taking one heck of time and slows the whole process and takes much of the resources, what should I do to make the deletion process faster?

THanks,

 

[AlexCuse]

Can you show the delete query you're using?

If it is just "delete table from table" type of query (with no joins or criteria, just clear it all out) TRUNCATE may be an option.

Hope this helps,

Alex

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[washaw]

Truncate doesn't work for me, the tables have foreign key related and I am using inner joins to delete data from some of the tables

Thanks

 

[memdiscount]

Hi;

In this case you might to look into some of the main index which is used in joins, you might need to create couple of indexes on these columns and it would help the delete process. It requires some study of your all DML queries before creating indexes, but it would be worth while doing a test by creating one or two indexes on joining columns, if they are not primary keys.

Thanks

 

[washaw]

The indexes are in place, and they helped a little bit, I am thinking of isolating this process and not creating a log file after deletion; log files are taking much of the resource,

what isolation level do you suggest ans what should I do to prevent the delete process creating a log file

Thanks

 

[k01]

I recommend you check sqlserver log if the database log file is required to be expanded before that.

If it is required, it is better to re-configure the database log file size to avoid auto expanse.



koichi

http://aboutabi.blogspot.com/

 

[pjw001]

It may be the foreign keys that are causing the problem.

If you delete a row from a table that is referred to by a foreign key, then the table that has the foreign key must be searched to ensure that the foreign key constraint will not be broken by the delete.

For example, if you delete a row from the main product table it may be that a number of potentially large transaction history tables will need to be searched to ensure that there are no references to the product.

 

[SQLSister]

You can also usually improve performance in large deletes by wotking in batches. There's an FAQ about that somewhere around here.

"NOTHING is more important in a database than integrity." ESquared