Why hibernate is slower in fetching lesser number of rows?

Question

I am following a hibernate tutorial listed here http://docs.jboss.org/hibernate/orm/5.1/quickstart/html_single/#tutorial-native .

I have modified the code to use local mysql as the database. After that, I populated the database table with 10000 rows.

I compared the latencies of two types of DB reads - one via hibernate native query; other via direct JDBC and creating objects from ResultSet.

I found it very strange to see that hibernate is very slower in comparison to my custom JDBC and java object mapping implementation. This happens when the number of rows fetched are below 10000. For e.g fetching 10-100 rows with my method takes 3-18ms while hibernate takes 280-320ms around. But when I try to fetch >10K rows, hibernate gets efficient.

Can someone please explain what hibernate is doing which causes this much latency?

My hibernate.cfg.xml looks like below

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
    "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
    "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>

    <!-- Database connection settings -->
    <property name="connection.url">jdbc:mysql://localhost:3306/fquick-task-manager?useSSLx`=false</property>
    <property name="connection.username">root</property>
    <property name="connection.password"/>

    <!-- JDBC connection pool (use the built-in) -->
    <property name="connection.pool_size">15</property>

    <!-- SQL dialect -->
    <property name="dialect">org.hibernate.dialect.MySQLDialect</property>

    <!-- Disable the second-level cache  -->
    <property name="cache.provider_class">org.hibernate.cache.internal.NoCacheProvider</property>

    <!-- Echo all executed SQL to stdout -->
    <property name="show_sql">true</property>

    <!-- Drop and re-create the database schema on startup -->
    <property name="hbm2ddl.auto">update</property>

    <mapping resource="org/hibernate/tutorial/hbm/Event.hbm.xml"/>

</session-factory>
</hibernate-configuration>

My Test function looks like below

public void testBasicUsage() {

    // Using JDBC
    Session session = sessionFactory.openSession();
    session.beginTransaction();
    String queryStr = "";
    try {
        long start = System.currentTimeMillis();
        Statement statement = ((SessionImpl) session).connection().createStatement();
        queryStr = "select * from Events where EVENT_ID < 10";
        ResultSet rs = statement.executeQuery(queryStr);
        List<Event> events = new ArrayList<Event>();
        while (rs.next()) {
            Long eventId = rs.getLong("EVENT_ID");
            String title = rs.getString("title");
            Date myDate = rs.getDate("EVENT_DATE");
            Event event = new Event(eventId,title ,myDate);
            events.add(event);
        }
        long end = System.currentTimeMillis();
        long timeTaken = end - start;
        System.out.println("Query took " + timeTaken + "ms");

    } catch (SQLException e) {
        System.out.println("Error in statement creation");
    }
    session.getTransaction().commit();
    session.close();

    // Using Hibernate
    session = sessionFactory.openSession();
    session.beginTransaction();
    queryStr = "select * from Events where EVENT_ID > 20 & EVENT_ID < 30";
    long start3 = System.currentTimeMillis();
    session.createSQLQuery(queryStr).list();
    long end3 = System.currentTimeMillis();
    long timeTaken3 = end3 - start3;
    System.out.println("Query took " + timeTaken3 + "ms");
    session.getTransaction().commit();
    session.close();

}

Answer 1

1: based on your hibernate properies, Hibernate is going to try and recreate the database schema, which requires querying metadata, calculating differences, applying differences (if necessary), which is not being done in straight JDBC. Any comparison is invalid.

2: Any sort of performance comparison between multiple techniques requires a warmup period, because there's all sorts of behind-the-scenes setup (connecting to the database, parsing SQL statements, interpreting metadata, etc.). You also including a connection pool size which, depending on how you used hibernate, might give hibernate an unfair advantage later.

3: You didn't include either the Hibernate mapping file or an object with JPA annotations on it. Even though your base SQL statement here does no joins, if you've defined relationships in the object, Hibernate is going to take that into account, so again, might not be a fair comparison.

4: During startup, Hibernate is going to connect to the database, load the mapping files/objects, make sure everything aligns and that the database and persistence objects are usable. If you have named queries with bad syntax, incorrec table/column names, etc., it should be identified. It also takes your persistence object and does some dynamic byte generation/CGLIB stuff to make the object (at least under the sheets) more than just a plain POJO (at least that's what we view it as).

5: When Hibernate is asked to fetch the data, it'll create the SQL statement and bind the results directly into the object. So obviously some overhead though once it's done it's going to more efficient. In your straight JDBC loop, each time you're requiring a search to find which column is being returned by the getLong, getString, getDate, etc rather than figure out the column numbers once and then use the direct index subsequently. Right here is the likely culprit, that Hibernate takes a little time to get everything set up efficiently, and then eventually surpasses the raw JDBC because of the efficiencies in creating the objects.

6: As an abstraction layer, Hibernate is always going to be slower than a well-written direct-JDBC application (which this example is not). However, the development time is less, the bugs are less, and overall quality of code should be better. You just have to work within its limitations.