Hi All,

I have a ScrollableResult iterator that I'm using to recurse through a result set of about 10,000 - 100,000 rows and was looking at the performance.

I obviously clear the Session cache every nth object, yet performance of the iteration doesn't seem to be as fast as I would like it to be. I see it takes about 30 - 40 seconds to iterate through that kind of result set (each row contains a bunch of numbers and some text...totaling about 100 bytes each).

I'm running over Cat-6 to the database box...and so I'm wondering what the performance bottleneck could be.

I'm thinking that it's the database, but I wanted to rule out any possible optimizations I could do on the Hibernate side.

Thanks!

Ronak Patel

For a point of reference, I set up a test in my environment. I'm able to scroll through ~280k records in under 20s and i suspect my data might be a bit larger, there are 3 columns defined as varchar2(2000).

What else are you doing in your iteration? Are you loading any additional data? Have you done any timings to see if your performance degrades over time? i.e. it's nice and fast for the first 1000 or so but then by the 10000th record it is considerably slower, etc.

While not a performance improvement per se but if you don't need to scroll backwards them if you set FORWARD_ONLY on your scrollable results then when you clear your session you will also be able to dump the underlying result set. I ran into a problem a little while back with memory consumption since the scrollable implementation from either the java or hibernate side was holding onto the underlying result set once it walked over it.

Hi!

Thanks for the reply. I ended up switching to raw JDBC and believe it or not, that had very minimal effects on performance.

It wasn't until I switched my calls from SELECT * to SELECT <the 3 or 4 columns out of 7 that I actually care about> that I really saw the performance improvement.
Improvement was about 4x - 8x depending on how much data was coming back.

I've even tried this where I do absolutely nothing in the iteration through the resultset...and performance is about the same (give or take a few hundred milliseconds).

I'm thinking of staying with JDBC at the moment because I have to move on to other tasks...but I find it weird that selecting a few more bytes of data (the other columns are just timestamps and doubles) would increase resultset iteration time so much (even in raw jdbc).

Thanks

Ronak

I have a similar problem see here

viewtopic.php?f=1&t=1006449