Performance of deleting versus updating

Hi,
I am building a DW in SQL2005.

There are many master files that are imported nightly. There is a chance
that old master file records may have been deleted (from source system) so i
need to merge all existing master file records with any records that exist in
the DW but have been deleted in the source system.

Is it better (performance/log wise) to perform
1. Update all fields in the DW table with results from the nightly extract
and then insert any new master file records
2. Delete all records from DW that have been imported then insert all master
files records into the DW table (basically updating existing records and
inserting any new ones).
3. Another options maybe?

Options one and two give the same result however are completely different
approaches. As we are doing this with 100,000's records a night i want this
process to be as efficient as possible.

It really is a question of is 100,000's of updates better than doing
100,000's of deletes and inserts.

Thanks.

Simon.

Assuming you're using SSIS, have you considered using slowly changing
dimensions? (Let BIDS take care of business.)


ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/