> Then I can't see how you can avoid the memory-eating behaviour.
> SUSPEND is causing IB to append each row to an output dataset
> in memory as it is processed. It's all inside one transaction,
> isn't it, so the dataset is going to keep growing until the
> transaction completes.

I wondered about that. But why should the version with the stored
procedures grow so dramatically, when a simple select of the same
number of records does not get any bigger after prepare?

I guess one explanation may be that IB may be playing it safe. That
is; when extracting simple fields IB knows (via transaction context)
that it can reread that data from the tables so it does not keep it in
memory. Whereas IB presumably realises that it may not be able to
re-execute a procedure and get the same result so it forces all values
to be cached.

It does make me wonder about the amount of memory used though. I mean
the database file itself is only 380Mb, with several other quite large
tables in it. To use 350Mb to cache the results of this one table
extraction does seem a bit excessive (but then I guess we are losing
the RLE compression).

I suspect I am going to have to rethink and retest a few items against
this new found knowledge. I may also need to setup a few
temporary/work-space tables for my batch processing so that I can
avoid having to use select procedures.

Such a shame, everything was starting to work out quite nicely before
I discovered this!

Geoff Worboys
Telesis Computing