Hibernate, DB2 - Slow running queries

Question

I'm doing a search on one of my tables (legacy database) and recieving a horrible time here. The query is build by criteria api of hibernate, e.g.:

Criteria crit = getSessionFactory().getCurrentSession().createCriteria(P1.class);
crit.add(Restrictions.sqlRestriction("{alias}.compno like ?", "%" + s + "%", new StringType()));
crit.setMaxResults(25);
crit.setFirstResult(0);
crit.addOrder(Order.asc("compno"));
crit.list();

As you can see I'm already doing a paging here to improve the perfomance. This criteria needs ~6 seconds on average.

Well the native query which looks like this

select * from SCHEM.P1 where compno like '%100%' order by compno fetch first 25 rows only

takes only 10 ms which is a huge difference imo. Why does the criteria runs so slow? Need I switch back to the native sql query?

Good point on the comments:

Yes there are some relationships which I didn't had on the scope:

<set name="pI" table="P12" lazy="false">
    <key column="awcompno" update="false" />
    <one-to-many class="org.gee.hibernate.P12" not-found="ignore"/>
</set>
 <one-to-one name="info" class="org.gee.hibernate.P13" />

<set name="ma" table="P03" lazy="true" schema="SCHEMP" mutable="false" >
    <key column="macountry" property-ref="land" update="false" />
    <one-to-many class="org.gee.hibernate.P03" not-found="ignore" />
</set>


<set name="users" table="P15" lazy="true">
    <key column="apcompno" update="false" />
    <one-to-many class="org.gee.hibernate.P15" not-found="ignore"/>
</set>

Need more information: Do you use one-to-many / many-to-one / many-to-many in your entity? — Christian Kuetbach
– Christian Kuetbach, Commented Dec 7, 2010 at 10:58

Christian Kuetbach · Accepted Answer · 2010-12-07 11:42:35Z

3

My tip is:

<set name="pI" table="P12" lazy="false">
    <key column="awcompno" update="false" />
    <one-to-many class="org.gee.hibernate.P12" not-found="ignore"/>
</set>

This collection is not lazy. That may be your bottleneck.

Do you need all informations? You may read fields of your entity with hibernate, if you only want to read the IDs.

answered Dec 7, 2010 at 11:42

Christian Kuetbach

16.1k5 gold badges45 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

onigunn Over a year ago

Yes you are right here. Removing this relation solves the slow query time. Thanks for hinting me in this way!

Leon Katsnelson · Accepted Answer · 2010-12-08 03:54:18Z

1

IBM pureQuery has some really nice facilities for accelerating Hibernate applications that work with DB2. The other benefit ... it makes it much easier to debug as it allows you to correlate your SQL and your Java code.

Take a look at this article http://www.ibm.com/developerworks/data/library/techarticle/dm-1008hibernateibatispurequery1/index.html

answered Dec 8, 2010 at 3:54

Leon Katsnelson

4503 silver badges5 bronze badges

Comments

octav · Accepted Answer · 2010-12-07 11:08:45Z

0

I'd say have a look at the DB logs to check what are the exact SQL instructions that get executed. Hibernate maybe loading more than just the native query you would expect, as it may load eager collections etc.

So - enable Hibernate query logging, or better yet, check the DB logs to see what gets executed.

answered Dec 7, 2010 at 11:08

octav

1,1817 silver badges6 bronze badges

Collectives™ on Stack Overflow

Hibernate, DB2 - Slow running queries

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related