Best Practice for batch processing In SQL Server

Question

Best Practice for batch processing In SQL Server

Miru Seshadri

SSC Veteran

Points: 258
More actions
December 19, 2018 at 8:15 am

#382495

Hello,
I have a general question regarding best practices for creating a batch process in SQL Server 2016.
All code is residing in the database procedures. I need to make sure that there is a easy way to restart the process in case of failure. A typical batch process will insert/Update multiple rows in multiple tables. In case of a failure how to roll back and restart? Since this is a very complex batch, it is not one big unit where I can have all the code within one begin/end transaction.
My first thought was to take a backup of all the tables that will be affected by this batch. Incase of failure data can be restored from backup and batch can be restarted. There may be 5 or 6 tables that are growing and the upper limit could be half a million records or so in each of the tables.
Is there a better way to implement this process?
Thank you for your valuable time and input.
Regards,
MMuthu

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

Miru Seshadri - Wednesday, December 19, 2018 8:15 AM
Hello,
I have a general question regarding best practices for creating a batch process in SQL Server 2016.
All code is residing in the database procedures. I need to make sure that there is a easy way to restart the process in case of failure. A typical batch process will insert/Update multiple rows in multiple tables. In case of a failure how to roll back and restart? Since this is a very complex batch, it is not one big unit where I can have all the code within one begin/end transaction.
My first thought was to take a backup of all the tables that will be affected by this batch. Incase of failure data can be restored from backup and batch can be restarted. There may be 5 or 6 tables that are growing and the upper limit could be half a million records or so in each of the tables.
Is there a better way to implement this process?
Thank you for your valuable time and input.
Regards,
MMuthu

Is your process the only process which modifies these tables?

Miru Seshadri SSC Veteran Points: 258 More actions · Answer 2

Yes, at the time of modification. It is a batch and will be run during off hours.
Thanks
MMuthu

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 3

Preprocess all your data in staging tables. When you're ready to update the final tables from the staging tables, start with ...

SET XACT_ABORT ON;
BEGIN TRANSACTION

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Miru Seshadri SSC Veteran Points: 258 More actions · Answer 4

Thank you Jeff. I will try that.
What is the difference between Xact Abort and Rollback transaction? Currently, I have all my data processing code in a Try block with a begin transaction. In the catch block, I have a rollback transaction. Would that be different than XACT ABORT ?
Thank you.
MMuthu

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 5

Miru Seshadri - Wednesday, December 26, 2018 5:50 AM
Thank you Jeff. I will try that.
What is the difference between Xact Abort and Rollback transaction? Currently, I have all my data processing code in a Try block with a begin transaction. In the catch block, I have a rollback transaction. Would that be different than XACT ABORT ?
Thank you.
MMuthu

XACT_ABORT auto-magically does the rollback and forces discontinuation of the code in multi-statement transactions. You still need the rollback check in the CATCH block to ensure that there's no transaction still in-process, just in case.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)