Wednesday, August 10, 2005

Hibernate Criteria API vs HQL Overhead

I was wondering what was the framework weight in the performance of my application, and I wanted to check at several frameworks performance. I used JMeter to benchmark a stripped application (database access through tomcat) under various loads.

My very modest test shows no significant difference between Hibernate Criteria API and HQL access for queries. Criteria might be a few milliseconds slower, but my query time will take 10x more time, even for a relatively simple query (my query is has 3 inner joins, is grouped, with a count and takes only 4ms when performed once in mysql under no load).

It seems much more important for the Framework of choice to provide good scalability, easy development and maintenance, rather than saving a few cycles, unless the Framework becomes a bottleneck.

Tags:

8 comments :

  1. Read some of the code involving Restrictions, etc, and you'll see that it's just a friendly way to produce HQL, really. It's still HQL when it gets sent to the query engine.

    ReplyDelete
  2. Anonymous, this is wrong. In CriteriaLoader class, Gavin King says:
    "Note that criteria queries are more like multi-object load()s than like HQL queries."

    If you look at the code, you will see that nowhere in the Criteria class and related classes, there is a use of the QueryTranslator class. QueryTranslator is charged of parsing HQL. Therefore, there is no HQL use.

    ReplyDelete
  3. Sorry, it generates SQL, you're right.

    http://cvs.sourceforge.net/viewcvs.py/hibernate/Hibernate3/src/org/hibernate/criterion/IlikeExpression.java?rev=1.9&view=auto

    ReplyDelete
  4. the performnace between hql Criteria and hibernate query are drastically different (10 to 20 time). Both use sql at the end. However, if the result set is big, qeury is mucher mucher faster than criteria. Be careful when using criteria!!!

    ReplyDelete
  5. Chris, what kind of data do you have to support this claim?

    ReplyDelete
  6. Actually, I was worried about the performance of HQL to begin with. For some unittest I saw 100ms+ compiletime for a moderately complex HQL query. It joined a lot of tables and had 10 or so where criteria. I have been googling about HQL overhead, and how to (if at all possible) to pre-compile HQL but did not find anyhthing. Now, I would do a slew of benchmarks if only I had the time!

    ReplyDelete
  7. "and how to (if at all possible) to pre-compile HQL but did not find anything"

    Sandows: You're looking for "named queries" (sometimes called externalized queries). They live in an external Foo.query.hbm.xml and are loaded and compiled once at initialization.

    ReplyDelete
  8. Better late than never... the reason for the performance difference is down to the way criteria are translated into raw SQL.

    Each time this happens, Hibernate generates new alias names for the tables in the query. On Oracle this means that every time a new criteria-based query is run, the database must create a QEP - query execution plan - as it is unable to match the SQL it has been given to any in its cache.

    Creating the QEP can take 30% of the time it takes for Oracle to respond to an SQL statement, so for the second and subsequent executions of the 'same' but for alias names) SQL statement, criteria has a built-in overhead that makes it 50% slower than HQL.

    For example, if the first execution of a query takes 3s you can split that into 1s (qep) and 2s (execution). The second and subsequent executions for HQL are 2s each, assuming the QEP stays in the SGA, whereas for Criteria it is 3s every time.

    Repeat that query in a loop 100 times and the HQL query will consume 201s whereas the Criteria query will consume 300s.

    The example I've quoted is Oracle - they locate QEPs in the cache by comparing the SQL statement string (which is why criterial queries miss every time), but I'd be surprised if other databases weren't doing similar things...

    ReplyDelete