Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Terminate long query in Hibernate?

 
jay lai
Ranch Hand
Posts: 180
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all:
I have question on how to kill a long query run in Oracle 10 g,
Let's say, I have an web app using Hibernate/Oracle/Spring
On the app, i put in some term to search for, if the query run too long, i want to hit a Cancel button and it will kill the background query, not the connection to the database.

Please help.
 
Jimmy Clark
Ranch Hand
Posts: 2187
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
... i want to hit a Cancel button and it will kill the background query, not the connection to the database.


Ok. Why do you think the connection to the database is killed when you hit a Cancel button?
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

On the app, i put in some term to search for, if the query run too long, i want to hit a Cancel button and it will kill the background query, not the connection to the database.


Rather than relying on users to hit a cancel button if the query takes too long, why not add rules in your database to abort long running queries? It gives you a more dependable way of controlling the load on you DB server. Also, given you are using Hibernate, why not also use its build in row limit fuctionality to prevent large result sets?
 
jay lai
Ranch Hand
Posts: 180
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi:
I already limit the rownum, but some query take like 20 min to run. therefore, the user want to have that capability. If so, can you tell me how I am going to implement this using Hibernate/Oracle 10 g,

Many thnsk
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
20 minutes to run! That is an enormously slow query - have you done any investigation why it is so slow? If you are already limiting the amount of rows returned this suggests something is seriously wrong with your environment, data model or application logic.

Session has a cancelQuery() method you can use. But I'd find out what is so wrong with the query itself rather than just relying on users to cancel. Runnign a query like that is liable to significantly impact other users of the system.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic