performance of bulk insert

10-21-2004, 01:54 AM
I have a database that holds an information about customers, each customer's data is holded in many referenced tables. In one stored procedure i should insert/update many customers. I have 2 options: bulk insert of all customers data in one transaction or insert each customer in separate transaction. My question is: what approach is considered to be correct, and gives better performance assuming that the number of inserted/updated customers is very large? Thank you

10-21-2004, 07:06 PM
It depends..I have never had a problem with a bulk insert. Regarding code maintenance, the fewer procedures you implement the better. The major concern with multiple transactions is how you implement your row and table locks within each transaction..if you are running many transactions back to back, and you are conducting strict row locking to protect your transaction, you have to avoid bottleneck. The next transaction will not be able to begin its work until the COMMIT TRANSACTION method is fired and the row lock is released. When this is designed properly, it works fine, and just as good as a bulk insert.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum