Christian,

I think it is in the nature of your application task - not as a fault, but
as a characteristic of what the task does.

Like other data, blob data are cached into the page cache. And like
database data, blob data in the cache are cached on pages separate from the
row data. So, for the blob, there will be a minimum of one page of the
cache that will get allocated for each iteration of the loop and will be
left "tied up" for the duration of the transaction and possibly longer,
regardless of whether the insert occurs via an insert statement directly
from the client or an insert statement in a stored procedure.

Also, the transaction state bitmap, which is responsible for the accounting
of the work performed by transactions, will be increasing and increasing
with each iteration of the loop. With inserts, there is additional memory
overhead because the system maintains a separate rollback cache in memory
for inserts, as well. (You can disable this by using an elusive API
parameter that I came across while writing The Firebird Book and now can't
track down...something like isc_no_rollback...)

Your deduction that the memory growth is somehow happening as a result of
opening the cursors seems coincidental: memory growth certainly does occur
once in each iteration of the loop. Of course, at the point where you
inspect memory usage, the next iteration of the loop is under way. Since
you last checked, the system has grabbed one or more pages of cache for the
latest blob and, if needed, one or more for the row data, and has updated
(increased) both the TSB and the insert cache, all for the previous iteration.

If the application is being run on the server, you might find that FR needs
to do some kind of buffering to compensate for not having a buffered
dataset, and isn't cleaning up properly. This is nothing more than
conjecture, though. It might be worth seeing whether using an IB_Query for
the detail sets would avoid that, if the app is server-based.

You wrote:

> As each read-only statement is executed several hundreds of times,
> the server consumes hundreds of megabytes of memory which is only freed
> when the transaction commits or rolls back.

> I cannot believe that this high memory usage is acceptable.

It depends on whether high memory usage is more acceptable to you than the
requirement to perform this operation cyclically. It's a pragmatic
decision...time is costly, memory is cheap.

> So I wonder if there is some fault in my logic

The logic looks reasonable to me - except for cBrokerPos2, that is opened
in each iteration of the loop yet never has any parameter values applied to
it. Presumably, it surfaces some static data for the report, so calling
its First() method once, before the loop starts, should suffice.
It's eating a bit of server resource unnecessarily at each iteration.

The main memory drain I see is that each iteration of the loop is passing a
(possibly quite large) blob to an INSERT statement.

> or if there is a way doing the same consuming less memory.

Obviously, if you could break the process up into a number of smaller
transactions, and hard-commit each one, the overall memory load will be
reduced. Ending the transaction will "wash out" the TSB and also reset the
insert cache.

It won't clear the page cache, though. Those pages will stay in cache
until BOTH a) they not needed by any transaction AND b) their space is
needed because the page cache is full. If you don't have a special reason
to need hundreds of megabytes of cache, you might gain something by
reducing the size of the cache to lower the ceiling and allow it to get
full sooner.

btw, the fact that the statements are read-only makes absolutely no
difference on the server side -- as far as the server is concerned, all
SELECT statements are read only and virtually all other statements are
not. :-)) Having a read-only *transaction* can improve resource usage in
other areas (GC and the size of the TSB) but a read-only transaction isn't
possible for your task.

Helen