December 5, 2016 at 7:57 am
Hello All,
I'm trying to research this as much as possible this last weekend to make sure I am on the right path. Is there any major issue with using Explicit Transactions in our ETL process ? We are not currently using SSIS, Instead we use Stored Procedures. The main reason i would like to use Explicit trans is not as much for performance but not rather consistency and atomicity of the data.
Is there any issues I'm not think of by batching my Inserts, Deletes and updates in Explicit BEGIN TRAN END TRAN with Try Catch ?
Thanks !
***SQL born on date Spring 2013:-)
December 6, 2016 at 11:32 am
If you must have all or nothing for the entire dataset per ETL operation, an explicit transaction wrapping the bulk copy/load gives you that control (instead of batch level). Also, if the T part of your ETL involves one or more tasks that must be complete for each batch then explicit transactions allows you to control scope and failure actions.
Just remember that same optimization practices apply for how you handle the loading so don't end up with a REBAR process. Explicit transactions can sometimes obscure REBAR script impact because of reduced log flushes. Doing a bunch of row inserts using a loop still has higher overhead compared to bulk insert and the difference can be significant if you're dealing with lots of rows.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply