Hi Thomas,

> * Garbage collection might kick in here

I have sweep set to manual and I do it every night just before the backup using gbak. I don't know if that answers your question about garbage collection.

> * Updating active indexes takes time as well, especially with a small page cache.

I've never played around with the page cache. There is so much caching going on in my system, I was afraid to add another, or make any changes. What size do you recommend and where would I set it? I imagine it would require a restart of the FB service (which is ok)

> Perhaps you can deactivate indexes before running the SP and activate them afterwards?

There are a ton of indexes that are being updated on each insert. Unfortunately, I cannot switch off the indexes because they are all constantly in use by the stored proc itself, as well as other users on the system.

> Depending on how you get your records for inserting, this might be inefficient due to a bad PLAN (missing index, bad index, out-dated

statistics)

The PLAN seems ok. Nothing is coming out as "NATURAL". All correct indexes are being used.

> * Are you using EXECUTE STATEMENT in your SP

Yes, about 4 of them. I have to ensure that all 4 are either successful or rolled back, so they must all be enclosed in one (mother of all) transaction(s).

Thanks for your help Thomas.

Further on my suggestion about PAUSEME: It would be cool to have a built in variable named CYCLE. So, a stored proc could be written like:

FOR SELECT GIGIDY FROM GOBELY INTO :PAR1 DO
BEGIN

IF CYCLE MOD 1000 = 0 THEN PAUSEME;
END

The PAUSEME command should allow the OS and FB to turn it's attention to other threads requesting attention. (I know... it gets complicated) PAUSEME duration should be configurable in the fb config file. Range 5 millisec to 1000 millisec.