How to insert “Optimizer” tooltip into Hibernate request api criteria

Question

I have a hibernation request that dynamically aggregates using api criteria. it generates queries that are unbearably slow if they run as is.

but I noted that they are about 1000% faster if I add / * + FIRST_ROWS (10) * / to the request. how can i do this using api criteria?

I tried the criteria .setComment (..), but that seems to be ignored.

in dormant documents, 3.4.1.7. Hint pointers are mentioned, but it clearly states: "Note that these are not SQL query hints"

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

Answer 1

You can change the optimizer mode at the session level:

ALTER SESSION SET optimizer_mode = FIRST_ROWS; 

Either before your request, or after it returns to the default value ( ALL_ROWS ) or in your case, since 99% of the requests will benefit from it, you can change it at the schema level (using the ON LOGON trigger for the example) or even at the level instance (change init parameter).

Answer 2

I managed to add an Oracle hint by adding ProjectionList criteria 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 the order of ProjectionList

Answer 3

I have another general solution that should work for every Criteria request:
use the standard comment and Hibernate interceptor, modifying the resulting SQL into the database.
(I used it with Hibernate 3.3, but should be used for each version, registration of the Interceptor may be different.)

In your request code use:

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

Record an interceptor to modify 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 for Oracle, but it needs to be easily configured for each DBMS. Perhaps you can / should create a constant for the hint marker "$ HINT $".
Logging should be done too (so you can easily see the correct interceptor call), I left it higher 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 Hibernate 3.3 docs):

A session-bound interceptor is specified when a session is opened using one of the overloaded methods of SessionFactory.openSession () receiving an interceptor.

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

The SessionFactory interceptor is registered using the Configuration Object prior to creating the SessionFactory. If the session is explicitly opened, indicating the use of an interceptor, the delivered interceptor will be applied to all sessions opened from this SessionFactory. SessionFactory interceptors should be threads safe. Make sure that you do not save session-specific states, as multiple sessions will use this interceptor potentially simultaneously.

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

Answer 4

The problem is that the tooltip syntax is not a comment, it just looks a bit like one. This really should go between SELECT and selected columns, while setComment() adds a comment before SELECT .

Other than that, there are no silver bullets. FIRST_ROWS not a performance tool. It may take longer to return all rows. Of course, in a user-centric program, getting the first ten lines may be all we need to do.

But, whenever you refuse it, if you want to use the Oracle prompt syntax, you will need to go down the Native SQL route.

What else can you do? I have no experience setting up Hibernate yet. Once, when I had such a task, the query captured rows from a number of tables to create an instance of an object with a large number of subtypes. Each subtype was a separate table. The query created by Hibernate had many OUTER JOINs that confused the optimizer. Destroying this monster into several focused queries (one per subtype), which only INNER JOINs used, resulted in a hundredfold reduction in search time.

This may not be practical for you. But basically, look at the Hibernate query and see if it can be implemented in a different, more efficient way.