PRB: Jet Memory Usage Increases When You Insert New Records (313011)

RESOLUTION

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.

MORE INFORMATION

Steps to Reproduce Behavior

Create a new Visual Basic Standard EXE project.
Set a reference to Microsoft ActiveX Data Objects 2.x Library (ADO) and Microsoft ADO Ext. 2.x for DDL and Security.
Add a command button to Form1.

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

Configure System Monitor (PerfMon) to monitor Private Bytes for the VB6.EXE process.
Run the Visual Basic project. Before you click the command button, note the value of Private Bytes in System Monitor.
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.