Subject RE: [IBO] Orphaned Statements
Author Jason Wharton
You should NOT have to unprepare statements prior to a transaction being
ended. If it is true then the people at InterBase have made a huge mistake
and I would very much hope I don't have to modify IBO to work around this.
A prepared statement should persist a transaction ending.

Jason Wharton

> -----Original Message-----
> From: []On
> Behalf Of ulf_at_thgg
> Sent: Wednesday, October 26, 2005 1:11 PM
> To:
> Subject: [IBO] Orphaned Statements
> All,
> 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
> plummet.
> 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.