Microsoft KB Archive/248014

= PRB: Jet OLE DB Provider Consuming Too Much Memory =

Article ID: 248014

Article Last Modified on 7/28/2003

-

APPLIES TO


 * Microsoft OLE DB Provider for Jet 4.0

-



This article was previously published under Q248014



SYMPTOMS
After performing a large number of operations ( for example, inserts) on a Microsoft Jet database engine with the Jet 4.0 OLE DB Provider, large amounts of memory are consumed by the application. For example, performing 1000 inserts in a tight loop may reveal 100+ megabyte usage. When the connection is closed, the memory is freed.



CAUSE
The Jet 4.0 OLE DB Provider sets the Jet OLEDB:Max Buffer Size property to 0 by default. This allows the size of the cache to grow very large. For example, on a 256-MB computer up to 132 megabytes can be consumed by Jet. If you are using Visual C++, the Max Buffer Size property is a session property (DBPROPSET_JETOLEDB_SESSION property set) with the value of DBPROP_JETOLEDB_MAXBUFFERSIZE. These constants are defined in the Msjetoledb.h file that is included with the Microsoft Data Access SDK.

IMPORTANT: This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows Registry

The Max Buffer Size property control is the maximum amount of memory that can be used by the read-ahead/write-ahead Jet cache.

Note that the Max Buffer Size property doesn't have any affect on Jet 3.x files. You must set the registry to the following registry setting:



RESOLUTION
Set the Jet OLE DB:Max Buffer Size property to a value such as 2048 kilobytes. When using ADO, the property must be set after opening the connection. When using the Visual C++ ATL consumer templates, you must set the property using the session object.

Here is an code sample of how to use the property in a Visual Basic application:

Private Sub Command1_Click

Dim conn As New Connection

conn.Open "provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\MyDB.mdb" conn.Properties("Jet OLEDB:Max Buffer Size") = 500

For i = 1 To 1000 conn.Execute "INSERT into MYTable values (1,1)",, adExecuteNoRecords conn.Execute "Delete from MyTable",, adExecuteNoRecords Next i

End Sub

Following is Visual C++ ATL code which demonstrates how to set the property:

HRESULT OpenDataSource {  HRESULT      hr; CDBPropSet dbinit(DBPROPSET_DBINIT);

dbinit.AddProperty(DBPROP_AUTH_CACHE_AUTHINFO, true); dbinit.AddProperty(DBPROP_AUTH_ENCRYPT_PASSWORD, false); dbinit.AddProperty(DBPROP_AUTH_MASK_PASSWORD, false); dbinit.AddProperty(DBPROP_AUTH_PASSWORD, OLESTR("")); dbinit.AddProperty(DBPROP_AUTH_USERID, OLESTR("Admin")); dbinit.AddProperty(DBPROP_INIT_DATASOURCE, OLESTR("D:\\MYDB.mdb")); dbinit.AddProperty(DBPROP_INIT_MODE, (long)16); dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4); dbinit.AddProperty(DBPROP_INIT_PROVIDERSTRING, OLESTR("")); dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033); dbinit.AddProperty(DBPROP_AUTH_CACHE_AUTHINFO, true); hr = db.Open(_T("Microsoft.Jet.OLEDB.4.0"), &dbinit); if (FAILED(hr)) return hr;

hr = m_session.Open(db); if (FAILED(hr)) return hr;

CDBPropSet SessionProps(DBPROPSET_JETOLEDB_SESSION); SessionProps.AddProperty(DBPROP_JETOLEDB_MAXBUFFERSIZE, 512L); CComQIPtr spISP = m_session.m_spOpenRowset; return spISP->SetProperties(1, &SessionProps); }



STATUS
This behavior is by design.

