Hi, I have a stored proc that calculates account balances. Most of the time it takes between 1-2 seconds, but every now and then it takes 22 seconds.
It seems to be approx. every 5 or 6 records. Ie. I have a stores procedure that returns a list of 6 accounts and their balances. I have a sub proc that works out the balance for each account. The entire list takes one and a half minutes to generate. 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? Any other info I need to provide that will help? (I have tried page buffers, sweep interval, can provide more details, but don't want to clog up this post if it's not necessary info..) (using Firebird 2.5.1 classic, ODS 11.2) (this is quite a large database at 4 gig. There are 3 million transactions in the table I am calculating balances from. I am only reading about 200 000 transaction records for these 12 accounts though. Index usage appears to be good) Thank you Maya [Non-text portions of this message have been removed]
