Which is better for performance and makes more sense?

tlh1005 · **Joined:** Fri Mar 05, 2004 1:48 pm **Posts:** 28

Here is my scenerio.
I have two tables. A STUDENT table and a table of COURSES.
It is a One-To-Many relationship a Student has many Courses.

Lets say I need to find Courses for a Student taken in Fall 2003.
I have two options:

#1
I can get the set/iteration of ALL of the student's courses and loop through selecting the particular OBJECTS which have FALL and 2003 field values.

OR

#2
I can have hibernate select ONLY the records with FALL and 2003 from the start.

It seems to me option# 2 is the better approach. But what if later in the application I need the Courses for Spring 2004? Then I have to query again for those courses instead of looping through the whole set I would have already had from option# 1. Also note, something I do is create a vector of all of the Courses returned by Hibernate. Is this pointless? I don't have a good grasp of everything Hibernate does between the JDBC calls and the objects. Hibernate generated a method called getCourses() which returns the complete set of courses. When I decided to put the Courses into a vector I was making the assumption that each time I call getCourses() it will query the database for them. I was trying to eliminate DB calls. Is this correct? If it is correct, is it more of a performance hit to deal with Java objects in the vector or to make more DB calls?

emmanuel · **Posted:** Tue Apr 20, 2004 10:22 am

Do a proper test will give you the answer, there is no absolute answer.
An intermediary solution would be to use a cached query.

tlh1005 · **Joined:** Fri Mar 05, 2004 1:48 pm **Posts:** 28

One problem was that I didn't understand all of the behaviors of Hibernate. I still don't, but one thing that is helping is setting hibernate to print out the SQL. I am finding out that it doesn't generate a SQL call as often as I would have thought. My goal is to reduce the DB hits because databse performance is where our organization suffers. Our web apps have to compete with Cobol batch jobs which have priority on MVS :-(

jeyoung439 · **Posted:** Tue Apr 20, 2004 10:55 am

Good OOP design would dictate adding the following method to your Student class.

Code:

public Collection getCourseForPeriod(Semester semester) {
   ArrayList courses = new ArrayList();
   Iterator itr = getCourses().iterator();
   while (itr.hasNext() ) {
      Course course = (Course) itr.next();
      if (course.getSemester().equals(semester) ) {
         result.add(course);
      }
   }
   return courses;
}

But, you may prefer to execute a SQL directly if performance becomes an issue.

emmanuel · **Posted:** Thu Apr 22, 2004 5:18 am

So choose the first one and beware stale data.
You'll minimize SQL roundtrips and use more Java CPU