0

I have multiple staging tables containing data that needs to be moved to production tables. The process of moving this data is done within a transaction to make it possible to rollback everything in case the process fails. The most log consuming operations are:

  • Deleting old data from production tables
  • Inserting new data into production tables

The query used to delete the old data is:

DELETE FROM PROD_TABLE WHERE CONDITION

A simple truncate is not possible since not all the data can be deleted.

The data to be deleted and the data to be moved is about 200 Million records.

Is there anyway to improve the process to reduce the log consomption.

Improve this question
6
  • Would it be possible to segment the data?
    – McNets
    Commented Jun 24, 2020 at 10:53
  • What do you mean by segmenting data and how? The important thing is that whole process can be rolled back
    – ayou392
    Commented Jun 24, 2020 at 11:04
  • By date for example.
    – McNets
    Commented Jun 24, 2020 at 11:05
  • data can be segmented but I don't see how that would help. Because after copiying N segments if one segment fails the already moved segments needs to be rolled back. meaning everything needs to be within the same transaction.
    – ayou392
    Commented Jun 24, 2020 at 11:07
  • 1
    @ayou392, with segmented processing, you would use separate transactions by date and, in the case of failure, undo previously committed transactions with reversing inserts/deletes. The implication is data will be inconsistent until the entire ETL process completes. You mentioned 200M rows are deleted and inserted. How many are left untouched? Can the ETL process have exclusive access to the table for the duration of the process?
    – Dan Guzman
    Commented Jun 24, 2020 at 11:33

1 Answer 1

Reset to default
0

You might like to look at partition switching - the staging table could be switched in to the production table, and the production table switched out to a history table. https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver15#c-switching-partitions-between-tables gives an example of a switch out. There are quite a few blogs about the technique, look up a few and see if they match what you are trying to do.

A system I used to work on had a monthly data load. The data was staged to a table, which was then switched in to the live table. The live table had a partition per month. It was very quick as I think it's a metadata operation. HTH

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.