Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Extremely Slow Hibernate Query

 
Simon Ritchie
Ranch Hand
Posts: 95
4
Eclipse IDE Hibernate Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi folks,

I've set up a Hibernate mapping (using a hbm.xml file) to a Java Bean which represents objects stored in a table in an Oracle Database. The table only has a couple of hundred rows of data. When I query it via SQL Developer it returns results immediately. But when I query it via the Hibernate mapping (ie: when I use a Java class to get a List of Beans from this table) the response is very slow - three minutes in fact. This isn't the case with other mappings I've written that map to different tables in the database. I've traced the source of the slowness to one lined of Java code




As you can see it's a basic query with no clauses and my mapping isn't doing anything complex or anything that I'm not already doing in different mappings that don't experience this issue. Does anyone know how I can delve deeper to see why this is so slow? Is this likely to be a database issue or is there something I should be doing with Hibernate to improve performance?

Thanks
 
Dave Tolls
Ranch Hand
Posts: 2103
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What does the class look like?
Any relationships in it that aren't lazy loading?

How are you timing it?

I wouldn't rely on the SQLDeveloper timing, as it returns rows as it pages I believe, so won't be handling all 200 rows in one go, and it also won't be handling any relationships.
 
Simon Ritchie
Ranch Hand
Posts: 95
4
Eclipse IDE Hibernate Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The class is very basic (I'm a little reluctant to post the code here as it's proprietary data for my employer). It's just a constructor with a series of getters and setters for three long variables and seven String variables. It implements the Serializable interface but that shouldn't have an impact.

I stripped all relationships out of the hbm.xml file except for the id attribute



It had no effect - the data still took over two minutes to load.

I'm timing it using debug messages. I put System.out.println(<current time>) commands after each line of the code and saw that there was a difference of two minutes before and after the line of code I highlighted in the OP (I stripped these commands out before posting the OP).

So, it could be that this is a database issue?
 
Dave Tolls
Ranch Hand
Posts: 2103
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How are you timing it?
 
Simon Ritchie
Ranch Hand
Posts: 95
4
Eclipse IDE Hibernate Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


Between time checks two and three there is a gap of a couple of minutes which leads me to believe that this line is the source of the problem

this.session.createQuery("FROM MyTable").list();
 
Dave Tolls
Ranch Hand
Posts: 2103
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can open Hibernate up to DEBUG or TRACE (if you need to) so you can see exactly what query is being created, but I'd be surprised if it's going to be the query for a simple entity.
But that might give you some timestamps to see where it's taking its time.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:You can open Hibernate up to DEBUG or TRACE (if you need to) so you can see exactly what query is being created

If you are using log4j, you can enable detailed Hibernate logging using this configuration

Simon Ritchie wrote:Between time checks two and three there is a gap of a couple of minutes which leads me to believe that this line is the source of the problem

this.session.createQuery("FROM MyTable").list();

It might be a stupid question, but how many records has this table? Because with that query it will return all records from that table. And you might be running low on memory in the end, so garbage collection could be ran several times to claim as much memory as possible (which would also slow down the execution).

[edit] You already stated in your first post that the table only has a couple of hundred rows and that should not be any problem at all. So I would enable Hibernate logging and see which SQL statements are triggered during the execution of that statement.

Hope it helps!
Kind regards,
Roel
 
Simon Ritchie
Ranch Hand
Posts: 95
4
Eclipse IDE Hibernate Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks folks, I'll try the logging suggestions and see how far I can get.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic