Page by Page iterator does not solve DB bottlenecks?


EJB design: Page by Page iterator does not solve DB bottlenecks?

  1. Page by Page iterator does not solve DB bottlenecks? (4 messages)

    Hellou people,

    I've come across several implementation of Page-by-Page Iterators. OK, the one mentioned in J2EE Core patterns does solve some problems, but I think the best implementation not only iterate in memory but on the DB access level.

    Most real project implementation that I see retrieves all the rows required and then sort them out in memory. But, this doesn't make sense if you've got 1000s of rows and user decides to view only the first 2 rows.

    Tell me am I making sense.

    Please take a look at the latest EJB sample from Oracle. ( Specifically look at these java implementation: -


    Example performance issue user input: -
    If the Account User has 100 Portfolios, but only interested in viewing the first Portfolio on a page of size 5....don't you feel this sort of common Page-by-Page iteration do not solve the performance problem entirely?

    Thank you.
  2. Hi Simon,
    I think you are right. Either I haven't checked the oracle sample, I met the same issue in a project I've worked at.

    A simple first solution can be to limit the result of the query and this parameter to be configurable at runtime. If EJBQL is used , there is a method to set this limit ( setMaxElements). Obviously, is not a perfect solution.

    Another possible way is to write JDBC code when selecting, open a cursor, and fetching data from that cursor. This will bring the data from the DB server cache, not from app server memory. I've never done it from EJB, maybe someone who has done it will reply.

    Hope it helps,
  3. Hello all,

    I believe that there is a little confusion. Any smart EJB container will do the following:

    If a finder method is invoked that returns a Collection such as the following:


    The container will not retrieve all data for each and every student in the collection. Instead, it will return a Collection with only the primary key of each entity. In otherwise, it returns a collection of primary keys. Only when a specific StudentLocal is retrieved and its getters/setters are called does the container actually populate an entity bean instance with the data associated with that particular student. Therefore, if you are using the Page-by-Page design pattern, you can avoid loading all data for each and every student by using the java.util.List.subList() method to grab only a subset from the total Collection. That way you are in effect only pulling 10 or 20 (however many you specify) entities and loading their respective data.

    I'm using BEA WebLogic 7.0. =)

    Ryan LeCompte
    rml7669 at louisiana dot edu
  4. Hi Ryan

    I'm using the same version of WebLogic. I use EJBQL to select data(i use CMPs). Lets suppose the following code:

     QueryLocalHome qlh = (QueryLocalHome) studentsLocalHome;
     String query = "SELECT OBJECT(student) FROM Students AS student WHERE student.<condition> ...";
     Query query = qlh.createQuery();
     Collection col = query.find(query);

    The col will have 100 elements, 100 StudentLocal. In this moment, there is any entity populated with data? How can I check it?

  5. I don't think that's happening. I hope to see that's true for those EJB containers out there, though.

    I still think that the Oracle sample (or even most apps implemented out there) retrieve the whole collection with values from the DB. All the sorting are just done in memory using 'sublist' like you said. I'm putting my bet on both CMP and BMP entity bean impl :)

    Anyone care to try them out?