Bad load time (20 objects/sec)

[sseaman]

I have a Bean (local/remote) named School that has a relationship to few other beans.

If I use a finder to get an iteration of the Beans it takes less than one second but when I go over the beans and use two or three get()'s from the bean I drop down to processing 20 beans a second.

This is very slow and I'm wondering if there are ways to improve the performance.

Here is the code that is doing the lookup (it is in a jsp file for right now):
SchoolHome slh = (SchoolHome)ctx.lookup(EJBConstants.JNDI.SCHOOL);
Iterator it = slh.findByState(user.getLeagueContact().getState().getStateId()).iterator();
Vector schoolsInLeague = new Vector();

int maxLen = 0;
StringBuffer options = new StringBuffer();
// This loop only does 20 beans a second...
while (it.hasNext()) {
School sc = (School)it.next();
if ( (sc.getLeague() == null) || (sc.getLeague().getLeagueId() != leagueId) ) {
String scName = sc.getName();
int scNameLen = scName.length();
if (scNameLen > maxLen) maxLen = scNameLen;
options.append("<OPTION value=\"").append(sc.getSchoolId()).append("\">")
.append(scName).append("\n");
}
else {
schoolsInLeague.addElement(sc);
}
}

Now, what I have found very odd is the SQL that is being created by JBoss. Here is a snipit:
2002-10-07 16:44:49,841 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.School] Executing SQL: SELECT schoolId,name, address1, address2, city, postalCode, url, state, league FROM School WHERE (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?) OR (schoolId=?)

And it does this for EACH bean but everytime the number of (schoolId=?) decreases by one.

I'm curious why it is doing it and even it if has to, why doesn't it just use an IN()?

Any help on speeding this up would be great. Right now it takes over 6 seconds to render the page...

Thanks!

[aloubyansky]

This huge sql statement is executed for each loaded bean because each loading is executed in a separated transaction. If you wrap fetching and iterating through beans in one transaction, the sql statement will be executed just once for all beans.
Also consider configuring read-ahead strategy to optimize beans loading.

[sseaman]

I am not familiar with how to do either of what you said. Could you give me a few tips or point me in the right direction?

Thanks!

[sseaman]

I checked my jbosscmp-jdbc and I have had read-ahead on the whole time.

Here is what I have:
<read-ahead>
on-load
<page-size>250</page-size>
<eager-load-group>*</eager-load-group>
</read-ahead>

[sseaman]

I have been working on wrapping the EJB Entity Bean (named School) with a Session Bean named SchooList. I have transactions required for both but ti doesn't seem to be working. I am still getting all of those SQL statements.

Here is the code for the wrapper session bean:

public class SchoolListBean
implements SessionBean,
java.util.Iterator
{
private SessionContext sessionContext;
private Context ctx = null;
private SchoolHome schoolHome = null;
private School school = null;
private Iterator it = null;

public void ejbCreate() throws CreateException {
try {
ctx = new javax.naming.InitialContext();
schoolHome = (SchoolHome)ctx.lookup(EJBConstants.JNDI.SCHOOL);
}
catch (Exception e) {
throw new CreateException(e.getMessage());
}
}
public void ejbRemove() {}
public void ejbActivate() {}
public void ejbPassivate() {}
public void setSessionContext(SessionContext sessionContext) {
this.sessionContext = sessionContext;
}
public void findByState(Integer _stateId)
throws FinderException
{
it = schoolHome.findByState(_stateId).iterator();
}

public boolean hasNext() {
return it.hasNext();
}

public Object next() {
school = (School)it.next();
return school;
}

public void remove() {
it.remove();
}

public String getName() {
return school.getName();
}

public Integer getSchoolId(){
return school.getSchoolId();
}
}

When I am accessing school info (getName,getSchoolId) I am doing it through the session bean and not the object returned from the next().

Here is a snipit of my test code (in a jsp for now):
SchoolListLocalHome schoolListHome = (SchoolListLocalHome)ctx.lookup(EJBConstants.JNDI.SCHOOL_LIST);
SchoolListLocal schoolList = schoolListHome.create();
schoolList.findByState( user.getLeagueContact().getState().getStateId() );
while (schoolList.hasNext()) {
schoolList.next();
String name = schoolList.getName();
Integer id = schoolList.getSchoolId();
}

Help?

[sseaman]

I got it figured out.

I did not realize everything had to happen in one method call.

Here is what I did (is there a better design pattern?):
I have a method in a session bean named findByState(SchoolPK _pk) that calls the same finder method in the entity bean.

In this method I then iterator over the returned collection and create new storage objects called BufferedSchool which is really nothing more than a datastructure copy of the School bean.

These BufferedSchool objects go into a Vector which is returned as a Collection by the findByState method in the session bean.

Seems like a pain in the a** to do this just for optimization. Are there better ways?

[aloubyansky]

You need to get a list of school data object, right?
What causes a pain for you? Session Facade Pattern? It helped me all my life :) Or did I misunderstood you?

[sseaman]

The Session Facade pattern. I guess I've just been expecting more optimization pattern recognition from EJB... ah well.

Anyway, is the manner in which I am attaching the situation correct? Mainly I am curious about the fact that I am using data structure objects to mimic the actual beans.

I'm just looking for verification that I am doing this the best way possible..

The code I posted before from my session bean has changed greatly so please ignore that.

BTW: I have noticed a major preformance improvement. It is now faster by over 3.5 seconds.

[aloubyansky]

The approach you are using is ok. I think you have to decide yourself whether it's the best one for your project because only you know the requirements.