ScrollableResults possible in JBoss ?

[marcelvanvelzen]

Hi there,

I am retrieving information from a Postgres database via the EntityManager, createQuery and its function getResultList(). It is easy to use especially with the EJB entity annotations. A disadvantage is that it stores all the results in memory. I know about the pagination possibilities with setFirstResult() and setMaxResults().

I want to access data like a cursor and only having one record at a time in memory (not totaly true, since the dbms will store some additional records in memory, but ok). It is meant for a server side process triggered by a timer, so pagination using the getResultList() makes no sence.

Has anybody some experience with ScrollableResults and the scroll() and evict() functions in JBoss ? And if possible share some code how to do this in JBoss.

I have spent several days now on this topic, but I have no idea how to proceed.

Thanks in advance,

Marcel

[marcelvanvelzen]

I did some more research, but I think this feature is lacking in the current implementation JBoss 4.05GA with EJB3.0.

I hope in the future this feature will be implemented.

It surprised me that it is lacking, since cursor behaviour is a very old and proven concept in a DBMS environment.

Only pagination is possible, but is a very inefficient way of retrieving bulk information and therefore results in the need for a higher pool size in the connection pool. In addition when performing stress and bulk testing the amount of memory necessary for running such processes is not really small (many MB's).

This worries me for the production environment.. Well, luckily JBoss will work in a cluster environment.

I also hope in the future releases of the Java JDK will be much more memory efficient. It's open source now, so we see how this develops.

Some Hibernate queries are slow. During performance testing I decided to put some logic on the DBMS. Instead of 50 seconds running time, this was downsized to 2 seconds.