I run into a strange Hibernate performance issue these days. Hopefully someone can give me some advice.

I defined two class mappings and use the following code to load them from database:

hsql = "select r,c from "
+ RepActivityRuntime.class.getName()
+ " r,"
+ RepActExecTimeConfig.class.getName()
+ " c "
+ "where r.flowId in (:flowids) and c.id=r.id and r.state not in (:states)";

Query q = session.createQuery(hql.toString());
q.setParameterList("flowids", flowIds);
q.setParameterList("statess", excludeStates);
long starttime = System.currentTimeMillis();
List queryResult = q.list();
System.out.println("Query time:"+(System.currentTimeMillis()-starttime));

The q.list() method returns about 900 records, but it took 11.246 seconds!
I tried to run same SQL against the SQL Serevr database, the result returns in less than a second.


The first class has about 20 fields and the second has about 10. The size of all 900 returned records takes about 1M bytes(I know it becase I copyed query result from Query Analyser and saved to a text file).

We are using Hibenrate 2.1.7.

I heard Hibernate will bring maximum 10% lose of performance. But to me, it is more than 10 times.

Anyone can give me any suggestion about where to look at?

Thanks!

-Li