CODEWITHSUNDEEP
javasql-serverhibernatecriteria-api
Malte
Malte

Reputation: 174

How to add SQL-Server query hint in Hibernate Criteria API

we are using MS SQL-Server and Hibernate Criteria API.

Recently I discovered a query that suffers from parameter sniffing and so I would like to add a OPTION (RECOMPILE) as query hint. But although Hibernate seems to support query hints for Criteria API the added 

criteria.addQueryHint("OPTION (RECOMPILE)");

does not seem to have any effect (the logged SQL does not contain any hints).

Any ideas?

Upvotes: 2

Views: 4011

Answers (2)

KevinC
KevinC

Reputation: 335

Hinting was added to the org.hibernate.dialect.SQLServer2012Dialect class, so make sure the properties have hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect

Using the javax.persistence.EntityManager:

var query = entityManager.createQuery(criteriaQuery).unwrap(org.hibernate.query.Query.class);
query.addQueryHint("RECOMPILE");
return query.list();

The dialect will automatically prepend OPTION to the argument.

Upvotes: 3

Malte
Malte

Reputation: 174

It seems that Hibernate Criteria API only supports query hints for Oracle databases.

The best solution I found was implementing the support for SQL-Server on my own (you could also do something like criteria.add(Restrictions.sqlRestriction("1=1 OPTION (RECOMPILE) ")); but that is pretty much a hack and does not work if you e.g. want to add an ordering to the query) .

I used the Hibernate implementation for Oracle as blueprint (have a look at Oracle8iDialect). For my use case it was sufficient to implement a version that appends the query hint to the end of the query (as the OPTION-clause is always at the end of the query). 

package de.mystuff.hibernate;

import java.util.List;

import org.hibernate.annotations.common.util.StringHelper;
import org.hibernate.dialect.SQLServer2008Dialect;


/**
 * Special version of {@link SQLServer2008Dialect} which adds a simple query hint support.
 */
public class MySQLServer2012Dialect extends org.hibernate.dialect.SQLServer2012Dialect {


    /**
     * {@inheritDoc}
     * <p>
     * Currently this is a pretty simple query hint implementation. It just concatenates all SQL hints and adds them to the end of the query. This is fine for
     * e.g. {@code OPTION (RECOMPILE)}.
     */
    @Override
    public String getQueryHintString(String sql, List<String> hints) {
        if (hints.isEmpty()) {
            // no query hints at all
            return sql;
        }

        // concatenate all hints
        final String hint = StringHelper.join(", ", hints.iterator());

        if (StringHelper.isEmpty(hint)) {
            // all query hints are empty
            return sql;
        }

        return sql + " " + hint;
    }

}

Remind that you have to tell Hibernate to use your query hint supporting dialect:

hibernate.dialect=de.mystuff.hibernate.MySQLServer2012Dialect

Upvotes: 2

Related Questions