Hi everybody.

I just recently started using Hibernate and ran into some problems with the performance of prepared statements. I have a set of select and update HSQL queries, which I run about 1000 times. The base tables do not grow during the test, but the time that it takes to execute a set of queries increases from 200ms at the beginning of the test to 1600ms at the end of the test.

We found out that the performance degradation is caused by the reducing speed of each hql query execution. One of the reasons, we believe, is that prepared statements are not refreshed, therefore causing the database to use bad query plan at the end of the test.

I tried disabling statement_cache and use dbcp and c3p0 with 0 statement cache sizes, but it didn't work. I was still able to see that on the SQL Server side stored procedures created at the beginning of the test are used to execute same queries at the end of the test. Somehow I need to force query plan recompilation.

Has anyone experienced similar problems and what would be a good solution for this?

Thanks.

Alec

The only instance of growing execution times I have seen is when re-using a Session between commits without clearing the level one cache.

I would doubt PreparedStatements are the root of the error.

You didn't mention the database type. On some JDBC drivers you can add a switch to manually disable PreparedStatements if you want to remove it from Hibernates control.

I think you are right. I just profiled the SQL Server and it shows that queries take about the same time on the database side. So, the overhead is coming from the Hibernate side.

How do I force the first-level cache to clear? Do I need to clear any other caches?

Thanks.

Alec

after transaction.commit() or transaction.rollback(), do session.clear() to make sure the next user of the Session has a clean start.

So why not just drop the hibernate session and create a new one for the next user - as per Hibernate recommendations ?

Clearing the session cache fixed the performance problem.

Thanks.

Alec

Greetings.

Earlier in this discussion thread it was recommended that I clear the first level cache in order to solve the problem with performance. Doing so indeed solved that problem. However, now I am getting a new exception:

net.sf.hibernate.NonUniqueObjectException: a different object with the same identifier value was already associated with the session: 12249, of class: org.waterford.db.data.StudInfo

which seems to be the result of clearing the cache.

My project first retrieves a student record (id=12249). Then it runs some tests periodically clearing the first-level cache.

Note that I have many-to-one constraints declared and during the tests some database records are retrieved causing the student record to be re-cached again as well (since it was cleared off the cache earlier).

So, if at the end of the test I need to update the old reference to this StudInfo object I get the error above. I guess this is because it is still associated with the old data object, which was cleared off the cache.

What is a good way to fix this problem? Is there a way to reassociate the old reference with the newly cached data object?

Thanks.

Alec

Try http://www.hibernate.org/hib_docs/reference/en/html/transactions.html#transactions-optimistic-longsession


Sherman

The documentation says: "This pattern is problematic if our Session is too big to be stored during user think time". I have one single session, which lasts for 5-10 minutes with intensive database updates. However, there are a few arbitrary records that get retrieved in the beginning and may be updated at the end of the session. So, if I only use the "10.4.1. Long session with automatic versioning" approach I get terribke performance in a matter of minutes and if I clear the first-level cache every now and then, then I get that "net.sf.hibernate.NonUniqueObjectException: a different object with the same identifier value was already associated with the session".

I am trying to separate save/update logic in one class and I don't want to spread stuff like opening/closing sessions and committing/rolling back transactions around the whole application and I don't see an easy and consistent way to do this for my application.

Any suggestions would be greatly appreciated.

Thanks.

Alec