Query within iteration slows down

hannehomuth · **Joined:** Mon May 11, 2009 11:16 am **Posts:** 13

Hi Hibernate Users,

i've a question regarding a query execution which is located within a loop. Imagine following scenario.

Code:

List<SomeObject> allSo = new ArrayList<SomeObject>();
while(resultSet.next()){
   Long someColumnValue = result.getLong("COLUMN_NAME");
   SomeObject so = (SomeObject) em.createNamedQuery("getSomeObjectBySomeColumn").setParameter("someColumn",someColumnValue).getSingleResult();
   allSo.add(so);   
}

Within this loop the query execution time slows down. The first time it might be a millisecond but after 100 iteration steps it will be 100 ms per execution and rising. I've profiled the application and saw that the method autoFlushIfRequired has a very high cost.
I know that hibernate wants to flush the session before every query execution and it check whether a flush is needed or not (autoFlushIfRequired). But this is unuseable. Might this be a bug? Is it already posted? I couild not find it!
I've tried to flush and clear the entitymanger which will increase the performance, but I don't want to do this, because it will clear other objects too which I need later on again (It's a long transaction).

What can I do to execute a query within a loop which won't last longer every iteration step?

Thx for suggestions

nordborg · **Posted:** Wed Aug 04, 2010 2:58 am

Quote:

What can I do to execute a query within a loop which won't last longer every iteration step?

You should disable the automatic flushing. On a Hibernate Session this is done by calling Session.setFlushMode(). It can also be done on the query level by calling Query.setFlushMode(). If you are using an EntityManager, check the javadoc for similar methods.

hannehomuth · **Joined:** Mon May 11, 2009 11:16 am **Posts:** 13

nordborg wrote:

Quote:

What can I do to execute a query within a loop which won't last longer every iteration step?

You should disable the automatic flushing. On a Hibernate Session this is done by calling Session.setFlushMode(). It can also be done on the query level by calling Query.setFlushMode(). If you are using an EntityManager, check the javadoc for similar methods.

Yes you are right, I've read this already, but isn't it possible that I run into stale data if I do so?
Furthermore I'm only able to set the Flushmode to AUTO or COMMIT.

Maybe this is an JPA restriction? I use the Hibernate Implementation of JPA (Maybe I should have provide that information before, sorry)

nordborg · **Posted:** Wed Aug 04, 2010 3:49 am

Quote:

Yes you are right, I've read this already, but isn't it possible that I run into stale data if I do so?

Not in the example you posted. But in a more complex scenario were you are also modifying objects in the loop, yes. The problem is that the automatic dirty detection will take more and more time as more and more objects are loaded into the session. The numbers you are posting doesn't seem unreasonable.

To get improved performance you'll need to help Hibernate. The first step is to select a non-automatic flush mode. Then you can at least control in the code when a flush happens. The next step is to reduce the number of times a flush is needed and to reduce the number of entities that Hibernate needs to check during the flush. Exactly how to do this depends on the actual scenario. Evicting items from the session cache may help. There is also a fairly new method Session.setReadOnly() which allows you to tell Hibernate to not dirty-check an entity but still keep it cached in the session.

hannehomuth · **Joined:** Mon May 11, 2009 11:16 am **Posts:** 13

nordborg wrote:

The next step is to reduce the number of times a flush is needed and to reduce the number of entities that Hibernate needs to check during the flush. Exactly how to do this depends on the actual scenario. Evicting items from the session cache may help.

The number of iteration steps in my example was far away from the productive amount. There are 20000+ steps needed. I will try to reduce the amount of flushes but I hate this because this will make the code more complicated as it has to be. But I guess it will be the only option.

nordborg wrote:

There is also a fairly new method Session.setReadOnly() which allows you to tell Hibernate to not dirty-check an entity but still keep it cached in the session.

As I said, we use the JPA Standard, with a concrete version of hibernate (3.2.6). We are not able to upgrade to a newer version, because our client sets this version in his "Book of Standards :-(

Thx

sanne.grinovero · **Posted:** Wed Aug 04, 2010 5:05 am

Quote:

As I said, we use the JPA Standard

sadly the JPA standard is too limited to be able to hand enough hints to tune this.
So you're not authorized to get a reference to the hibernate session from the entitymanager? technically, that's not an issue: entitymanager.getDelegate() and cast it to Session.

other options are:
1) have them migrate to JPA2
2) your code suggests me - but have no enough elements to state it for sure - that it might be possible to convert the first query and the inner loop in a single query - more complex but extremely more efficient.

hannehomuth · **Joined:** Mon May 11, 2009 11:16 am **Posts:** 13

s.grinovero wrote:

1) have them migrate to JPA2
2) your code suggests me - but have no enough elements to state it for sure - that it might be possible to convert the first query and the inner loop in a single query - more complex but extremely more efficient.

JPA2 is not an option because not supported by our customer (He has a "Book of Standards")

Your second option is the way I will do.

But one question I have to ask. As you said, hibernate slows down within this loop because it flushes all objects in the session to the database. But I always collect only one object within one iteration step. I would say hibernate has to flush only one object per iteration step? Why it flushes all objects (even the ones he had already flushed to db)? Is there a reason for that? I want to understand, so I hope I won't run into same problems later on

nordborg · **Posted:** Wed Aug 04, 2010 8:50 am

Quote:

I would say hibernate has to flush only one object per iteration step? Why it flushes all objects (even the ones he had already flushed to db)? Is there a reason for that?

Hibernate will only flush objects that have been changed. If you are not changing anything no flush to the database will happen. But... Hibernate doesn't know what you are doing in your code and can't know if objects have been changed or not. So before deciding what to flush (if anything) Hibernate has to check if you have modified any objects. This check takes a certain amount of time and the more objects you have in the session the longer the check will take. If your code is indeed as simple as the example in the first post, and you are not modifying the objects loaded in the loop, then you can safely set the flush mode to COMMIT during the execution of that loop.

hannehomuth · **Joined:** Mon May 11, 2009 11:16 am **Posts:** 13

nordborg wrote:

Quote:

I would say hibernate has to flush only one object per iteration step? Why it flushes all objects (even the ones he had already flushed to db)? Is there a reason for that?

Hibernate will only flush objects that have been changed. If you are not changing anything no flush to the database will happen. But... Hibernate doesn't know what you are doing in your code and can't know if objects have been changed or not. So before deciding what to flush (if anything) Hibernate has to check if you have modified any objects. This check takes a certain amount of time and the more objects you have in the session the longer the check will take. If your code is indeed as simple as the example in the first post, and you are not modifying the objects loaded in the loop, then you can safely set the flush mode to COMMIT during the execution of that loop.

My real code was a little bit more complex

Code:

List<AssociationObject> allSo = new ArrayList<AssociationObject>();
while(resultSet.next()){
   Long someColumnValue = result.getLong("COLUMN_NAME");
   Long someAnotherColumnValue = result.getLong("ANOTHER_COLUMN_NAME");
   SomeObject so = (SomeObject) em.createNamedQuery("getSomeObjectBySomeColumn").setParameter("someColumn",someColumnValue).getSingleResult();
   SomeObject so2 = (SomeObject) em.createNamedQuery("getSomeObjectBySomeColumn").setParameter("someColumn",someAnotherColumnValue).getSingleResult();
   
   AssociationObject association = new AssociationObject();
   association.setObject1(so);
   association.setObject2(so2);
   
   //Save in list to persist them later outside this loop
   allAssociations.add(so);   
}

But I didn't change the object, only created a new one which associates the two objects so I know that there belong together. So I don't change them. I've implemented the workaround mentioned as option #2 by s.grinovero, which works in acceptable performance now. But for the next time I will know.

Thx to all, really!