I have a stateful session bean that is executing (on the OC4J Oracle appServer) an oracle query via a java sql statement. Is there an easy way to kill the query process if the query takes too long (the user asks for too big of a query). I have used a querytimeout method and this helps, but there are times when this is too short a time period for other queries, and too long for others. This is a problem since merely pressing the stop button on the browser may terminate the servlet from waiting for results back from the stateful session bean, but the stateful session bean continues to remain suspended as it tries to complete the query to the database on the application server. Any references to the stateful session bean via the servlet or jsp page hangs until the query is complete.
Any help would be appreciated.
Without knowing the intracies of your requirements, the approach you are suggesting seems abrubt and not very user-friendly. Is there a way to guide your users and narrow their queries. Good GUI and client workflow design can often solve the problem of runaway DB queries.
Once the query reaches DB servers, the onus is on DB servers and not your application. It can not be killed, may be you can take the DB server in quincient mode and bring it online again, for killing the user session. You should optimize the query before sending it to DB server, you can see the query performance by SET EXPLAIN ON statement.
You have two methods in the Statement interface that can help you:
Statement stmt= <stmt.setQueryTimeout(timeOut);
If you need different timeOut in two queries, just call the setQueryTimeOut() with the timeOut that you need before calling to the execute() method
Your only option is to terminate the Oracle user process runnig the query but this is probably not what you want.
Most likely, the sheer size of the result set that your query returns is the problem here, since all this data are passed over SQLnet to your app server. In which case you may try to change your method signature to provide for "paging" of the result set, e.g., getResult( lastRowKey, nRows). To implement it you can either write a stored procedure to enumerate over the result set or use
SELECT * from SELECT ... ORDER BY key
where key>?lastRowKey and rownum<?nRows