>> 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
