[b]Summary: Query.scroll() returning ScrollableResults is very slow. [/b]
[b]Hibernate version: 2.1.6[/b]

[b]Mapping documents:[/b]

[code]<query name="PosLocation.Cleansed.ItemsInMergeGroupOrder"><![CDATA[
select distinct cln
, mg1.groupId
, mg0.groupId
from
com.purisma.match.group.staged.mergegroup.Entity1MergeGroup as mg1,
com.purisma.match.group.staged.mergegroup.Entity0MergeGroup as mg0,
com.purisma.generated.pos.db.PosLocationCln as cln
where
mg1.matchClnId = cln.id
and mg0.groupId = mg1.ownerGroupId
and mg1.batchId = ?
and mg0.matchGroupType = ?
and mg1.lifecycleState = com.purisma.db.core.state.LifecycleState.ACTIVE
order by
mg0.groupId,
mg1.groupId
]]></query>[/code]

[b]Name and version of the database you are using: Oracle 9.2.0.4[/b]

Hello! I am running the abovementioned query where the tables represented by cln, mg0 and mg1 have approximately 50K records each. I create a Query object, bind params and call query.scroll() like this

[code]ScrollableResults results = query.scroll();[/code]

I run this against several sets of data (all ~ 50K in each table each time). In most runs the time it takes is a few seconds. I measure the time like this:

[code]long s0 = System.currentTimeMillis();[/code]

both before and after the query.scroll() call and take the difference.
In some runs, the query seems to take several minutes (3 min - 12 min).
[b]Are there any performance known limitations with ScrollableResults ?[/b]
Also, I have tried running the SQL stmt directly on the Oracle database (in sqlplus or Toad) and its pretty fast taking atmost 4 sec. The explain plan that is used varies (we use CBO) depending on the stats and size of the data in the tables.

I would appreciate any pointers on how to proceed with figuring this out.

-sudhir

Hello Sudhir,

You need to call query.setFetchSize() to something smaller like 100 or 1000. I have scrollable results working farily quickly (under a second but with a fair amount of restrictions) for several 50k+ row tables.

I think that if you don't set the fetch size Hibernate will try and load all 80k rows into the session.

-- Kastor

Thanks! I'll post after I try it out.

Am still running my tests with the new settings.

I'm trying out 2 things:
1) Measure the time it takes to run the query with pure JDBC.
2) Measure the time it takes for query.scroll()
3) Measure the time it takes for query.scroll() with the query.setFetchSize(1000) call

We are using Hibernate 2.1.6, not the latest.

Here are the results I'm observing from my experiments.
There are three queries, I'll call them ntv, cln and sts. Here are the times I measured:
Query, Query.scroll(), JDBC
ntv, 9.63 min, 47 ms
cln, 9.52 min, 16 ms
sts, 9.74 min, 16 ms

I ran the JDBC calls first and then the 3 calls to Query.scroll(). In the case of the sts query I made a call to query.setFetchSize(1000) before the query.scroll() call. It doesnt seem to make any difference to the timing. Doesnt it just set the fetch size of the underlying resultset coming back from Oracle?

I did a bit of digging into the Hibernate 2.1.6 codebase and was able to trace the query.scroll call to the following code in SessionImpl.java:


Does the commented code have any significance to my problem? It seems like the "shallow" param has been defaulted to "false" which will cause the query.scroll() call to retrieve all the rows for the query and instantiate Hibernate beans for them. Still, I dont see how that can take 9+ minutes