getResultList in while loop get slower every next iteration

pionier · **Joined:** Fri Nov 20, 2009 9:27 am **Posts:** 7

Hi i have big problem getResultList in while loop get slower every next iteration, code:

Code:

Query q = sc.getEntityManager().createQuery("select i from InvoiceEJB i join fetch i.customer");
      final Integer MAX_RESULT = 500;
      q.setMaxResults(MAX_RESULT);
      q.setFirstResult(0);
      List<InvoiceEJB> invList = new ArrayList<InvoiceEJB>();
      boolean hasElement = true;
      Integer index = 0;
      while (hasElement){
         log.info(index);
         List<InvoiceEJB> tmpList = q.getResultList();
         invList.addAll(tmpList);
         if (tmpList.size()<MAX_RESULT ){
            hasElement=false;
            break;
         }
         q.setFirstResult(index+=MAX_RESULT);
      }

Whay is hibernate slowing down?

Im using hibernate 3 and mysql 5 and jboss4.0.3 in mysql cache is disabled.
my persistence.xml is realy simple

Code:

<?xml version="1.0" encoding="UTF-8"?>
<persistence>
   <persistence-unit name="AppManager">
      <jta-data-source>java:/AppDS</jta-data-source>
      <properties>
         <property name="hibernate.dialect"
            value="org.hibernate.dialect.MySQLInnoDBDialect"/>
         <property name="hibernate.hbm2ddl.auto" value="update"/>
      </properties>
   </persistence-unit>
</persistence>

AppDS source:

Code:

<?xml version="1.0" encoding="UTF-8"?>
 
<datasources> 
    <local-tx-datasource> 
        <jndi-name>AppDS</jndi-name> 
   <connection-url>jdbc:mysql://localhost:3306/standard?characterEncoding=utf8&amp;autoReconnect=true</connection-url>
        <driver-class>com.mysql.jdbc.Driver</driver-class> 
        <user-name>root</user-name> 
        <password>password</password> 
   <max-pool-size>50</max-pool-size>
        <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
    </local-tx-datasource> 
</datasources> 

any idea?;/

sanne.grinovero · **Posted:** Wed Mar 09, 2011 1:58 pm

you're loading all the data from your table in memory, adding 500 at each loop: you're either exhausting your memory, or also because you're loading all your customers in memory it has to check for every 500xAverageJoinSizeXloopIterator if the same customer wasn't loaded already, quite a N^3 complexity problem.

nordborg · **Posted:** Wed Mar 09, 2011 5:27 pm

And there is also the issue that (if not configured differently) that Hibernate will perform automatic dirty check of all loaded entities before executing the query. For each iteration they become more and more. Eg. changing the flush mode from FlushMode.AUTO to FlushMode.COMMIT may help a lot. At least this is true when using the Hibernate API directly, I am not sure exactly how this translates to JPA, but I think there is something equivalent. Check javadoc for javax.persistence.FlushModeType.

You may also try evicting/clearing entity manager at the end of each loop.

But I don't really understand why you are looping in the first place. It seems like everything ends up in 'invList' at the end and it would be a lot easier and quicker to just load everything with one query:

Code:

Query q = sc.getEntityManager().createQuery("select i from InvoiceEJB i join fetch i.customer");
 List<InvoiceEJB> invList = (List<InvoiceEJB>)q.getResultList();

I noticed that you use MySQL and if you are worried about memory usage of the JDBC driver then you could try adding useCursorFetch=true, useServerPrepStmts=true and defaultFetchSize=100 (you may use a larger value) options to the connection url.