A couple of days ago I found a query which hangs when running from the application but goes fine when running from a SQL client.
The problem was in the way I was calling the query. It was:
hibernateSession.createQuery(“from Product p inner join p.attributeValues av where av.value=:value”).setParameter(“value”, someValue)
Nothing wrong with it, isnt it?
DB server background
When you implement a parametrized query the DB (MSSQL server 2008R2 in my case) uses the same query execution plan for all parameter values. Unfortunately the plan may be not efficient. The execution plan may (and I think will) fit the first parameter value. But may not fit the second.
JDBC and Hibernate reality
Theoretically you can avoid execution plan reusing by using regular java.sql.Statement instead of java.sql.PreparedStatement. But this is not possible when you work with Hibernate. Hibernate uses java.sql.PreparedStatement only internally.
hibernateSession.createQuery(“from Product p inner join p.attributeValues av where av.value=” + someValue)
Welcome hackers 😉