How to release memory when using JetUpdate to insert record in Extensible Storage Engine Database?

Question

I need to insert millions of records.Right now I'm in a very tight loop where, for every record, I

a) start a transaction  (JetBeginTransaction)
b) prepare an update (JetPrepareUpdate)
c) add the row (JetSetColumns)
d) update (JetUpdate)
e) commit the transaction (JetCommitTransaction)

But more and more memory is occupied, when records are inserted by excuting JetUpdate. Even if I stop to excute insert records or all records are inserted, the memory would not be released.

How could I limit the memory to rise?

Why the JetCommitTransaction does not release the memory?

How to release the memory timely?

Answer 1

The database cache is likely growing. Confirm with the Perf Counter: Database -> Database Cache Size (MB).

You can cap the size with JET_paramCacheSizeMax (see https://learn.microsoft.com/en-us/windows/desktop/extensible-storage-engine/database-cache-parameters ).

I also agree with egray's comment that you should insert more than one insert per transaction. Or at least use Lazy Commit (a flag to JetCommmitTransaction). Otherwise you will be writing to your transaction log file much too frequently, and perf will greatly suffer.