Best way to delete large amount of records from the table

(0) Share

Report

Posted on by Johnny

6,478

Hello,

We have a regular table which will be used as a 'tmp' table.

After some processes it will hold around / more than 1m records.

At each new run records should be removed from the table.

Which approach would be best to develop? Just a "delete_from" statement, or we should connect with sql directly and pass the string with Truncate keyword?

Or any other options??

Thanks and regards

I have the same question (0)

All responses (1)

Answers (0)

Martin Dráb 237,959 Most Valuable Professional on at

Like (1)

Report

First of all, think carefully whether your design will work in multi-user environment. For example, I could delete all the data while you're using it.

delete_from is fine if it doesn't roll back to one-by-one processing. That happens when there is business logic involved, such as code in delete() method, or delete actions or database logging. If there are delete actions or code to maintain data consistency, skipping such logic would lead to data corruption. Therefore you should try to design your solution so you can safely delete the records without extra steps.

Using database log or alerts on such a table probably doesn't make a good sense, but you can explicitly skip them to be sure that any incorrect setup doesn't harm performance.

Was this reply helpful? Yes No