Subject Orphaned Statements
Author ulf_at_thgg

we're using IBObjects in a fairly large project against Interbase and
Firebird. We've been battling some performance issues for quite some
time now.

We have recently discovered (thanks to IB7.x's Performance Monitor),
that the amount of memory used by Interbase for compiled user queries
grows excessively over time. In one instance, Interbase was hogging
nearly 500MB (!!) of memory for compiled user-queries. At a certain
point, the amount of memory used becomes too large for the machine and
it begins swapping excessively, which then causes the performance to

Upon further investigation, we were able to determine that the vast
majority of these queries were not only "STALLED" but also orphaned --
this means they dodn't belong to any active transaction. Therefore,
there is no way for our application, or the server, to release this
memory back to the operating system.

We discussed this with Interbase directly, and they explained that
this situation occurs when a transaction is rolled back or committed
before the associated statement handles are released. While
admittedly this is something the server or the API should take care
of, it is up to the application to keep track of open statement
handles and to release them before releasing a transaction.

Our application was initially migrated from Paradox to BDE, and then
later to IBObjects. Because of this history, it currently simply
relies on IBO's "transaction magic" to handle database access. Thus
it appears as though IBO does not keep track of statement handles
associated with a transaction, and/or in certain cases will not
release them, which in turn creates those orphaned statements.

One workaround has been to explicitly UnPrepare every query after
closing it, but we're hoping that there might be an easier (and
better!) way to fix this problem within IBObjects.

Any comments, hints or suggestions are greatly appreciated.