Hi Helen,

> IB_Cursor is great for things that don't need the overhead of
> buffering. As it transpired (eventually) your report involved many nested
> levels. IB_cursors would be OK for this if the report didn't involve
> grouping - but yours clearly did.

Correct.

> Because no buffers were available for each iteration of each level of the
> outer loop, I could envisage that FastReport was probably doing something
> internally to retain (or restore) access to the data controlling the
> loops. As Delphi reporting tools, in the general sense, work with buffered
> datasets, I could visualise 1000 or so separate cursors accumulating there
> on the server, waiting for Fetch calls that would never happen. If
> FastReport didn't know how to clean up these cursors, that would account
> for the unusual memory growth.

A deeper analysis showed that FastReport was executing the cursors
several times in comparison to the queries. As each execution of the
loop's select statements consumed quite a lot of memory on the
server, it made a big difference in comparison to single execution of
each statement. Now I wonder why I did not recognize this earlier,
but as huge amounts of data were processed, the output of the
IB_Monitor was quite long. I focused to much on analyzing the
statements and plans and all that stuff.

> If there were sorts involved (which I couldn't tell, because you didn't
> provide any SQL)

*duck*

> -- possibly at all three levels -- involving perhaps 1000
> * 1000 "redundant" open cursors, then the big numbers you reported would be
> unsurprising.

One could say that in this case, it could have helped if I had provided the IB_Monitor
output. *duck again*

> A big report cycle with a lot of sorts is going to consume a lot of RAM in
> Fb 1.5, too, because, unlike previous servers, Fb 1.5 uses RAM for storing
> the intermediate sets.

Sorting did not seem to be the problem. The connection which was
executing the report was able to use a maximum of 64 MB of memory for
sorting. (OK, another information I should have provided in my initial
posting. Instead of ducking, I should perhaps consider not to stand
up again ;-)

> As I suggested, you could lower the ceiling by
> splitting the run into separate transactions, but you said you needed the
> whole run to be done in one single transaction.

Yes. My application is creating commission statements for a
distributor of an insurance company. Due to all the booking that takes
place, this has to be be done in a single transaction.

> OK -- so, to make an
> omelette you have to break some eggs. :-)

:-)
Yes, and in I can live with a huge report (800 pages) consuming about
400 MB of server memory. In comparison to 1,25 GB of the IB_Cursor
version, this is quite an improvement.
(I must confess that my initial (very unoptimized) version would have
consumed 8 GB of server RAM).

>>> Sorry, I'm not Wonder Woman. :-)
>>Are you sure? ;-)
> Yup. I can't see through brick walls or dense mud and I can't move
> mountains. I can't fly. An I'd *never* pass the Swimsuit Test. :-)

But you know quite a bit about some species of birds :-)

Helen (and Nando), thanks a lot.


With kind regards,


Christian