Improve Hybris Performance using Query/JDBC Hints

This might look like short, but it’s an important topic which your team can use to improve system health and performance by instructing database on how it should execute the query in best optimized way. Let’s check how we can improve hybris Performance using Query/JDBC Hints

What are Query/JDBC Hints?

Some database engines allow you to set additional hints for an underlying optimizer that in certain circumstances may speed up query execution. In addition, the Java JDBC driver allows you to set some options on the PreparedStatement object that may also help in some situations.

Oracle has support for “query hints”, which are formatted comments embedded in SQL that provide some hint for how the query should be executed. These hints are usually designed to provide suggestions to the Oracle query optimizer for how to efficiently perform a certain query, and aren’t typically needed for any but the most intensive queries.

Source : https://ci.apache.org/projects/openjpa/2.2.x/docbook/dbsupport_oracle.html

Hybris Support for Query Hints

There are two ways to use Hints supported in Hybris…

  • Query Hints (de.hybris.platform.jalo.flexiblesearch.hints.QueryHint)
  • PreparedStatement Hints (de.hybris.platform.jalo.flexiblesearch.hints.impl.JdbcHints)

How to use Query Hints?

Hybris Platform provides implementation of the QueryHint interface for use in the SAP HANA database. The HanaHints class acts as a factory builder for any hints SAP HANA supports.

And of-course, you can implement your own Custom QueryHint implementation if database you are using supports hints.

To apply these hints to database query is quite simple… use as below :

HanaHints hints = HanaHints.create("IGNORE_PLAN_CACHE");
// OR
HanaHints hints = HanaHints.create("IGNORE_PLAN_CACHE", "USE_OLAP_PLAN");

// AND Add to FlexibleSearchQuery
FlexibleSearchQuery fQuery = new FlexibleSearchQuery("SELECT {PK} FROM {User}");
fQuery.addHints(hints);

How to use JDBC Hints?

Hybris Platform provides the JdbcHints class that allows you to create hints that operate on the underlying PreparedStatement object.

You can use  JdbcHints#preparedStatementHints() factory method to produce an instance of the PreparedStatementHint interface to gain access to the PreparedStatement object.

PreparedStatementHint hints = JdbcHints.preparedStatementHints().withFetchSize(50).withQueryTimeOut(200).withHint(ps -> {
        ps.setCursorName("fooBar")
        return ps;
    });

//ADD TO FlexibleSearchQuery
FlexibleSearchQuery fQuery = new FlexibleSearchQuery("SELECT {PK} FROM {User}"); fQuery.addHints(JdbcHints.preparedStatementHints().withQueryTimeOut(200));

Hope this helps you fine tune your queries and hence the performance.

Thanks for reading this out.

OLD POSTS

4140cookie-checkImprove Hybris Performance using Query/JDBC Hints

Leave a Reply

Your email address will not be published.