Stored procedure taking varying amounts of time

[timwood]

Hi

We are experiencing an unusual problem with our system that is proving
difficult to track down. The system is a 3 tier app, uses a custom
JDBC layer connected via sybase jConnect to an ASE 15-0.3 DBMS.

The problem is related to stored proc execution time. We have a
stored procedure that executes roughly every minute , that is
responsible for taking user records from a smaller table (lets call it
transactions) and migrating them to an archive table ( call it
archive_transactions). The final step in the process is a delete from
the transactions table. It also issues out a (bunch of) result sets
from within the SP that are interpreted by java and sent back to our
front end. The issue that we have is that this stored procedure (and
the processing of results from it) can vary between sub 100ms and at
times > 20 secs. In our environment we'd prefer to keep it to sub 1
second if possible since user experience starts to tank at around the
1 second level.

Details.
We've put timing information into the JDBC layer that samples
- the duration of the call to CallableStatement.executeQuery()
- the duration of each call to ResultSet.nextResult and each call to
(for instance) Callable statement.getMoreResults / getUpdateCount etc.

What we've found from this is that:
-- the time perceived by the jdbc layer of the call to executeQuery is
uniformly within the bounds of what is reasonable / acceptable
(typically <400ms)
-- during the pulling of result sets (of which there are many -- the
original implementors of this SP were big believers in returning the
kitchen sink with any SP execution) we see what you might call
inexplicable delays doing e.g. one call to ResultSet.nextREsult , and
at differing points in the result set processing ( ie -- sometimes the
delay is processing the first result set, sometimes one in the middle,
sometimes will get stuck pulling an update count out )

The other relevant data point here is that we have approx 10
deployments, and only one of them shows this problem. The other 9
deployments have one middleware layer talking to their DB, this
particular deployment has approximately 8 middleware's running on top
of it (one of our clients has much bigger requirements than the
others.).

So - the current line of thinking is that the contention brought on by
the (approx) 8 middlewares running on top of this DB is causing the
high variance and at times enormous delays in the exection of this SP.
It seems like the issue is network contention trying to pull in the
result sets, but we also haven't ruled out that the delete is causing
the contention. We have made one recent change, which was to

sp_configure 'send doneinproc tokens',0

which has made an appreciable difference to the time taken for the
degenerate runs of this SP. I'm wondering if any readers on the
newsgroup might have some suggestions for what we can look at.

Cheers
Tim