PRB: Jet Memory Usage Increases When You Insert New Records

When you use the OLE DB Provider for Jet to insert records into a Microsoft Access database file, if you use the AddNew and Update methods of the ActiveX Data Objects (ADO) Recordset object, the memory usage of Jet increases slowly but continuously. This memory is not released until you close the recordset.

The Jet provider maintains a "map" in memory of all new rows that you add to the rowset (or recordset). When the number of rows increases, the memory that is used for this map increases as well. This memory is not released until the recordset is closed.

Windows NT, Windows 2000, or Windows XP

On the Microsoft Windows NT, Windows 2000, or Windows XP platform, the Jet provider allocates this memory from the 2 gigabytes of addressable virtual memory; therefore, Microsoft does not anticipate that the provider's memory usage will become problematic for customers. Our tests suggest that you must call AddNew more than 11 million times to exhaust even half (1 gigabyte) of the addressable virtual memory. Furthermore, you must call AddNew more than 11 million times without ever restarting the application or closing the recordset or its connection to use this amount of virtual memory. If an application must support this volume of inserts without interruption, Microsoft strongly recommends that you use the more robust Microsoft SQL Server database platform instead of the Jet desktop database engine.

You can also use the following workarounds to control or to minimize the memory usage of the Jet provider:

• 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� 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.

Windows 98 or Windows Me

This can become a significant problem on systems that are running Windows 98 or Windows Millennium Edition (Me) because the memory for the map is reallocated every 64 records. Windows 98 and Windows Me handle memory management at the application level very differently than Windows NT, Windows 2000, or Windows XP. When the map is reallocated, memory becomes fragmented. No workaround exists for this type of memory fragmentation on Windows 98 or Windows Me because this is a limitation of the operating system.

This behavior is by design.

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.