Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

delete statement takes time

Status
Not open for further replies.

washaw

Programmer
Feb 13, 2008
48
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,

 
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
 
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
 
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

 
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
 
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
 
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.
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top