Windows NT, Windows 2000, or Windows XP

• Use the MaxBufferSize registry setting with the value 512. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
  248014  (http://kbalertz.com/Feedback.aspx?kbNumber=248014/EN-US/ ) PRB: Jet OLE DB Provider Consuming Too Much Memory
  When Microsoft tested this workaround on Windows XP Professional Edition, the virtual memory for 100,000 inserts is reduced from approximately 18 megabytes to approximately 15 megabytes.
• Use SQL INSERT statements with the Execute method of the ADO Connection or Command object. This approach avoids the memory usage issue entirely and is more efficient for ADO. However, when you concatenate SQL INSERT strings in Microsoft Visual Basic, you may reduce the performance gain.
• Close the ADO recordset periodically, and reopen it immediately if necessary. This releases the memory that the Jet Provider previously held for its "map" of newly inserted rows.

Back to the top

Windows 98 or Windows Me

Back to the top

Steps to Reproduce Behavior

1. Create a new Visual Basic Standard EXE project.
2. Set a reference to Microsoft ActiveX Data Objects 2.x Library (ADO) and Microsoft ADO Ext. 2.x for DDL and Security.
3. Add a command button to Form1.
4. Paste the following code into the Click event of the command button. Make sure that you adjust the value of "m" for the maximum number of new records that you want to add during this test:

   Private Sub Command1_Click()
       Dim cat As ADOX.Catalog
       Dim cn As ADODB.Connection
       Dim rs As ADODB.Recordset
       Dim tbl As ADOX.Table
       Dim i As Long
       Dim m As Long
       Dim strDatabaseName As String
       
       strDatabaseName = App.Path & "\Test.mdb"
       
       'First, delete the test database if present.
       On Error Resume Next
       Kill strDatabaseName
       On Error GoTo 0
   
       'Next, (re)create the test database.
       Set cat = New ADOX.Catalog
       cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=" & strDatabaseName
       Set tbl = New ADOX.Table
       tbl.Name = "TestTable"
       tbl.Columns.Append "ID", adInteger
       tbl.Columns.Append "Field1", adVarWChar, 20
       tbl.Columns.Append "Field2", adVarWChar, 20
       cat.Tables.Append tbl
       Set tbl = Nothing
       Set cat = Nothing
   
       Set cn = New ADODB.Connection
       cn.CursorLocation = adUseClient
       cn.Provider = "Microsoft.Jet.OLEDB.4.0"
       cn.Open strDatabaseName
   
       Set rs = New ADODB.Recordset
       Set rs.ActiveConnection = cn
       rs.Open "TestTable", cn, adOpenStatic, adLockOptimistic, adCmdTable
   
       m = 100000
       For i = 0 To m
           With rs
               .AddNew
               .Fields(0) = i
               .Fields(1) = "data"
               .Fields(2) = "more data"
               .Update
           End With
       
           If (i Mod 5000) = 0 Then
               Debug.Print "Records inserted: " & Str(i) & " of" & Str(m)
           End If
       
           DoEvents
       Next i
   
       rs.Close
       Set rs = Nothing
       cn.Close
       Set cn = Nothing
   End Sub
   					

5. Configure System Monitor (PerfMon) to monitor Private Bytes for the VB6.EXE process.
6. Run the Visual Basic project. Before you click the command button, note the value of Private Bytes in System Monitor.
7. Click the command button. In System Monitor, watch the slow, continuous increase in the memory that is used. Notice that the memory increases when the Jet provider continues to insert new rows into the recordset. As soon as the recordset is closed, the memory usage returns approximately to its starting level.

Back to the top