Hibernate version: 2.1.7

Name and version of the database you are using: Oracle 9.2.0.6, JDBC Driver 10.1.0.2.0

We have a table ORDERS that contains > 300K records. We do a query on this table of the form:
FROM orders WHERE customer = ? ORDER BY dateCreated DESC

The result of the this query is approx. 2000 records. We then use the standard hibernate paging pattern (setMaxResults/setFirstResult) to page through the query result. This works very well.

However, we now need to include extra information in the result that comes from a view. This view internally calls stored procedures. So the query form now looks like:
FROM orders, orderStatus WHERE orderStatus.orderNumber = orders.orderNumber AND orders.customer = ? ORDER BY dateCreated DESC

The problem is that the time it takes to execute the query is now much, much worse (10 seconds compared to 200ms).

Is there anyway to get hibernate to only perform the join after having first done the paging?

I know from my data that the join cannot affect the results of the query, only add information about the results.

Any help would be greatly appreciated,
David Sykes

H might not perform join in the case but try to execute one extra SQL for every row to get the extra enformation you need.

Please set show_sql=true in the configuration file and send a log.

try with ScrollableResultset