How to insert an "Optimizer hint" to Hibernate criteria api query

Question

i have a hibernate query that is dynamically put together using the criteria api. it generates queries that are unbearably slow, if executed as-is.

but i have noted they are about 1000% faster if I prepend /*+ FIRST_ROWS(10) */ to the query. how can i do this with the criteria api?

i tried criteria.setComment(..), but this seems to be ignored.

in the hibernate docs, 3.4.1.7. Query hints are mentioned, but it clearly states: "Note that these are not SQL query hints"

the result of the query will be paginated, so in 99% of the cases i will display the results 1-10.

Answer 1

There are two types of "hints", which can be confusing:

1. JPA query hints (link), which can be set using the javax.persistence.Query#setHint method and are consumed by the Java Persistence provider (e.g. Hibernate/EclipseLink). These are NOT database query hints and cannot be used to directly provide optimizer hints to the database.

2. Hibernate query hints (link), which can be set using the org.hibernate.query.Query#addQueryHint method and can be used to provide optimizer hints. The exact support depends on the used dialect; but the Oracle dialect handles them well.

If you only have a JPA query (e.g. using the EntityManager), but need to set an optimizer hint, you can first unwrap the JPA query into a Hibernate query and then add the query hint:

var jpaQuery = entityManager.createQuery(...)
jpaQuery.unwrap(org.hibernate.query.Query.class).addQueryHint(...);
return jpaQuery.getSingleResult();

Answer 2

We can use addQueryHint on Criteria object

Criteria c = getCurrentSession().createCriteria(AbcEntiry.class);
c.addQueryHint("first_rows(1500)");

Answer 3

I have another generic solution, that should work for every Criteria query :
use a standard comment and an Hibernate Interceptor changing the ultimate SQL to the database.
(I used it with Hibernate 3.3, but should be useable for every version, registering of the Interceptor may be different.)

In your query code use:

criteria.setComment("$HINT$ push_pred(viewAlias)");

Write an Interceptor to change to SQL text (this one uses commons.lang3.StringUtils):

public class HibernateEntityInterceptor extends EmptyInterceptor {

@Override
public String onPrepareStatement(String sql) {
    if (sql.startsWith("/* $HINT$")) {
        String hintText = StringUtils.substringBetween(sql, "/* $HINT$", "*/");
        sql = sql.replaceFirst("select ", "select /*+" + hintText + "*/ ");
    }
    return sql;
}

Above is for Oracle, but should be easily adjustable for every DBMS.
Maybe you can/should create a constant for the hint marker "$HINT$".
Logging should be done, too (so you can easily see the correct calling of the Interceptor), I left it out above for simplicity.

The Interceptor must be registered. In Spring this is done in applicationContext.xml:

<bean id="entityListener" class="your.package.HibernateEntityInterceptor"/>

<bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
    <property name="entityInterceptor" ref="entityListener"/>
    [...]

Or (copy from the Hibernate 3.3 docs):

A Session-scoped interceptor is specified when a session is opened using one of the overloaded SessionFactory.openSession() methods accepting an Interceptor.

Session session = sf.openSession( new HibernateEntityInterceptor() );

A SessionFactory-scoped interceptor is registered with the Configuration object prior to building the SessionFactory. Unless a session is opened explicitly specifying the interceptor to use, the supplied interceptor will be applied to all sessions opened from that SessionFactory. SessionFactory-scoped interceptors must be thread safe. Ensure that you do not store session-specific states, since multiple sessions will use this interceptor potentially concurrently.

new Configuration().setInterceptor( new HibernateEntityInterceptor() );

Answer 4

I was able to put in a Oracle hint by a adding a ProjectionList to the criteria.

ProjectionList proList = Projections.projectionList();
proList.add(Projections.sqlProjection("/*+INDEX_DESC(this_ MY_INDEX_NAME)*/ 1 as MYHINT",
    new String[]{},
    new Type[]{}));
//add properties from your class
proList.add(Projections.property("field1"));
proList.add(Projections.property("field2"));
proList.add(Projections.property("field3"));
c.setProjection(proList);

c.list() returns List<Object[]> in order of ProjectionList

Answer 5

You could modify the optimizer mode at the session level:

ALTER SESSION SET optimizer_mode = FIRST_ROWS;

Either just before your query and then putting it back to its default value (ALL_ROWS) or in your case since 99% of the queries would benefit from it you could modify it at the schema level (with an ON LOGON trigger for exemple) or even at the instance level (modify the init parameter).

Answer 6

The problem is that the hint syntax is not a comment, it just looks a bit like one. It really has to go between the SELECT and the selected columns, whereas setComment() prepends the comment before the SELECT.

Beyond that, there are no silver bullets. FIRST_ROWS is not a performance enhancing tool. It may end up taking longer to get all the rows back. Of course, in a user-facing program retrieving the first ten rows may be all we need to do.

But, which ever way you bounce it, if you want to use Oracle's hint syntax you'll need to go down the Native SQL route.

What else can you do? I don't (yet) have much experience tuning Hibernate. The one time I have under such a task the query was grabbing rows from a whole bunch of tables to instantiate an object with lots of sub-types. Each sub-type was a separate table. The query generated by Hibernate had many OUTER JOINs, which confused the heck out the optimizer. Breaking that monster into several focused queries (one per sub-type) which used only INNER JOINs produced a two hundredfold reduction in retrieval time.

This may not be of any immediate use to you. But the principle is, look at the Hibernate query and see whether it can be implemented in a different, more efficient way.