0

I am working on a new project on some existing code . It uses HibernateTemplate.findByNamedQueryAndNamedParam to invoke a stored procedure in the database .

When I execute the stored procedure on the database , it executes in 2 or 3 seconds . But I execute it via the HibernateTemplate method , it takes anywhere between 2 to 34 minutes .

When I turn showsql on , I see that there are thousands of select statements being triggered . Any pointers on possible reasons for why this could happen .

ben75
  • 29,217
  • 10
  • 88
  • 134
Zak
  • 111
  • 3
  • 11

1 Answers1

0

Your problem may be caused by a bad configuration of 2nd level cache and query cache.

Check that expiration time of your query cache is the same as the expiration time for the 2nd level cache - for all entities fetched by your query -. (look into ehcache config file -or any other 2nd level cache provider you are using-)

ben75
  • 29,217
  • 10
  • 88
  • 134
  • Thanks . Where do I set the query cache expiry times – Zak Jul 09 '15 at 15:53
  • If you are using ehcache there is file named ehcache-config.xml (or something like that) with an element for the query cache : – ben75 Jul 09 '15 at 16:00
  • Yes we are using ehcache . But I don't have that set up . Wil give it a shot now . – Zak Jul 09 '15 at 16:06
  • I assume once this is set I should use the criteria.setcachesble(true ) or query.setcacheable(true) ? – Zak Jul 09 '15 at 16:08
  • You can try this : hibernateTemplate.setCacheQueries(false) to see if it change anything (it will be a good indication that the problem is there.) – ben75 Jul 09 '15 at 16:11
  • Ok will try that ! Thank you . Also the entity that the result of the store procedure is trying to populate with its results , has lot of child entities at multiple levels . And the mappings are set to lazy=false and fetch =select . Could this be a cause for all those select statements – Zak Jul 09 '15 at 16:16
  • I set the setcachequeries(false) and I still see the select statements . I am wondering if it is because of some eager loading – Zak Jul 09 '15 at 16:19
  • It's difficult to say without seeing your code/mapping/db schema – ben75 Jul 09 '15 at 16:19
  • I can send some configuration to you - is there a way to attach files here – Zak Jul 09 '15 at 16:36
  • List entities = session.createcriteria(ParentEntity.class).list(); triggers thousands of select statements . When RHS result is copied into LHS . I don't have the freedom to change the hibernate mappings . Is there a way to prevent this without changing the mapping attributes lazy and fetch – Zak Jul 09 '15 at 23:33