Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources]

Understanding the Benefits of Transaction Processing

In Access 2.0, there were many marginal benefits of added transaction processing because Access 2.0 did no implicit transaction processing itself. Listing 20.1 shows code that, when run in Access 2.0, writes the data to disk each time the Update method occurs in the loop. These disk writes were costly in terms of performance, especially if the tables were not located on a local machine.

Listing 20.1 Transaction Processing Using Access Basic as Seen in Access 2.0

Sub IncreaseQuantity() On Error GoTo IncreaseQuantity_Err Dim db As DATABASE Dim rst As Recordset Set db = CurrentDb Set rst = db.OpenRecordset("Select OrderId, _ Quantity From tblOrderDetails", _ dbOpenDynaset) 'Loop through recordset increasing Quantity field by 1 Do Until rst.EOF rst.Edit rst!Quantity = rst!Quantity + 1 rst.UPDATE rst.MoveNext Loop IncreaseQuantity_Exit: Set db = Nothing Set rst = Nothing Exit Sub IncreaseQuantity_Err: MsgBox "Error # " & Err.Number & ": " & Error.Description Resume IncreaseQuantity_Exit End Sub

NOTE

This code, and all the code in this chapter, is located in the CHAP20EX.MDB database on the sample code CD-ROM in the basTrans module.

NOTE

Any discussion of Access 2003 covered in this section also applies to both Access 2000 and Access 2002.

The same code found in Listing 20.1 performs much differently when run in Access 2003. In addition to any explicit transaction processing you might implement for data-integrity reasons, Access 2003 does its own behind-the-scenes transaction processing. Jet does this implicit transaction processing solely to improve the performance of your application. As the processing loop in the IncreaseQuantity routine executes, Access buffers and then periodically writes the data to disk. In a multiuser environment, Jet (implicitly) commits transactions every 50 milliseconds by default. This period of time is optimized for concurrency rather than performance. If you feel that it is necessary to sacrifice concurrency for performance, you can modify a few Windows registry settings to achieve the specific outcome you want. The next section covers these settings.

Although implicit transaction processing, along with the modifiable Windows registry settings, generally gives you better performance than explicit transaction processing, it is not a cut-and-dried situation. Many factors impact the performance benefits gained by both implicit and explicit transaction processing:

• Amount of free memory

• Number of columns and rows being updated

• Size of the rows being updated

• Network traffic

If you plan to implement explicit transaction processing solely to improve performance, you should make sure that you benchmark performance using both implicit and explicit transactions. It is critical that your application-testing environment be as similar as possible to the production environment in which the application will run.