Meaningless Drivel is fun!*
The moose likes Object Relational Mapping and the fly likes ResultSet vs. ScrollableResults Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "ResultSet vs. ScrollableResults" Watch "ResultSet vs. ScrollableResults" New topic
Author

ResultSet vs. ScrollableResults

Franck McGeough
Greenhorn

Joined: Oct 27, 2005
Posts: 26
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

Joined: Oct 27, 2005
Posts: 26
I did find that calling :

inside the loop fetching the results cut the ScrollableResults time in half.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

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.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Franck McGeough
Greenhorn

Joined: Oct 27, 2005
Posts: 26
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

Joined: Apr 14, 2004
Posts: 10336

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

Joined: Oct 27, 2005
Posts: 26
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

Joined: Apr 14, 2004
Posts: 10336

I'm surprised Hibernate is making any synchronized calls. Are the synchronized calls being made by Hibernate or ehcache?
Franck McGeough
Greenhorn

Joined: Oct 27, 2005
Posts: 26
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
 
subject: ResultSet vs. ScrollableResults