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?
Joined: Oct 27, 2005
I did find that calling :
inside the loop fetching the results cut the ScrollableResults time in half.
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.
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 ]
Joined: Oct 27, 2005
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.