At 21:50 17/08/2008, you wrote:

>> If that does turn out OK, then is there a possibility that the queries
>> are running under a WAIT transaction, and so they are waiting to see
>> if the conflicting transaction commits or rolls-back before returning.
>
>OK this sounds logical, but how can I detect these WAIT transactions?
>And when this is the problem, why it is this problem?

The most common cause of habitual, gradual slowdown is never-ending transactions with pending updates that are locking out other transactions that want to update or delete the same rows. However, your garbage stats are fine, indicating that garbage buildup isn't an obvious factor.

It is normal behaviour for transactions to encounter locks on records that they want to write changes to. How the lock conflict is resolved depends on the parameters set in each transaction. WAIT is one setting - it instructs the transaction to keep waiting until the "busy" row has been committed and then attempt to do what it wants to do. The other face of WAIT is NO WAIT - where the change attempt fails with a conflict exception immediately. If your application is not showing you lock conflict messages then you can well suppose that the transactions encountering the bottleneck are WAIT transactions.

Since this is an intermittent problem, the answer does lie somewhere in what the application software is doing. From your description, you seem to be getting delays on SELECTs (moving from one set of data to another). This suggests that there could be some pessimistic locking involved. Some application developers (wrongly) use pessimistic locking techniques with WAIT transactions to avoid having to handle lock conflicts. In so doing, they create the exact situation you have described as soon as multiple users get working on overlapping sets.

You mentioned that you thought a lot of triggers were involved....well, triggers don't fire on SELECTs. However, if the app implements pess. locking from the client side (by performing a "dummy update" on every row that the user gets focus on) then update triggers *will* fire in response to the dummy update and lock conflicts will propagate.

So - even if you could get an eyeball on what transactions are doing - as one can in Fb 2+ by implementing a client routine that reads and interprets the MON$ tables - there is nothing you as an end-user (rather than the developer of the software) can do to cure the problem. What's needed is for you to get as exact a description as possible of the conditions surrounding the undesirable events and put in a bug call to the vendor of your ERP system.

./heLen