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"
<class name="MyRecord" table="MYTABLE">
<id column="id" name="id" type="long">
<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 ... />
(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);
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?
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.
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.