0

In my project , I am doing search on certain parameters using hibernate criteria query. But to return 300-400 records in soap response(after some small processing logic applied) its taking around 9-10 mins in eclipse junit test case.

After enabling logging level to trace , I observed same query sometimes executes 2 times or sometimes 3 times (This might be the issue but I am not understanding it it's the reason).
e.g. 

  select * from ( select this.id as _id.....
  select * from ( select this.id as _id.....
  select * from ( select this.id as _id.....

To fix this performance issue ,I CANNOT change anything in the DB entities and their relation.

Wanted to check if I can do anything in hibernate criteria query only to improve the performance.

Below code is used while doing search .I am doubting on below code .Just for analyzing I commented below code and executed junit but still there is no improvement in the performance.

 criteria.setResultTransformer(CRITERIA_DISTINCT_ENTITY);
 criteria.createAlias("name","name",CriteriaSpecification.LEFT_JOIN);
 criteria.createorder(..);

Can anybody suggest what I can do to improve criteria query performance.

pankaj sharma
  • 1
  • There is absolutely no way anyone would be able to make a suggestion without having the details - what is the precise query running, the structure of tables involved, indexes on the tables, the table statistics, EXPLAIN results etc. Have you tried the query directly against the database to check how the query performs? – uncaught_exception Apr 14 '17 at 14:33
  • yes after enabling logging level to trace , I executed generated query in DB. In DB its working fine.Returning data quickly..What could be your suggestion(what different things I should try to improve it using hibernate criteria) – pankaj sharma Apr 14 '17 at 14:53
  • A few things you can try or confirm you have tried. Is the 9-10 mins for the entire JUnit test case or just the method call to the query. Is this happening just for this one query? Just want to ensure a new connection isn't being established with that call. In any case 9-10 minutes sounds very unusual. Is there any tool you have on the database or any network monitoring tool that can confirm it is the database where it is waiting. – uncaught_exception Apr 14 '17 at 16:17
  • may be you are setting the fetch type to EAGER check this: – Charif DZ Apr 14 '17 at 16:38
  • http://stackoverflow.com/questions/2990799/difference-between-fetchtype-lazy-and-eager-in-java-persistence-api – Charif DZ Apr 14 '17 at 16:39

0 Answers0