Encapsulate all transactions within stored procedures, including both the BEGIN TRANSACTION and COMMIT TRANSACTION statements in the procedure. This provides two benefits that help to reduce blocking locks.
First, it limits the client application and SQL Server to communications before and after the transaction, thus forcing any messages between the client and the server to occur at a time other than when the transaction is running (reducing transaction time).
Second, it prevents the user from leaving an open transaction (holding locks open) because the stored procedure forces any transactions that it starts to complete or abort. [6.5, 7.0, 2000, 2005]
Performance of the SQL MERGE vs. INSERT/UPDATE
-
MERGE is designed to apply both UPDATE and INSERTs into a target table from
a source table. The statement can do both at once, or simply do INSERTs or
on...
Posts
No comments:
Post a Comment