How to use database indexes with Hibernate queries

Question

I am developing an application using Hibernate, Derby, and Java 6. I have a database table that contains about 2.5 million records and I have a query that uses the combination of two fields to retrieve a record from this table.

Therefore, I have defined an index on these two fields in order to speed up queries. My Hibernate mapping file looks as follows:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="...">
    <class name="MyRecord" table="MYTABLE">
    <id column="id" name="id" type="long">
      <generator class="native"/>
    </id>
    <property column="myIndex" index="MY_IDX" name="myIndex" not-null="true" type="long"/>
    <property column="myCode" index="MY_IDX" length="2" name="myCode" not-null="true" type="string"/>
    <!-- More properties here -->
    <many-to-one ... />
    </class>
</hibernate-mapping>

(Note that myIndex is just the name of a field, whereas the defined daabase index is MY_IDX.)

I then execute the query as follows:

...
Session session   = ...;
Criteria criteria = session.createCriteria(MyRecord.class);
criteria.add(Restrictions.eq("myIndex", myIndex));
criteria.add(Restrictions.eq("myCode", myCode));

List result = criteria.list();
...

The above query finds the correct result (one record, if present) but in my opinion it takes way too long (between 9 and 13 seconds): I do not know the details of how Hibernate manages database indexes, but I would expect a query on an indexed key to run quite fast. So even with 2.5 million records, the running time of the query should be IMO much smaller.

Question: can it be that I have configured my database index incorrectly and that no index is used at all? Or if an index has been created in the database, am I doing something wrong in my query?

UPDATE

I think that Hibernate had not created any index at all. I have created the index in Derby directly (connected to it via SQuirrel) and executed the above query again: it is now very fast (7 milliseconds). I am a bit confused: either the index attribute in the Hibernate mapping file has no use at all, or I am doing something wrong.

Hibernate doesn't really take an interest in your indexes. You should use a profiler to determine if the time taken is in the execution of the query. If so you'll need to look at the query plan to determine if the expected index is used. See here for tips on this wiki.apache.org/db-derby/PerformanceDiagnosisTips — Alex Barnes
– Alex Barnes, Commented Aug 25, 2012 at 22:14
Thanks a lot for the hint. Does it mean that Hibernate does create the index (according to the mapping file) but does not use them when constructing a query? — Giorgio
– Giorgio, Commented Aug 26, 2012 at 9:37
If you let Hibernate deploy the schema it will create the index. But at the point at which you execute the query Hibernate will just generate SQL from the HQL and the mappings which it will send to the database. The database will then determine how best to execute the query. — Alex Barnes
– Alex Barnes, Commented Aug 26, 2012 at 14:04

Alex Barnes · Accepted Answer · 2012-08-26 17:30:45Z

If you let Hibernate deploy the schema it will create the index. Hibernate doesn't really take an interest in your indexes after deploying the database. At the point at which you execute a query Hibernate will generate SQL from the provided HQL and the mappings which it will send directly to the database. The database will then determine how best to execute the query.

You should use a profiler to determine if most of the time taken is in the execution of the query. If so you'll need to look at the query plan to determine if the expected index is used. See here for tips on this wiki.apache.org/db-derby/PerformanceDiagnosisTips.

If you use an SQL query you are able to use query hints to tell your database which index it should use. This is useful if the query plan reveals that the index you're expecting to be used isn't. Unfortunately Hibernate doesn't have support for query hints out of the box using HQL.

This post provides some useful background on this topic and explains how to extend Hibernate to use a query hint.

Collectives™ on Stack Overflow

How to use database indexes with Hibernate queries

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related