At 08:24 AM 10/03/2006, The Wogster wrote:

>I think the problem is that this all happens in one massive transaction,
>and then it needs to commit the whole thing at the end. I don't
>remember where you can start and end transactions within a procedures,

You can't start and end transactions within procedures at all.

>
>but if you could commit every 10,000 records or so, then it wouldn't
>need to try and commit the whole thing at the end.

I agree the size of the whole operation is waaay too much for a
machine with only 512 Mb of RAM. The procedure has to be re-thought,
in terms of a parameterised SP that takes and returns arguments to
enable the thing to be partitioned into realistic chunks. With such
small memory resources, unspecified n operations * 700,000 iterations
in one transaction is not even close to realistic. 7000 per
transaction would be closer, perhaps less.

>Also, if you just let it run, then it would end, eventually.

Or not. If the CPU is stuck on 100% perpetually then there's
something loopy going on...literally. It's probably in the caching,
but look at all *your* loops, both in the application and in the SPs,
as a reality check.

Some clarification of terminology would be useful.

1. What is this "thin" table?

2. What DO you understand about commits when you say "I can
understand that this is due to the commit being fired by Firebird at
the end" ? Firebird doesn't "fire commits" at all.

3. "But does this mean that firebird has some issues with rollback segment?"

What's "rollback segment"? Are you using user savepoints in the
app? Or internally handled exceptions in the SP? Or are you
referring to the undo log? (Undo logging will always have issues
when memory is inadequate. With this volume of inserts, the engine
would abandon it fairly early...the big impact of an absent undo log
would occur on a rollback.)

Helen