Performace issues with Hibernate Criteria + join

Question

I have facing performance issue using Criteria and left join . Following is my query :

Criteria crit = session.createCriteria(CuttingAssignment.class);
    crit.add(Restrictions.eq("cuttingMachine", cuttingMachine));
    crit.createAlias("cuttingBatch", "cuttingBatch");
    crit.addOrder(Order.asc("psn"));
    crit.setFetchMode("cuttingBatch", FetchMode.JOIN);
    crit.setFetchMode("cuttingMachine", FetchMode.JOIN);
    crit.createAlias("cuttingBatch.isolatingBatches", "cuttingBatch.isolatingBatches", Criteria.LEFT_JOIN);
    crit.createAlias("cuttingBatch.isolatingBatches.assignments", "cuttingBatch.isolatingBatches.assignments", Criteria.LEFT_JOIN);
    crit.createAlias("cuttingBatch.articleType", "cuttingBatch.articleType");
    crit.setFetchMode("cuttingBatch.articleType", FetchMode.JOIN);
    crit.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);
    List<CuttingAssignment> cuttingAssignmentList = crit.list();

Now it will execute so many sql queries in DB because of that I am facing performance issue?

What are the ways to optimize Criteria Query ? Is there any difference between the criteria and HQL query ?

Thanks in advance.

You should first check the SQL requests sent by hibernate. For instance see http://stackoverflow.com/questions/1710476/print-query-string-in-hibernate-with-parameter-values. Then check you have the correct indexes for the JOIN. — Guillaume, Oct 14 '13 at 13:52
Have you checked the SQL that is used for this query by turning the log levels up and then tried to execute it manually with an EXPLAIN PLAN. — beny23, Oct 14 '13 at 13:53

Nakul Patekar · Answer 1 · 2013-10-14T14:04:12.103

0

Yes, HQL is much more efficient, simple than criteria. If one don't know SQL, I could understand using Criteria queries, but overall I prefer HQL.

edited Oct 14 '13 at 14:04

answered Oct 14 '13 at 13:53

Nakul Patekar

123
5

That's a bit of a generalisation and will not always hold true. It will depend on the specific scenario for I think badly written HQL that would easily be outperformed by a criteria query. – beny23 Oct 14 '13 at 13:55

score 0 · Answer 2 · answered Oct 14 '13 at 14:33

Please post your HSQL to compare. In general, HSQL will send only one statement to database while Criteria API may send more than one, depending on the number of lazy associations. If you want to fetch lazy associations in one query using outer join, check Criteria Query paragraph 15.5

Performace issues with Hibernate Criteria + join

2 Answers2