--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:

>
> At 12:27 PM 27/06/2006, Adam wrote:
> >* Create an output parameter called recordsprocessed, increment it
> >and suspend inside your for select loop.
>
> No, not for this job. This is an executable SP. Its purpose is to
> perform DML. We don't want to slow it down just to feed a progress
> bar. It's a bit like my Nana, who always use to sew faster when the
> thread was running out out. The supposed "gain" from getting output
> during processing might provide a way for the application to display
> progress but it doesn't do a single thing for the root cause of the
> performance attenuation.

Agreed. As I warned in the subsequent paragraph, it will significantly
slow it down because the procedure will need to suspend while each
record is returned. For smaller files and batch processes and only
suspending every n records, the reduction in speed from the suspends
may not be significant.

>
> A big part of the performance hit here is occurring because the input
> file is huge. Processing the entire file (up to half a million
> records involved) in one call to the SP will cause the undo log to
> build up and build up until the engine simply abandons it, i.e. all
> that resource deprivation comes to naught. The undo log is a Good
> Thing (TM) for a reasonable-sized batch, because it allows the engine
> to clear out new records in case a rollback happens, thus avoiding
> the survival of garbage when inserts are abandoned.
>
> But if the undo log itself is abandoned by the engine because it has
> consumed more than a reasonable amount of resource, there will be
> garbage - a lot in this case. These inserts are already going into a
> table that is pushing the limits, even *without* the gross extra

garbage.

GBak uses a technique to tell Firebird not to maintain an undo log,
part of the connection or transaction from memory. Could this sort of
thing be employed here?

Adam