I am trying to get some general idea of the performance of hibernate relative to straight JDBC and I am getting some very strange results and have consulted various documents on the hibernate.org site that haven't revealed my problem (forgive me if I have missed any). My initial problem was that Hibernate was doing unrealistically bad when compared against raw JDBC calls and hand coded mapping.

For my application, we are mainly interested in the O/R mapping ability and less about persistent objects. We want to use hibernate as a way to pull objects out of a table as fast as possible. Right now we are only interested in a single table access (i.e. turning the columns from a single table into a fully populated objects).

Now for reading using a statelessSession I find that I get about a 2X slowdown when compared to using raw jdbc and "normal" reflection (i.e. hibernate 3700 Tps while raw jdbc 8500 Tps). The VERY weird thing is that hibernate is actually doing MUCH better (~7X!!) on writing (hibernate 2500 Tps vs jaw jdbc 330 Tps!!!). I do a commit on both the hibernate (session.commit()) and the jdbc (connection.commit()) every 1000 records written). I can't imagine why my jdbc code is running slower it is so simple and it reuses a preparedStatement object but I am happy to get MORE performance! :-)

So I guess I am most worried about the 2X slowdown (although this was worse when I used a regular session and not a statelesssession).

Just wanted to know if anyone has any suggestions on what I can do for the reading side of things to speed things up.

Hibernate version:
Hibernate 3.1 beta 2

Mapping documents:
<?xml version='1.0'?>
<!DOCTYPE hibernate-mapping PUBLIC
'-//Hibernate/Hibernate Mapping DTD 3.0//EN'
'http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd'>
<hibernate-mapping package='myPackage'>
<class name='myClass' table='myTable' lazy='false'>
<id name='id' column='ID'/>\n");
<property name='f1' column='c1'/>
<property name='f2' column='c2'/>
<property name='f3' column='c3'/>
<property name='f4' column='c4'/>
..... (15 columns/fields in all)
</class>");
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():
For reading:

ScrollableResults iter = session.createQuery("from " + className).scroll(ScrollMode.FORWARD_ONLY);
int count = 0;
int increments = 1000;
long[] readTimes = new long[9];
readTimes[count++] = System.currentTimeMillis();
while (iter.next()) {
Object obj = iter.get(0);
if (doTimings && count % increments == 0) {
readTimes[(int) Math.round(count / increments)] = System.currentTimeMillis();
}
count++;
}
session.close();


for writing:

int count = 0;
long[] writeTimes = new long[9];
writeTimes[count++] = System.currentTimeMillis();
session.beginTransaction();
for (int i = 0; i < objList.size(); i++) {
Object o = objList.get(i);

session.insert(o);

if (doTimings && count % increments == 0) {
writeTimes[(int) Math.round(count / increments)] = System.currentTimeMillis();
session.getTransaction().commit();
session.beginTransaction();
}
count++;

}
session.getTransaction().commit();
session.close();

Full stack trace of any exception that occurs:
NA
Name and version of the database you are using:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
The generated SQL (show_sql=true):
select myClass0_.ID as ID0_, myClass0_.C1 as C1_0_, myClass0_.C2 as C2_0_, myClass0_.C3 as C30_, myClass0_.C4 as C4_0_, .... from MYTABLE myClass0_

Debug level Hibernate log excerpt:

Apr 18, 2006 2:32:48 PM org.hibernate.cfg.Environment <clinit>
INFO: Hibernate 3.1 beta 2
Apr 18, 2006 2:32:48 PM org.hibernate.cfg.Environment <clinit>
INFO: hibernate.properties not found
Apr 18, 2006 2:32:48 PM org.hibernate.cfg.Environment <clinit>
INFO: using CGLIB reflection optimizer
Apr 18, 2006 2:32:48 PM org.hibernate.cfg.Environment <clinit>
INFO: using JDK 1.4 java.sql.Timestamp handling
Apr 18, 2006 2:32:49 PM org.hibernate.cfg.Configuration configure
INFO: configuring from XML document
Apr 18, 2006 2:32:49 PM org.hibernate.cfg.Configuration doConfigure
INFO: Configured SessionFactory: null
Apr 18, 2006 2:32:53 PM org.hibernate.cfg.HbmBinder bindRootPersistentClassCommonValues
INFO: Mapping class: myPackage.MyClass-> MYTABLE
Apr 18, 2006 2:32:53 PM org.hibernate.cfg.Configuration secondPassCompile
INFO: processing extends queue
Apr 18, 2006 2:32:53 PM org.hibernate.cfg.Configuration secondPassCompile
INFO: processing collection mappings
Apr 18, 2006 2:32:53 PM org.hibernate.cfg.Configuration secondPassCompile
INFO: processing association property references
Apr 18, 2006 2:32:53 PM org.hibernate.cfg.Configuration secondPassCompile
INFO: processing foreign key constraints
Apr 18, 2006 2:32:53 PM org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: Using Hibernate built-in connection pool (not for production use!)
Apr 18, 2006 2:32:53 PM org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: Hibernate connection pool size: 1
Apr 18, 2006 2:32:53 PM org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: autocommit mode: false
Apr 18, 2006 2:32:53 PM org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: using driver: oracle.jdbc.driver.OracleDriver at URL: jdbc:oracle:thin:@foo:1521:bar
Apr 18, 2006 2:32:53 PM org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: connection properties: {user=xxx, password=****}
Apr 18, 2006 2:32:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: RDBMS: Oracle, version: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Apr 18, 2006 2:32:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: JDBC driver: Oracle JDBC driver, version: 10.2.0.1.0
Apr 18, 2006 2:32:54 PM org.hibernate.dialect.Dialect <init>
INFO: Using dialect: org.hibernate.dialect.Oracle9Dialect
Apr 18, 2006 2:32:54 PM org.hibernate.transaction.TransactionFactoryFactory buildTransactionFactory
INFO: Using default transaction strategy (direct JDBC transactions)
Apr 18, 2006 2:32:54 PM org.hibernate.transaction.TransactionManagerLookupFactory getTransactionManagerLookup
INFO: No TransactionManagerLookup configured (in JTA environment, use of read-write or transactional second-level cache is not recommended)
Apr 18, 2006 2:32:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Automatic flush during beforeCompletion(): disabled
Apr 18, 2006 2:32:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Automatic session close at end of transaction: disabled
Apr 18, 2006 2:32:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: JDBC batch size: 1000
Apr 18, 2006 2:32:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: JDBC batch updates for versioned data: disabled
Apr 18, 2006 2:32:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Scrollable result sets: enabled
Apr 18, 2006 2:32:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: JDBC3 getGeneratedKeys(): enabled
Apr 18, 2006 2:32:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Connection release mode: null
Apr 18, 2006 2:32:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Default batch fetch size: 1
Apr 18, 2006 2:32:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Generate SQL with comments: disabled
Apr 18, 2006 2:32:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Order SQL updates by primary key: disabled
Apr 18, 2006 2:32:54 PM org.hibernate.cfg.SettingsFactory createQueryTranslatorFactory
INFO: Query translator: org.hibernate.hql.ast.ASTQueryTranslatorFactory
Apr 18, 2006 2:32:54 PM org.hibernate.hql.ast.ASTQueryTranslatorFactory <init>
INFO: Using ASTQueryTranslatorFactory
Apr 18, 2006 2:32:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Query language substitutions: {}
Apr 18, 2006 2:32:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Second-level cache: disabled
Apr 18, 2006 2:32:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Query cache: disabled
Apr 18, 2006 2:32:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Optimize cache for minimal puts: disabled
Apr 18, 2006 2:32:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Structured second-level cache entries: disabled
Apr 18, 2006 2:32:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Echoing all SQL to stdout
Apr 18, 2006 2:32:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Statistics: disabled
Apr 18, 2006 2:32:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Deleted entity synthetic identifier rollback: disabled
Apr 18, 2006 2:32:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Default entity-mode: POJO
Apr 18, 2006 2:32:55 PM org.hibernate.impl.SessionFactoryImpl <init>
INFO: building session factory
Apr 18, 2006 2:32:55 PM org.hibernate.impl.SessionFactoryObjectFactory addInstance
INFO: Not binding factory to JNDI, no JNDI name configured
Apr 18, 2006 2:32:55 PM org.hibernate.impl.SessionFactoryImpl checkNamedQueries
INFO: Checking 0 named queries

To speed up object creation (and by and large, object creation is the bit that will be slowing you down: Hibernate is pretty good at getting the most out of the SQL bits), here's a couple of tips:

- Use outer join selects when fetching associated objects: this slows down the selects, but reduces the numbers of them. Unless you've got table with massive numbers of columns, this usually results in a performance boost. Though if you're dealing with only one table, it won't affect you.

- Don't use components or anything else that requires more than one object to be created per result set row.

- If there's only one or two ways you get objects from the DB, consider using HQL's "select new" syntax, as this bypasses some reflection code. Reflection is faster than it used to be (pre java1.4), but it's still much slower than normal java method invocations (including "new").

Hi,



You are using the DriverManagerConnectionProvider.
Try using C3P0. I am not sure if DriverManagerConnectionProvider
is really using prepared statement caching.
Also you can check if you are using batch inserts, e.g.
in hibernate.cfg.xml

<property name="jdbc.batch_size">20</property>

But a performance loss of 2 x compare to plain jdbc is not
that bad.

I will try but I don't think the connection provider in my case is the problem since I am retrieving only one connection and then using it throughout the whole test but a good point in general I should consider. thanks

I didn't use the property jdbc.batch_size but I did use the hibernate.jdbc.batch_size property, assuming these are two different things I tried it out and it didn't really seem to make too much difference for reading or writing. :-(

In fact I just realized for my application that the setFetchSize and the addBatch/executeBatch sped my raw JDBC tests to lightening speed on Oracle (not sure about other databases). Now both read and write are WAYYYYY faster (~ 10X) than hibernate. One big cavaet is that I really need to run this test over a huge database with at least 100k rows...

thanks for you help and guidance

I will try but I don't think the connection provider in my case is the problem since I am retrieving only one connection and then using it throughout the whole test but a good point in general I should consider. thanks

I didn't use the property jdbc.batch_size but I did use the hibernate.jdbc.batch_size property, assuming these are two different things I tried it out and it didn't really seem to make too much difference for reading or writing. :-(

In fact I just realized for my application that the setFetchSize and the addBatch/executeBatch sped my raw JDBC tests to lightening speed on Oracle (not sure about other databases). Now both read and write are WAYYYYY faster (~ 10X) than hibernate. One big cavaet is that I really need to run this test over a huge database with at least 100k rows...

thanks for you help and guidance

- Unfortunately for my case as you point out the outer join selects probably won't hep me! :(

- yeah I only have simple simple bean like object with only double, long, short, byte, and Strings in it.

- Unfortunately I don't think I can use the select new syntax since my beans are limited to empty constructors (not actually limited in my small test but in the ultimate application those kind of constructors won't be available).

thanks

- Unfortunately for my case as you point out the outer join selects probably won't hep me! :(

- yeah I only have simple simple bean like object with only double, long, short, byte, and Strings in it.

- Unfortunately I don't think I can use the select new syntax since my beans are limited to empty constructors (not actually limited in my small test but in the ultimate application those kind of constructors won't be available).

thanks

Use jdbc.batch.size if you're defining it in hibernate.cfg.xml, or hibernate.jdbc.batch.size if it's in hibernate.properties. However I'm not sure that StatelessSesion will do JDBC batching for you--I suspect not.

Are you using a database cursor in your JDBC reading code?

In hibernate.cfg.xml, the property to set is hibernate.jdbc.batch_size. It used to be jdbc.batch_size. jdbc.batch.size was never a configuration property.

The Connection Pooling is used for prepared statement pooling too,
and this may be your problem I think.

No database cursor use. Actually someone asked me at work if I was using a database cursor as well. I was just using a simple preparedStatement and then executing it and iterating over the result set (for reading). Is there some speed advantage to using a database cursor? I never have before but right now the plain JDBC code is going so fast that I am wondering how to make hibernate faster now! :-)

Would hibernate actually make use of any connection or preparedStatement pooling if I am only doing a single type of query/update also I just noticed that I set my connection.pool_size = 1? In other words I am only reading/writing a group of objects from/to a single table. I will try using a different connection pooling and see what that does for me. thanks!

Connection pooling has to do with connections being multiplexed across several concurrent threads, and is really not relevant for this thread.

Accidently rated this as unhelpful (bad twitch in my mouse finger!), but it was helpful in eliminating possible things to check and test. Unfortunately I see no way of changing a rating for a post once given....*sigh*

i've added a point to him on your behalf.