Slow performance of queries and POJO mapping

[markvl]

I am comparing the performance of EJB3/Hibernate with straight JDBC calls. There seems to be an issue with the EJB3 code that maps the result set to the returned list of POJOs.

In a test of 20 threads each selecting 5000 rows, I am finding that EJB3 is about 3 times slower than the JDBC code (overall 4000 rows/sec as against 13000 rows/sec).

To exclude the underlying query being the cause, I switched to using em.createNativeQuery(sql,class) so that the exact same query was executed as with JDBC. This ran just as slow as createQuery.

I then tested using em.createNativeQuery(sql), ie without the POJO mapping, and the performance was > 10000 rows/sec.

So my conclusion is that the bottleneck is with the mapping of the result set to the list of POJOs, or perhaps with the caching of the POJOs in EntityManager (?)

Can anyone confirm this issue or suggest improvements I can make?

I am running:
JBoss 4.0.5.GA
Hibernate EntityManager 3.2.0.CR2
Hibernate Annotations 3.2.0.CR2

Thanks

[jc7442]

See

http://jira.jboss.com/jira/browse/EJBTHREE-326
and
http://www.jboss.com/index.html?module=bb&op=viewtopic&t=68954&postdays=0&postorder=asc&start=0

Problems is around the flush. If you perform a query with EJB3, a fluish is performed automatically before. It looks like that issues has been fixed in hibernate. It is not yet availabale in JBOSS EJB3 maybe for EJB3 RC10.

To check if flush is you problem, change the FlushModeType.

[markvl]

Following your suggestion, I tried setting FlushModeType to COMMIT (the only other value supported with EJB3) as follows, but the query is as slow as before.

 public List< TestData > select( int accountId, Timestamp timeScheduled ) {
 Query query = em.createNativeQuery( "select * from test_data where account_id = ? and time_scheduled > ?", TestData.class ).setParameter( 1, accountId ).setParameter( 2, timeScheduled );
 query.setFlushMode( FlushModeType.COMMIT );
 List< TestData > rows = query.getResultList();
 return rows;
 }

If there was some unexpected flushing going on, wouldn't there be additional calls to the DB server? I have hibernate.show_sql=true and from the logs there are no other queries being executed other than the above select statement.

[jc7442]

If you turn hibernate logs off, is it always so slow ?

[markvl]

Setting hibernate logging to WARN does help but there is still a signficant performance difference.

Here are the elapsed times with 20 consecutive selects each returning 5000 different rows (averaged over 3 runs excluding the 1st run). Hibernate logging set to WARN.

createQuery(hsql): 11088ms
createNativeQuery(sql): 5573ms
createNativeQuery(sql,class): 7417ms
JDBC: 5052ms

So createQuery is still more than twice as slow as direct JDBC, even with Hibernate logging disabled.

createNativeQuery(sql) is close to the JDBC time, but this excludes the time to generate the POJOs from the list of Objects returned.

Interestingly, createNativeQuery(sql,class) is considerable faster than createQuery even though the underlying query is essentially the same.

So perhaps the best approach is to go with createNativeQuery(sql,class) and then tune as required by rewriting as JDBC calls.

[jc7442]

I prefer using ejbql queries. for queries that returns a lot of elements, i do not retrieve all the elements, i uses pages. My gui is not able to display all the elements in once, so the cost of the query is hide by the time needed by the user to request the next page.

It is probably easier to maintain than native query ...