>>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) No responses, but I'm beginning to think that the best solution would be to try a solid state drive to minimise disk access time...
