Microsoft KB Archive/313011

= PRB: Jet Memory Usage Increases When You Insert New Records =

Article ID: 313011

Article Last Modified on 11/17/2003

-

APPLIES TO


 * Microsoft OLE DB Provider for Jet 4.0
 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.5

-



This article was previously published under Q313011



SYMPTOMS
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.



CAUSE
The Jet provider maintains a &quot;map&quot; 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 &quot;map&quot; 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.



STATUS
This behavior is by design.



Steps to Reproduce Behavior
 Create a new Visual Basic Standard EXE project.</li> Set a reference to Microsoft ActiveX Data Objects 2.x Library (ADO) and '''Microsoft ADO Ext. 2.x for DDL and Security'''.</li> Add a command button to Form1.</li>  Paste the following code into the Click event of the command button. Make sure that you adjust the value of &quot;m&quot; 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 & &quot;\Test.mdb&quot; '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 &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; & _ &quot;Data Source=&quot; & strDatabaseName Set tbl = New ADOX.Table tbl.Name = &quot;TestTable&quot; tbl.Columns.Append &quot;ID&quot;, adInteger tbl.Columns.Append &quot;Field1&quot;, adVarWChar, 20 tbl.Columns.Append &quot;Field2&quot;, adVarWChar, 20 cat.Tables.Append tbl Set tbl = Nothing Set cat = Nothing

Set cn = New ADODB.Connection cn.CursorLocation = adUseClient cn.Provider = &quot;Microsoft.Jet.OLEDB.4.0&quot; cn.Open strDatabaseName

Set rs = New ADODB.Recordset Set rs.ActiveConnection = cn   rs.Open &quot;TestTable&quot;, cn, adOpenStatic, adLockOptimistic, adCmdTable

m = 100000 For i = 0 To m       With rs            .AddNew .Fields(0) = i           .Fields(1) = &quot;data&quot; .Fields(2) = &quot;more data&quot; .Update End With If (i Mod 5000) = 0 Then Debug.Print &quot;Records inserted: &quot; & Str(i) & &quot; of&quot; & Str(m) End If       DoEvents Next i

rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub </li> Configure System Monitor (PerfMon) to monitor Private Bytes for the VB6.EXE process.</li> Run the Visual Basic project. Before you click the command button, note the value of Private Bytes in System Monitor.</li> 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.</li></ol>

Keywords: kbdatabase kbjet kbprb KB313011

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.