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.
