This week's book giveaway is in the Clojure forum.
We're giving away four copies of Clojure in Action and have Amit Rathore and Francis Avila on-line!
See this thread for details.
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

ResultSet vs. ScrollableResults

 
Franck McGeough
Greenhorn
Posts: 26
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
We have the need to do queries that return large sets of rows in particular parts of our code. By large I mean 100,000 rows or so. The table in question is straightfoward, standalone - no collections or other strange stuff going on. In straight JDBC tests using ResultSet I have times around 1/2 sec. Using a ScrollableResultSet yields times above 4 seconds. I'm stepping through the Hibernate code out of curiousity to see what might be taking so long (I naturally expected it to be slower than a JDBC ResultSet but was surprised that it was 8 times slower). Does anyone have any opinion / insights into this? I'm not concerned, just curious. What features are slowing it down to this level?
 
Franck McGeough
Greenhorn
Posts: 26
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I did find that calling :

inside the loop fetching the results cut the ScrollableResults time in half.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'd imagine that it is memory consumption that is slowing this down. Using ScrollableResults is not much different from just navigating back and forward through a ResultSet as you would with straight JDBC, i.e.:

The difference for Hibernate is that the Session is involved. Remember that the Session is the first level cache. Any DB operations waiting to happen will be cached in the Session, as will every object you populate into it till you flush, close or clear the Session.

I might be wrong (its just a guess), but you could check by comparing two apps that get your results via normal JDBC and via Hibernate and ScrollableResults.
 
Franck McGeough
Greenhorn
Posts: 26
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Is there anyway to turn off the cache for particular queries? I called setCacheable(false) on the Query but that didn't change the performance at all.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The first level cache (if indeed that is your problem) is implicit and unavoidable. Things will be cached in the Session unless you are clearing/flushing the session after every loop iteration. The setCacheable() method applies only to the second level cache. Second level cache participation is controlled by the mapping file, and queries don't have a mapping file, hence the need for this method.
[ December 01, 2005: Message edited by: Paul Sturrock ]
 
Franck McGeough
Greenhorn
Posts: 26
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The first level cache is at least part of the reason the code is slower. I'm using the default ehcache. I provided a ehcache.xml file and set maxElementsInMemory="1" and it trimmed another 1/2 sec off the time. I wonder if a good part of the remainder might be the synchronized methods down in Hibernate? They are run through for each next() call on the ScrollableResults.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm surprised Hibernate is making any synchronized calls. Are the synchronized calls being made by Hibernate or ehcache?
 
Franck McGeough
Greenhorn
Posts: 26
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You're right. I was confused. There are synchronized calls down in Hibernate (just doing a search through the source code) but the stuff I was looking at was cache related.
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic