Use SQL DDL Statements Instead of DAO Looping Routines

In most cases when using Microsoft Jet 3.5, it is better to use a single SQL data definition language (DDL) statement than a DAO looping routine. The following example runs an update first by using a recordset, then by executing an SQL DDL statement. Both updates are wrapped in transactions and rolled back after they have been timed.

In this example, updating with the SQL DDL statement is only slightly faster than the recordset update, but the difference becomes significant for large data sets. In this example, strDbPath is the path to the NorthwindTables database:

Dim wrk As Workspace
Dim dbs As Database
Dim rst As Recordset
Dim lngTimer As Long
Dim strDbPath As String, strSQL As String

' Return reference to default workspace.
Set wrk = DBEngine(0)
Set dbs = OpenDatabase(strDbPath)
strSQL = "UPDATE [Order Details] SET UnitPrice " _
	& "= UnitPrice*1.1, Discount = Discount+0.1;"

' Begin transaction.
wrk.BeginTrans
' Begin timing.
lngTimer = Timer
Set rst = dbs.OpenRecordset("Order Details")
' Update each record in the recordset.
Do Until rst.EOF
	rst.Edit
	rst!UnitPrice = rst!UnitPrice * 1.1
	rst!Discount = rst!Discount + 0.1
	rst.Update
	rst.MoveNext
Loop
Debug.Print "Elapsed time for recordset update = " & Timer - lngTimer
' Roll back changes.
wrk.Rollback

' Perform same operation with SQL DDL query.
wrk.BeginTrans
lngTimer = Timer
dbs.Execute strSQL, dbFailOnError
Debug.Print "Elapsed time for DDL update = " & Timer - lngTimer
wrk.Rollback

There are two reasons that the SQL DDL update is faster than the recordset update:

• Microsoft Jet 3.5 removed implicit transactions for SQL DDL statements.
  
  
• In this example, string values are being manipulated. Visual Basic uses the Unicode format and DAO inherently uses Visual Basic; however, Microsoft Jet only stores string data in ANSI format for U.S. code pages. This requires Visual Basic to translate from ANSI to Unicode and then back to ANSI whenever string data passes through DAO. This translation, especially for long string types, can slow down performance. Because the query optimizer in Microsoft Jet does not process string data through Visual Basic (unless expressions are being used), performing bulk type operations from SQL can be significantly faster when manipulating string data.

See Also For more information on SQL DDL statements, see “Removal of Implicit Transactions for SQL DDL Statements” earlier in this chapter.