We have a need to be able to process large results sets, of around say 10000 rows.

For smaller result sets we are going to rely on the Pagination support on the Query api (setFirstResults(), setMaxResults() ), but for larger result sets we want to avoid bringing these all into memory.

From what we understand
a) executing a list() against a Query is going to bring back all the rows
b) using iterate() on a Query is going to return an Iterator over a Collection containing just the PK IDs of the matching rows

We want to avoid (a) because we don't want to retrieve such a large result set in memory and second level cache, especially when a typical user is not going to page through all the results anyway.

An alternative to bringing all the results to cache would be to setMaxResults on the initial query to restrict to a more managable size and then issue further paged queries against this limited set to perform the pagination against the values in cache. The SQL that is generated for this uses the rownum() function - our DBAs are telling us that this is also undesirable because DB2 will still return the entire matching result set in order to retrieve the subset that is returned to Hibernate.

Approach (b) is also undesirable because we will have to do 2 queries for every item retrieved. Firstly to return the IDs, secondly to return the actual data.

Our preferred approach would be to use a cursor which would allow us to stream the results as needed from the database. This approach is supported in TopLink (Cursored Stream Support using ScrollableCursors), but it looks like this is not available in Hibernate?

If ScrollableCursors are not supported , is there an alternative approach which would address our concerns?

Thanks,
Kevin Hooke

I plan to add a version of Query.scroll() that retrieves whole objects, not just ids. The current implementation of scroll() is probably wrong.

Note, however, that if you customize the Dialect, you can make Hibernate use a scrollable resultset to implement setMaxResults() / setFirstResult(), instead of using the rownum stuff.

hi, gavin.

I believe, we need a full-powered wrapper for LIMIT/OFFSET (MySQL),
LIMIT (Postgres), rownum (Oracle), TOP (MSSQL).

What I would like it the built-in syntax in HSQL like OFFSET n LIMIT m,
with using old method (getting old records and filtering them) in case
the DBMS do not support the feature.

Since many, really many applications today have web-based interface,
it crucial to have a pager. And one of the things I've choosen Hibernate
is it's cross-DB nature -- I do not want to bother about SQL dialects any
more.

So, is there any change we will get that functionality soon?

Doesn't Query.scroll() retrieve whole objects when scrollable result sets are available? Or does it still select ids only first?

Scott

It selects just ids in the first query. As I said, I think this is a misfeature and should be changed.

Query.scroll() seems to return the same as Query.iterate() i.e. a collection of objects corresponding to the PKs for the query.

I am facing this same issue and Gavin's idea to make Query.scroll() return a collection of objects sounds great.

Please slap me down if I am meandering off-topic but can anyone tell me why Query.iterate() returns a collection of raw Object[] and Query.list() returns a collection of hydrated Objects? If Query.iterate() returned the same as Query.list() then the Query.scroll() enhancement would not be necessary.

Negative. We already have exactly this functionality using setMaxResults() / setFirstResult().

eh? what do you mean?

Sorry, that was a very poorly explained question.

In the following code fragment is a query (from viewAllAuctionsFast) which creates a new object. If you get the query's list and iterate through it you get the objects but if you iterate though the query's iterator you get an array of the values i.e. item.id,... max(bid.amount) etc.



If the query's iterator returned an object then my issue would be gone....

oh yeah, you didn't mention "select new()". I agree that we should support "select new" in iterate() and scroll(). its just laziness that we do not.

Not sure it's the same. It returns IDs. It would be okay when it will
retrieve objects instead, but I have a doubt:

Currently I have a complex query that looks like:

select from
Intersection ti, DomainValue dv1, DomainValue dv2
where
ti=dv1.intersection and
ti=dv2.intersection and
dv1.domain = ? and
dv2.domain = ? and
(dv1.normalizedValue <= ?) and
(dv2.normalizedValue <= ?)
order
by dv1.nvnvp+dv2.nvnvp

(In this query there only two domains; actually there could be
up to 15).

As you see, I get back Object[]. That's because I have to show user
not only the best intersection found, but also the values associated
with the intersection. There are few hundreds of rows usually.
Currently, I just foolishly add "LIMIT N,M" to the query text.
Will Query help me or won't it?

Adding LIMIT into HSQL would make writing queries much simpler,
without need to use Query/Filter, BTW.

use query.setFirstResult().setMaxResult()

It would be great if Query.scroll() could return objects but scroll() has at least one limitation: it does not allow polymorphic queries like find() and iterate() do.

Is there any reason why iterate() could not get objects instead of ids? If there's no big reason not to do this I'll start looking into it....

It does allow polymorphic queries, just no implicit polymorphism!


I don't know what you think iterate() does different to find() ... the only difference is fetching ids instead of full objects, and I most certainly will not let you change that! It is the whole reason iterate() exists!

Oh I get it, you wanted to use iterate() to do paging.

Just use setMaxResults(), as is intended. Stop trying to work against the framework, and do things the intended way.