Copy activity - truncate and insert vs upsert

Question

Copy activity - truncate and insert vs upsert

Trystan Mortimer 31

Assuming I do not need any data which may be removed from my source dataset, what is more efficient?

Truncate a table and reinsert all data
Do not truncate and use upsert

Thanks

Samy Abdul 3,361 Reputation points

2022-10-20T06:45:58.51+00:00

Hi ,Typically, in Database paradigm insert works faster in comparison with data deletes

and updates, primarily due to new records being added to end of the table, hence less

locking contentions. Whereas truncate basically get rid of whole data state and upserts are

new modified set of data. When you truncate the data it needs to perform two operations

which firstly truncate the entire data and insert whole new data ,when volume of data is
quite high, it all counts. Thanks
Trystan Mortimer 31 Reputation points

2022-10-20T22:00:34.927+00:00

Thank you, as you say, I think it will differ on a case-by-case basis depending on the table size.
AnnuKumari-MSFT 28,001 Reputation points Microsoft Employee

2022-11-03T09:03:48.633+00:00

Hi @Trystan Mortimer ,
Just checking in to see if the below answer helped. Please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well. If you have any further query do let us know.

Samy Abdul 3,361 Reputation points

2022-10-20T06:45:58.51+00:00

Hi ,Typically, in Database paradigm insert works faster in comparison with data deletes

and updates, primarily due to new records being added to end of the table, hence less

locking contentions. Whereas truncate basically get rid of whole data state and upserts are

new modified set of data. When you truncate the data it needs to perform two operations

which firstly truncate the entire data and insert whole new data ,when volume of data is
quite high, it all counts. Thanks
Trystan Mortimer 31 Reputation points

2022-10-20T22:00:34.927+00:00

Thank you, as you say, I think it will differ on a case-by-case basis depending on the table size.
AnnuKumari-MSFT 28,001 Reputation points Microsoft Employee

2022-11-03T09:03:48.633+00:00

Hi @Trystan Mortimer ,
Just checking in to see if the below answer helped. Please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well. If you have any further query do let us know.

Answer 1

Nandan Hegde 27,241 MVP

Hey,
With your current ask that in case if the data is no longer in source but used to come earlier and you no longer need it, the only way based on direct copy activity usage wod be to do truncate insert only. Since in case of upsert, data would either be inserted or updated but deletee record in source would always remain active in destination

Trystan Mortimer 31 Reputation points

2022-10-20T22:01:39.71+00:00

Thank you for the response @Nandan Hegde , however, I am more interested in the speed and cost of each activity and not the functionality.
Nandan Hegde 27,241 Reputation points MVP

2022-10-21T03:07:40.843+00:00

Hey,
I would say truncate and insert data is much quicker than upsert because in case of truncate , you are cleaning all data and then blindly reloading the entire dataset whereas in case of upsert, there is time to compare post which you insert or update.
No out of box feature to delete in case of upsert operation.
And w.r.t pricing, you would be charged for comparison as well
https://stackoverflow.com/questions/73725951/azure-data-factory-price-in-real-world-does-unchanged-upsert-is-treated-as-one/73811109#73811109

Hope this helps :)

Copy activity - truncate and insert vs upsert

1 answer

Activity