On Wed, Sep 12, 2012 at 3:06 AM, Maya Opperman <maya@...>wrote:

>
> >>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.
>
> >>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. There may be other statistics available now describing
your cache hit rate. You should also check the depth of indexes (gstat).
Which type of garbage collection are you using separate thread,
cooperative, or blended? How many tables and indexes are you touching in
computing an account balance?

Good luck,

Ann

>

[Non-text portions of this message have been removed]