If we exit the process and then reopen the database in a new process, all that memory is not reallocated.Are you sure you have that right? What OS are you running?
Also, how are you checking to see if the memory was deallocated in the first place?
The kernel may still indicate that the process's virtual size has grown to some huge size, even though -- internally -- the process isn't actually using all of the memory. Sometimes the system won't reap the unused pages.
In general, if your app is memory and performance sensitive, you will need to balance the use of BEGIN/END transaction with the # of individual statements in any one transaction.
Writing to the disk has a huge performance impact, but caching all of those statements / state changes in memory prior to the END TRANSACTION requires a lot of memory.
Often, there is a balance between the two that is appropriate to your app. If not, you'll just have to get faster disks or more memory.
b.bum
