0

I have a performance issue with a method that calls org.hibernate.Query#list. The duration of the method call vary over time: it usually lasts about one second but some days, for maybe half a day, it takes about 20 seconds.

How can this issue be resolved? How can the cause for this issue be determined?

More elements in the analysis of this issue:

  • Performance issues have been observed in production environment, but the described issue is in a test environment.
  • The issue has been observed for at least several weeks but the date of its origin is unknown.
  • The underlying query is a view (select) in MS SQL Server (2008 R2):
    • Database reads/writes in this test environment are from a few users at a time only: the database server should not be overly sollicited and the data only changes slowly over time.
    • Executing the exact query directly from a MS SQL Server client always takes less than a second.
    • Duplicating the database (using the MS SQL Server client to backup the database and restore this backup as a new database) does not allow to reproduce the problem: the method call results in being fast on the duplicate.
  • The application uses Hibernate (4.2.X) and Java 6.
    • Upgrading from Hibernate 3.5 to 4.2 has not changed anything about the problem.
    • The method call is always with the same arguments: there is a test method that does the operation.
    • Profiling the method call (using hprof) shows that when it is long, most of the time is spent on "Object.wait" and "ref.ReferenceQueue.remove".
    • Using log4jdbc to log the underlying query duration during the method call shows the following results :
      • query < 1s => method ~ 1s
      • query ~ 3s => method ~ 20s
  • The query generates POJO as described in the most up-voted answer from this issue.
  • I have not tried using a Constructor with all attributes as described in the most up-voted answer from this other similar issue because I do not understand what effect that would have.
Community
  • 1
  • 1
cooltea
  • 1,113
  • 7
  • 16

2 Answers2

0

A possible cause of apparently random slowness with an Hibernate query is the flushing of the session. If some statements (inserts, updates, deletes) in the same transaction are unflushed, the list method of Query might do an autoflush (depending on the current flush mode). If that's the case, the performance issue might not even be caused by the query on which list() is called.

Marc-André
  • 846
  • 6
  • 8
  • Thank you for your answer. Could you describe a way to test the cause you are suggesting? Probably something like flushing all operations before testing the one that might be problematic? – cooltea Feb 03 '14 at 09:07
0

It seems the issue is with MS SQL Server and the updating of procedure's plan: following DBCC FREEPROCCACHE, DBCC DROPCLEANBUFFERS the query and method times are consistent.

A solution to the issue may be to upgrade MS SQL Server: upgrading to MS SQL Server 2008 R2 SP2 resulted in the issue not appearing anymore.

It seems the difference between the duration of the query and that of the method is an exponential factor related to the objects being returned: most of the time is spent on a socket read of the result set.

cooltea
  • 1,113
  • 7
  • 16