Delete Insert VS. Update Insert
-
I have a nightly process (SISS Package) that will do data dump from several tables used for order entry to tables used for reporting. My question is whether there is a general rule from a performance stand point that says wether it is faster to do an update on existing rows in the reporting table then insert new ones or just delete for a range then do a bulk insert. The number of records being updated/inserted would be anywhere from 2 records to 150,000. Assume that appropriate indexes will be used for deletes and updates.
-
I have a nightly process (SISS Package) that will do data dump from several tables used for order entry to tables used for reporting. My question is whether there is a general rule from a performance stand point that says wether it is faster to do an update on existing rows in the reporting table then insert new ones or just delete for a range then do a bulk insert. The number of records being updated/inserted would be anywhere from 2 records to 150,000. Assume that appropriate indexes will be used for deletes and updates.
I think It's better to update to avoid fragmentation in index. Unless you drop and rebuild the index after each bulk operation.
Hesham A. Amin blog
-
I think It's better to update to avoid fragmentation in index. Unless you drop and rebuild the index after each bulk operation.
Hesham A. Amin blog
-
Thanks for the information. Do inserts always cause fragmentation or is this more of a problem with bulk inserts?
They always do if the new records are inserted between existing record unless there is a space according to a previously deleted record. You can minimize this effect by configuring fill factor and padding for indexes. If you use the clustered index as an identity column, fragmentation should not occur. So there is no very direct do and don't in this case. You need to study your database structure carefully.
Hesham A. Amin blog