Hibernate version:2.1.7
Name and version of the database you are using: DB2 8 on AIX
The generated SQL (show_sql=true): true
Debug level Hibernate log excerpt:
I am having a problem with hibernate running a query against a table with 2.9 million records. The max records has been set at 200 records. When I try to run hibernate it takes several minutes to run the query, but when I try the same query through JDBC it takes only 0.18 secs.
Here is the configuration from hibernate.cfg.xml:
Code:
<property name="connection.datasource">jdbc/MDIDB</property>
<property name="jndi.class">com.ibm.websphere.naming.WsnInitialContextFactory</property>
<property name="dialect">net.sf.hibernate.dialect.DB2Dialect</property>
<!-- Change to 'true' to see Hibernate-generated SQL -->
<property name="show_sql">true</property>
<property name="use_outer_join">true</property>
<property name="max_fetch_depth">10</property>
<property name="transaction.factory_class">net.sf.hibernate.transaction.JTATransactionFactory</property>
<property name="hibernate.transaction.manager_lookup_class">net.sf.hibernate.transaction.WebSphereTransactionManagerLookup</property>
<property name="connection.provider_class">com.freightliner.util.FTLDataSourceConnectionProvider</property>
<property name="statement_cache.size">20</property>
<property name="jdbc.batch_size">20</property>
<property name="jdbc.fetch_size">25</property>
<property name="jdbc.use_scrollable_resultsets">true</property>
<property name="cglib.use_reflection_optimizer">true</property>
<property name="hibernate.cache.us_minimal_puts">true</property>
Here is the code from the DAO that executes the query:
Code:
Query query = SessionUtil.getSession().createQuery(m_stringBuffer.toString());
query.setFetchSize(MdiConfig.getMaxDbResults());
query.setMaxResults(MdiConfig.getMaxDbResults());
list = query.list();
This is basically the query that hibernate is generating. I removed the column names. There are over 100 hundred of them:
Code:
select * from ( select rownumber() over() as rownumber_, ...... from TABLE TABLE0_) as temp_ where rownumber_ <= ?
[/code]