Hibernate HQL createQuery

Question

I'm trying to convert an old application executing SQL queries the old way as below:

java.sql.Connection connection = ....
String queryStr="select acct from Person where acct in (select acct from Document where dbcreate_date > DATEADD(hh,-12, GETDATE())) and status not in ('A','P')";
...
...
java.sql.Statement statement = connection.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        rs = statement.executeQuery(queryStr);

The above code takes about 10 milliseconds. This includes getting the database connection, creating the statement and executing the query.

I'm now using Hibenate HQL and created an HQL query like this:

Query query = session.createQuery("select p.acct   from Person p  where p.acct in (select  acct from Document d where create_date > :date and status not in ('A','P'))");

Now just this statement "session.createQuery(....)" is taking about 105 milliseconds, which is about 10 times longer then doing the whole query stuff in the old way as mentioned above.

Now I'm not really sure how Hibernate query caching works, but if I run this same HQL statement a second time, it will take about 5 milliseconds.

Now my question is why is this behavior happening using Hibernate HQL? Anyone knows what is going on inside the "session.createQuery(...)" method that takes much longer the first time but much less the second time it runs? I also noticed that that Hibernate executes the SQL against the database both times when doing "query.list()".

Thanks.

Answer 1

Try using a native query in Hibernate to compare the performance:

Query query = session.createSQLQuery("select acct from Person where acct in (select acct from Document where dbcreate_date > DATEADD(hh,-12, GETDATE())) and status not in ('A','P')");

---

On the performance:

You are not really comparing like with like with your two queries.

Your first example, straight JDBC query is just a simple query (plain Statement). The HQL, on the otherhand, has a parameter in it - so that will translate into a JDBC PreparedStatement, and the SQL for this is compiled once so that you can run it quickly if you call it multiple times, and pass down different variable values.

If you execute both multiple times, you will probably find that the HQL query will be faster on average than the simple JDBC query.

If you just execute them once, then the simple JDBC version will probably be faster than the HQL, since the HQL will be compiled initially.

There's some interesting information on Statement vs PreparedStatement here:

http://oreilly.com/catalog/jorajdbc/chapter/ch19.html

Statement Versus PreparedStatement

There's a popular belief that using a PreparedStatement object is faster than using a Statement object. After all, a prepared statement has to verify its metadata against the database only once, while a statement has to do it every time. So how could it be any other way? Well, the truth of the matter is that it takes about 65 iterations of a prepared statement before its total time for execution catches up with a statement. This has performance implications for your application, and exploring these issues is what this section is all about.

When it comes to which SQL statement object performs better under typical use, a Statement or a PreparedStatement, the truth is that the Statement object yields the best performance. When you consider how SQL statements are typically used in an application--1 or 2 here, maybe 10-20 (rarely more) per transaction--you realize that a Statement object will perform them in less time than a PreparedStatement object.

Answer 2

You can use session.createCriteria in order to avoid parsing HQL and build SQL.

Though, for complex queries it's easier just to pay some small penalty on first hit (though it's unlikely to be ~100ms), than building a complicated createCriteria structure.

http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/querycriteria.html