How To Speed Up Data Access by Using BeginTrans & CommitTrans

View products that this article applies to.

This article was previously published under Q146908

SUMMARY

You can speed up database operations in a Microsoft Access database by using transactions. A transaction starts with a BeginTrans statement and ends with a CommitTrans or Rollback statement. However, for reasons relating to stack usage it is recommended that these code regions are kept as small as possible. This ensures optimal performance and reliability." For more information as to when to use transactions, please see the following article in the Microsoft Knowledge Base:

145757 (http://support.microsoft.com/kb/145757/EN-US/ ) : Ideas to Consider When Using Transactions

The sample program below is over 17 times faster when using BeginTrans/CommitTrans. Performance may vary on different computers.

Back to the top

MORE INFORMATION

You can tune the performance of Visual Basic by using transactions for operations that update data. A transaction is a series of operations that must execute as a whole or not at all. You mark the beginning of a transaction with the BeginTrans statement. You use the Rollback or CommitTrans statement to end a transaction.

You can usually increase the record updates per second (throughput) of an application by placing operations that update data within an Access Basic transaction.

Because Visual Basic locks data pages used in a transaction until the transaction ends, using transactions prevents access to those data pages by other users while the transaction is pending. If you use transactions in a multi-user environment, try to find a balance between data throughput and data access.

If database operations are not within a transaction, every Update method causes a disk write.

Transactions are very fast because they are written to a buffer in memory instead of to disk. CommitTrans writes the changes in the transaction buffer to disk.

Robust error trapping is important when using transactions to avoid losing writes if the program gets an error in the middle of a transaction.

For more performance tuning tips for data access in Microsoft Visual Basic version 4.0, see the PERFORM.TXT file.

Back to the top

Step-by-Step Example

Start a new project in Visual Basic. Form1 is created by default.

Add the following to the Form Load event code:

      Private Sub Form_Load ()
         Dim Starttime, Endtime
         Dim db As Database
         Dim t As RecordSet
         Dim i As Integer
         Dim tempName As String
         Dim temphone As String
         Set db = Workspace(0).OpenDatabase("c:\vb\BIBLIO.MDB") ' Uses a
          ' copy of BIBLIO.MDB.
         Set t = db.OpenRecordSet("Publishers", dbOpenTable)
         Starttime = Now
         'BeginTrans  ' Add this and CommitTrans (below) for greater speed.
         For i = 1 To 100
            tempName = "testname" & Str$(i) ' Make an arbitrary unique
                                            '  string.
            tempPhone = Str$(i)             ' Make arbitrary number.
            t.AddNew ' AddNew clears copy buffer to prepare for new record.
            t!PubID = 30 + i  ' Set primary key to unique value.
            t!Name = tempName  ' Set Name field to unique value.
            t!Telephone = tempPhone  ' Set Telephone field to unique value.
            t.Update   ' Write the record to disk or to transaction buffer.
         Next i
         'CommitTrans  ' Add this and BeginTrans (above) for greater speed.
         Endtime = Now
         MsgBox "Time required= " & Format(Endtime - Starttime, "hh:mm:ss")
         t.Close
         db.Close
         End
      End Sub

The above code adds 100 new records to the BIBLIO.MDB database file. Add the records to a copy of BIBLIO.MDB instead of to the original.

Start the program (or press the F5 key). A message box reports the time required to add 100 new records. Close the form to end the program.

If you do not use the BeginTrans and CommitTrans statements, this program reports 17 seconds to add 100 records on a 486/66 PC. When you add BeginTrans and CommitTrans as shown in the program comments above, the program takes less than 1 second on that same computer. Performance may vary on different computers.

Back to the top

REFERENCES

Microsoft Visual Basic, version 4.0, "Professional Features Book 1," Page 204
"Microsoft Developer Network News" newspaper, January 1994, Volume 3, Number 1, published by Microsoft Corporation.

Back to the top