While that is correct, the problem is that IB's memory management
seems overwhelmed by this. Some compile statements take up as much as
15MB (that's Megabytes), and once you get to a point of having
hundreds of orphaned statements, it poses a severe performance
problem.

It's plausible to think that IB ought to reuse prepared statement in
the context of a new transaction, but that doesn't seem to be the case
-- memory simply grows, and statements remain "STALLED" and orphaned.
And we have word from IB that this happens only when a statement
handle is not released prior to releasing the transaction.

On 10/28/05, Jason Wharton <jwharton@...> wrote:
> 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: IBObjects@yahoogroups.com [mailto:IBObjects@yahoogroups.com]On
> > Behalf Of ulf_at_thgg
> > Sent: Wednesday, October 26, 2005 1:11 PM
> > To: IBObjects@yahoogroups.com
> > 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.
>
>
>
> ___________________________________________________________________________
> IB Objects - direct, complete, custom connectivity to Firebird or InterBase
> without the need for BDE, ODBC or any other layer.
> ___________________________________________________________________________
> http://www.ibobjects.com - your IBO community resource for Tech Info
> papers,
> keyword-searchable FAQ, community code contributions and more !
>
>
>
> ________________________________
> YAHOO! GROUPS LINKS
>
>
> Visit your group "IBObjects" on the web.
>
> To unsubscribe from this group, send an email to:
> IBObjects-unsubscribe@yahoogroups.com
>
> Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
>
> ________________________________
>