Hibernate 5 Native query support - Slow response generation

Question

I am trying to fetch 1000 rows using the Hibernate createNativeQuery. The query contains two joins. When I execute the same in SQL developer I see that response comes up in less than 400 ms but from the code it takes 20 sec to get the result.

Table

Employee - id, name, country_id, dept_id

Country - id, country_name, office_count,inflation_rate

Dept - id, dept_name,manager

String queryStr = "Select e.id as id, e.name as empName, c.country_name AS countryName,
    d.dept_name AS deptName FROM  owner.Employee AS e LEFT OUTER JOIN owner.Country c
        ON e.country_id = c.id LEFT OUTER JOIN owner.Dept AS d ON e.country_id = d.id";

List<Object[]> rows  = this.getSession().createNativeQuery(queryStr).setMaxResults(1000)
    .addScalar( "id", StringType.INSTANCE )
    .addScalar( "empName", StringType.INSTANCE )
    .addScalar( "countryName", StringType.INSTANCE )
    .addScalar( "deptName", StringType.INSTANCE )
    .list();

What is the additional processing that Hibernate is doing that could be causing this delay ? Is there someway I could optimize the code ?

Answer 1

I see nothing in your Hibernate code which alone would explain unusually slow performance. The calls to addScalar() might slow things down slightly, but that should be minor.

If you want to make your query dramatically faster, then you need to think about tuning and optimization. Here is one indexing strategy which should help:

CREATE INDEX country_idx ON Country (id, country_name);
CREATE INDEX dept_idx ON Dept (id, dept_name);

This strategy assumes that Oracle would do a full table scan on the Employee table. The above two indices would then allow for rapid lookup, for each record involved in the join.