Java and sql loop optimization

Question

I'm grabbing a ResultSet from a database of people and ordering them by last names. I also have a table of other people that relate to that other table but since I can't figure out a way to order the second table by the order of ids in the first, I'm forced to execute a statement referring to the current id each time in the loop.

I'm positive the sql statement being executed in the loop through each iteration is what's slowing things down.

Slow code with reoccurring sql statement in loop

startTime = System.nanoTime();
int id;
String phone;
for (int i = 0; i < studentCount; i++) {
    rs.next();
    String name;
    id = Integer.parseInt(rs.getString(1));
    String middleInitial = rs.getString(3);
    if (middleInitial == null){
        name = rs.getString(2) + " " + rs.getString(4);
    }
    else
        name = rs.getString(2) + " " + middleInitial + " " + rs.getString(4);
    sql = "select parent1mobilephone, parent1workphone from tblParent where id = " + id;
    rs2 = st2.executeQuery(sql);
    rs2.next();
    phone = rs2.getString(1).length() == 12 ? rs2.getString(1) : rs2.getString(2);
    rs2.close();
    DirectoryBoxesPanel.add(new DirectoryBoxes(name, id, phone, selection));    
}
endTime = System.nanoTime();
duration = (endTime - startTime);
System.out.println("For LOOP: " + duration/1000000L);     

Faster unfinished code

startTime = System.nanoTime();
int id;
String phone;
sql = "select parent1mobilephone, parent1workphone from tblParent ORDER by FIRST TABLE";
rs2 = st2.executeQuery(sql);
for (int i = 0; i < studentCount; i++) {
    rs.next();
    String name;
    id = Integer.parseInt(rs.getString(1));
    String middleInitial = rs.getString(3);
    if (middleInitial == null){
        name = rs.getString(2) + " " + rs.getString(4);
    }
    else
        name = rs.getString(2) + " " + middleInitial + " " + rs.getString(4);
    rs2.next();
    phone = rs2.getString(1).length() == 12 ? rs2.getString(1) : rs2.getString(2);
    DirectoryBoxesPanel.add(new DirectoryBoxes(name, id, phone, selection));    
}
endTime = System.nanoTime();
duration = (endTime - startTime);
System.out.println("For LOOP: " + duration/1000000L);     

The only other way I can think of making this faster would be putting the two tables together in one but I don't know if I want to resort to that yet.

Answer 1

I am assuming that your ResultSet rs is based on a SQL statement that goes like this (fully groping in the dark here due to total lack of detail in the question):

sql = "select id, first_name, middle_initial, last_name from tblStudents";

Your code would become MUCH faster if you retrieve the details from tblParent in that same query, like (again, going blind on the table structure):

sql = "select s.id, s.first_name, s.middle_initial, s.last_name " +
      "       p.parent1mobilephone, parent1workphone " +
      "from tblStudents s join tblParents p on p.student_id = s.id " +
      "order by s.last_name";

Your Java code then becomes a simple loop over the ResultSet:

startTime = System.nanoTime();
rs = ...;
while rs.next() {
    integer id = rs.getInt(1));
    String middleInitial = rs.getString(3);
    if (middleInitial == null) {
        String name = rs.getString(2) + " " + rs.getString(4);
    } else {
        String name = rs.getString(2) + " " + middleInitial + " " + rs.getString(4);
    }
    String phone = rs.getString(5).length() == 12 ? rs.getString(5) : rs.getString(6);
    DirectoryBoxesPanel.add(new DirectoryBoxes(name, id, phone, selection));    
}
endTime = System.nanoTime();
duration = (endTime - startTime);
System.out.println("For LOOP: " + duration/1000000L);