I am looking for much more better way to update tables using SSIS. Specifically, i wanted to optimize the updates on tables (around 10 tables uses same logic).
The logic is,
- Select the source data from staging then inserts into physical temp table in the DW (i.e TMP_Tbl)
- Update all data matching by customerId column from TMP_Tbl to MyTbl.
- Inserts all non-existing customerId column from TMP_Tbl1 to MyTbl.
Using the above steps, this takes some time populating TMP_Tbl. Hence, i planned to change the logic to delete-insert but according to this: In SQL, is UPDATE always faster than DELETE+INSERT? this would be a recipe for pain.
Given:
- no index/keys used on the tables
- some tables contains 5M rows, some contains 2k rows
- each table update took up to 2-3 minutes, which took for about (15 to 20 minutes) all in all
- these updates we're in separate sequence container simultaneously runs
Anyone knows what's the best way to use, seems like using physical temp table needs to be remove, is this normal?