Developing ADO.NET Applications : Designing .NET Applications for Performance : Designing .NET Applications : Managing Commits in Transactions

Managing Commits in Transactions

Committing transactions is slow due to the result of disk input/output and, potentially, network input/output. Always start a transaction after connecting; otherwise, you are in autocommit mode.

What does a commit actually involve? The database server must flush back to disk every data page that contains updated or new data. This is usually a sequential write to a journal file, but nonetheless, is a disk input/output. By default, Autocommit is on when connecting to a data source. Autocommit mode usually impairs performance because of the significant amount of disk input/output needed to commit every operation.

Furthermore, some database servers do not provide an autocommit mode natively. For this type of server, the .NET data provider must explicitly issue a COMMIT statement and a BEGIN TRANSACTION for every operation sent to the server. In addition to the large amount of disk input/output required to support autocommit mode, a performance penalty is paid for up to three network requests for every statement issued by an application.

The following code fragment starts a transaction for OpenAccess SDK:

OpenAccessConnection MyConn = new OpenAccessConnection

("Connection String info");

MyConn.Open()

// Start a transaction

OpenAccessTransaction TransId = MyConn.BeginTransaction();

// Enlist a command in the current transaction

OpenAccessCommand OpenAccessToDS = new OpenAccessCommand();

OpenAccessToDS.Transaction = TransId;

...

// Continue on and do more useful work in the

// transaction

Although using transactions can help application performance, do not take this tip too far. Leaving transactions active can reduce throughput by holding locks on rows for long times, preventing other users from accessing the rows. Commit transactions in intervals that allow maximum concurrency.