>> If I run the sub proc to get the balance one by one though, it runs
>> at
>> 1-2 seconds. So, using IBExpert, I manually worked my way through
>> the list, and on the 5th account - 22 seconds to execute. I try the
>> very same account again, and it takes 1 seconds. I need to carry on
>> down the list and do a few more, before I can get it to go slow again.
>>
>> Any ideas what to try?


>Get the statistics on the various runs of the sub-procedure- reads, writes, fetches, and marks.

No writes. 47 000 indexed reads.

> There may be other statistics available now describing your cache hit rate. You should also check the depth of indexes (gstat).

I'm not 100% sure you mean here...

> Which type of garbage collection are you using separate thread,
> cooperative, or blended?

Using 2.5 classic, so after googling, I take it, it must be cooperative
(By the way, I haven't changed any oconfig settings when installing either)

How many tables and indexes are you touching in

>computing an account balance?

Just one.
Er, no actually two.
Having another look now at the main procedure, it's actually calculating 3 balances using table A, and then one using table B.
Excet the order is:
TableA's balance
TableB's balance
Table A's other balance
Table A's third balance with different criteria.

I changed the order to work with the three balances from table A first, then table B, and voila - the whole thing went from well over a minute down to 12 seconds ;-) I am guessing what's happening, is there is now a lot less swapping out of data between buffer and disk.

I'm not sure if that is a sign something is wrong with my setup, or whether I just need to be careful of that kind of thing when writing stored procedures?

Thanks
Maya