PRB: DAO Field Object Assignment Slows as Field Count Increases

rs.Fields(x) =<value>
				

rs.Update, rs.Edit, or rs.AddNew
				

MORE INFORMATION

Steps to Reproduce Behavior

The following code creates two databases with tables named according to the number of fields in that table. That is, Table20 contains 20 fields, and so forth. The databases are identical except that the tables in one are created via the CreateTableDef() method, whereas the tables in the other are created via DDL queries. For each table in each database, the table will be opened and a new value assigned to a field. This assignment will be timed and the results written to an output file. Note that the actual data in the database will never be updated.

The output files are comma-delimited text files, named according to the corresponding database. The first column contains the number of fields in the table tested; the second column contains the approximate time, in seconds, that was required to assign a value to a field in the corresponding recordset object.

NOTE: All times are approximate and are intended only to illustrate the issue involved. Times will vary greatly depending on the specifics of the environment in which the test is run.

1. Create a new Standard Exe project in Visual Basic and add a new module to the project. Check Microsoft DAO 3.51 object Library under Project References.
2. In Project - Project1 Properties, change the Startup Object to Sub Main.
3. Place the following code in the General Declarations section of Module1 and run the project by pressing the F5 key:
   

   
      Option Explicit
   
      Const strWrkDir = "c:\temp\"
      Const maxfld = 200
   
      Dim dbfastname As String
      Dim dbslowname As String
   
      Sub CreateDBSlow()
          Dim db As Database
          Dim td As TableDef
          Dim fld As Field
          Dim cTbl As Integer
          Dim cFld As Integer
   
          Set db = DBEngine.CreateDatabase(dbslowname, dbLangGeneral)
          For cTbl = 1 To maxfld   
              Set td = db.CreateTableDef("Table" & cTbl)
              For cFld = 1 To cTbl
                  Set fld = td.CreateField("Field" & cFld, dbText)
                  td.Fields.Append fld
                  Set fld = Nothing
              Next cFld
              db.TableDefs.Append td
              Set td = Nothing
          Next cTbl
          db.Close
   
      End Sub
   
      Sub CreateDBFast()
          Dim db As Database
          Dim td As TableDef
          Dim fld As Field
          Dim cTbl As Integer
          Dim cFld As Integer
          Dim strSQL As String
   
          Set db = DBEngine.CreateDatabase(dbfastname, dbLangGeneral)
          For cTbl = 1 To maxfld
              strSQL = "CREATE TABLE Table" & cTbl & " ("
              For cFld = 1 To cTbl
                  strSQL = strSQL & "Field" & cFld & " text,"
              Next cFld
              strSQL = Left(strSQL, Len(strSQL) - 1) & ")"
              db.Execute strSQL
          Next cTbl
          db.Close
   
      End Sub
   
      Sub DoTest(dbName As String)
          Dim db As Database
          Dim rs As Recordset
          Dim sStart As Single
          Dim sStop As Single
          Dim cTbl As Integer
   
          Open dbName &; ".txt" For Output As 1
          Set db = DBEngine.OpenDatabase(dbName)
          For cTbl = 1 To maxfld
              Set rs = db.OpenRecordset("Select * From Table" & cTbl)
              rs.AddNew
              sStart = Timer
              rs.Fields(0) = ""
              sStop = Timer
              rs.Close
              Set rs = Nothing
              Write #1, cTbl, sStop - sStart
          Next cTbl
          Close 1
   
      End Sub
   
      Sub Main()
   
          dbfastname = strWrkDir & "fastdb.mdb"
          dbslowname = strWrkDir & "slowdb.mdb"
   
          CreateDBSlow
          CreateDBFast
          DoTest dbslowname
          DoTest dbfastname
   
          MsgBox "Test Complete"
   
      End Sub