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.
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Â
(http://kbalertz.com/Feedback.aspx?kbNumber=256986/EN-US/
)
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:
\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 3.x\MaxBufferSize
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<ISessionProperties> spISP = m_session.m_spOpenRowset;
return spISP->SetProperties(1, &SessionProps);<BR/>
}
This behavior is by design.
The Microsoft Developer Network CD contains more information about Jet OLE DB Provider specific properties. Search on the
DBPROPSET_JETOLEDB_SESSION property. This search places you into the Jet OLE DB Provider documentation where you can find out more about the provider specific properties.