DOCUMENT:Q199156 11-JAN-2001 [vbwin] TITLE :PRB: DAO Field Object Assignment Slows as Field Count Increases PRODUCT :Microsoft Visual Basic for Windows PROD/VER:WINDOWS:5.0,6.0 OPER/SYS: KEYWORDS:kbDAO350bug kbDatabase kbVBp500 kbVBp600 ====================================================================== ------------------------------------------------------------------------------- The information in this article applies to: - Microsoft Visual Basic Professional Edition for Windows, versions 5.0, 6.0 - Microsoft Visual Basic Enterprise Edition for Windows, versions 5.0, 6.0 ------------------------------------------------------------------------------- SYMPTOMS ======== Assigning values to DAO recordset Field objects is slow for recordsets with many fields. (This may be perceived as slow database edits or updates). Note that the affected code is: rs.Fields(x) = and not: rs.Update, rs.Edit, or rs.AddNew If these methods are slow, it is likely a different problem. RESOLUTION ========== There are two ways to work around this problem. One possibility is to use DAO 3.0 rather than DAO 3.5 or DAO 3.51. This can be done by changing the library selected in Visual Basic's References dialog. Use the References command on the Project menu to view this dialog. NOTE: DAO 3.0 is not distributed with Visual Basic 5.0 or Visual Basic 6.0. You can also work around this problem by using a DDL (Data Definition Language) query rather than DAO code to create the tables. If the application's database already exists, DDL can be used to create a copy of the table in a new database, and DML (Data Manipulation Language) statements, such as INSERT INTO, can be used to move the data to the new database. For more information on creating tables via DDL, read the "CREATE TABLE Statement" Help topic in the Visual Basic on-line Help. STATUS ====== Microsoft is researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available. 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 Additional query words: ====================================================================== Keywords : kbDAO350bug kbDatabase kbVBp500 kbVBp600 Component : dao Technology : kbVBSearch kbAudDeveloper kbZNotKeyword6 kbZNotKeyword2 kbVB500Search kbVB600Search kbVBA500 kbVBA600 kbVB500 kbVB600 Version : WINDOWS:5.0,6.0 Issue type : kbprb Solution Type : kbpending ============================================================================= THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY. Copyright Microsoft Corporation 2001.