Wednesday, June 29, 2011

Query with large result set

when caching is not required, it may be better to use StatelessSession.

ScrollableResults results = session.createQuery("SELECT person FROM Person person") .setReadOnly(true).setFetchSize( 1000 ).setCacheable(false).scroll(ScrollMode.FORWARD_ONLY)

- The fetch size is the number of rows physically retrieved from the database at one time by the JDBC driver as you scroll through a query ResultSet with next(). As a general rule, setting the query fetch size is only effective for large result sets.I typically set this value to 100 for large result sets. (by default 10)