5

I currently have hibernate set up in my project. It works well for most things. However today I needed to have a query return a couple hundred thousand rows from a table. It was ~2/3s of the total rows in the table. The problem is the query is taking ~7 minutes. Using straight JDBC and executing what I assumed was an identical query, it takes < 20 seconds. Because of this I assume I am doing something completely wrong. I'll list some code below. 

DetachedCriteria criteria  =DetachedCriteria.forlass(MyObject.class);
criteria.add(Restrictions.eq("booleanFlag", false));
List<MyObject> list = getHibernateTemplate().findByCriteria(criteria);

Any ideas on why it would be slow and/or what I could do to change it?

Joseph
  • 1,442
  • 21
  • 44

3 Answers3

5

You have probably answered your own question already, use straight JDBC.

Hibernate is creating at best an instance of some Object for every row, or worse, multiple Object instances for each row. Hibernate has some really degenerate code generation and instantiation behavior that can be difficult to control, especially with large data sets, and even worse if you have any of the caching options enabled.

Hibernate is not suited for large results sets, and processing hundreds of thousands of rows as objects isn't very performance oriented either.

Raw JDBC is just that raw types for rows columns. Orders of magnitudes of less data.

  • I just wasn't sure if hibernate had the ability to do this and I was just using it incorrectly or not. Thanks for the confidence that straight JDBC will be the appropriate solution. – Joseph Feb 22 '11 at 15:45
5

I'm not sure hibernate is the right thing to use if you need to pull hundreds of thousands of records. The query execute time might be under 20 seconds but the fetch time will be huge and consume a lot of memory. After you get all those records, how do you output them? It's far more data than you could display to a user. Hibernate isn't really a good solution for doing data wharehouse style data crunching.

JOTN
  • 6,120
  • 2
  • 26
  • 31
1

Probably you have several references to other classes in your MyObject class and in your mapping you set eager loading or something like that. It's very hard to find the issue using the code you wrote because it's OK.
Probably it will be better for you to use Hibernate Profiler - http://hibernateprofiler.com/ . It will show you all the problems with your mappings, configurations and queries.

zihotki
  • 5,201
  • 22
  • 26