Retrieving a large amount of data and returning them to the client can be quite paniful….however with EJB 3 it’s quite easy to paginate a large resultset: for example supposing you want to retrieve only the first “page” of 100 Users:
List <Users> tasklist = em.createNamedQuery("findUsersByRole") .setParameter("role", role) .setMaxResults(100) .setFirstResult(0) .getResultList();
You could further optimize the query with “setFetchSize” which sets a fetch size for the underlying JDBC query.
I have tested with a (rather complex) resultset of 100.000 records on JBoss AS 5.0 + Oracle 10.1. Retrieving the full Resultset took about 12 seconds (not too bad, with plain SQL it took about 7.5 seconds). Retrieving the single page of 100 records took just 0.5 seconds.