Neulich mußte ich u.a. eine Liste von DTOs an das Frontend liefern, die alle Promoventen im Status INAKTIV_BESTAETIGT und INAKTIV_ERINNERUNGSEMAIL_VERSCHICKT enthielt. Neben den Promoventendaten waren noch das Registrierungsdatum und das Downloaddatum erforderlich. Ich wollte eine alte Methode wiederverwenden:
@Override
public List<PersonDTO> getConfirmedPhds() throws MasterDataException {
List<PersonDTO> result = new ArrayList();
List<Person> persons1 = personDAO.getPersonByStatus(GradschoolStatusEnum.INAKTIV_BESTAETIGT.toString());
for (Person person : persons1) {
if (!person.getSubtype().equals(AffiliationsEnum.PROMOVIERENDENVERWALTUNG)) {
continue; // da die Alumnis und IGs nicht behandelt werden sollen
}
PersonDTO personDTO = DO2DTOMapper.map(person);
result.add(personDTO);
}
List<Person> persons2 = personDAO.getPersonByStatus(GradschoolStatusEnum.INAKTIV_ERINNERUNGSEMAIL_VERSCHICKT.toString());
for (Person person : persons2) {
if (!person.getSubtype().equals(AffiliationsEnum.PROMOVIERENDENVERWALTUNG)) {
continue; // da die Alumnis und IGs nicht behandelt werden sollen
}
PersonDTO personDTO = DO2DTOMapper.map(person);
result.add(personDTO);
}
return result;
}
Da für das neue PersonGridDTO zusätzlich die zwei Datumswerte benötigt werden und diese aus einer anderen Tabelle (sprich Entity) als die Personendaten stammen, bot sich an, die Methode getConfirmedPhdsAsPersonGrids() folgendermaßen zu implementieren:
@Override
public List<PersonGridDTO> getConfirmedPhdsAsPersonGrids() throws MasterDataException, RegistrationProcessException {
List<PersonDTO> persons = getConfirmedPhds();
List<PersonGridDTO> result = toPersonGrids(persons);
return result;
}
Dabei wird die zusätzliche Arbeit für die zwei Datumswerte noch in toPersonGrids() verlagert:
private List<PersonGridDTO> toPersonGrids(List personDTOs) throws RegistrationProcessException {
List<PersonGridDTO> personGrids = new ArrayList<PersonGridDTO>();
for (PersonDTO dto : personDTOs) {
Date registrationDate = registrationProcessDao.getLastRegistrationProcessDateByPersonIdAndStatus(
dto.getId(), GradschoolStatusEnum.INAKTIV_REGISTRIERT);
Date downloadDate = registrationProcessDao.getLastRegistrationProcessDateByPersonIdAndStatus(
dto.getId(), GradschoolStatusEnum.INAKTIV_BESTAETIGT);
PersonGridDTO personGrid = new PersonGridDTO(dto.getId(), dto.getStatus(),
dto.getSurname()+", "+dto.getGivenname(), dto.getDateOfBirth(),
registrationDate, downloadDate, false);
personGrids.add(personGrid);
}
return personGrids;
}
Es wird also über alle PersonDTOs iteriert und für jeden PersonDTO zwei DAO-Aufrufe am registrationProcessDao gemacht, um das Registrierungsdatum und Downloaddatum der jeweiligen Person zu erfahren. Zwei DAO-Aufrufe je Person sind wieder zwei Datenbank-Zugriffe (inklusive EJB-Framework Overhead) je Person.
Diese Methode hat sich als sehr teuer herausgestellt: Auf einem Abzug der Produktionsdaten mit etwa 500 in Frage kommenden Datensätzen dauerte der Aufruf an die 10 Sekunden.
Also überlegte ich, ob man alles mit einem Datenbank-Select erledigen könnte. Die Lösung war ein Left Outer Join. Diesen habe ich im DAO zuerst als JPQL formuliert, wobei ich mich nach einem Fehler an einen Internet-Tip hielt, statt des in JPQL gewöhnlichen Keywords „ON“ „WITH“ zu verwenden. Man muß zweimal mit der RegistrationProcess-Entity joinen:
private final static String query2 =
"select NEW de.rrze.ppsa.gs.model.dto.impl.PersonGrid(p.id, p.status, concat(concat(p.surname,', '), p.givenname), p.dateofbirth, r1.date, r2.date) "+
"from Person p "+
"left outer join RegistrationProcess r1 with r1.person.id = p.id and r1.status = 'INAKTIV_REGISTRIERT' "+
"and r1.rank = "+
"(Select max(rank) from RegistrationProcess r where r.person.id = p.id and r.status = 'INAKTIV_REGISTRIERT') "+
"left outer join RegistrationProcess r2 with r2.person.id = p.id and r2.status = 'INAKTIV_BESTAETIGT' "+
"and r2.rank = "+
"(Select max(rank) from RegistrationProcess re where re.person.id = p.id and re.status = 'INAKTIV_BESTAETIGT') "+
"where (p.status = 'INAKTIV_BESTAETIGT' or p.status = 'INAKTIV_ERINNERUNGSEMAIL_VERSCHICKT') "+
"and p.subtype = 'PROMOVIERENDENVERWALTUNG'";
Leider führte auch diese Query auf eine Fehlermeldung. Ein weiteres Googlen führte zum Tip ON und WITH wegzulassen und die Join-Bedingungen alle in die WHERE-Clause zu schmeißen, so dass der nächste Versuch so aussah:
private final static String query2 =
"select NEW de.rrze.ppsa.gs.model.dto.impl.PersonGrid(p.id, p.status, concat(concat(p.surname,', '), p.givenname), p.dateofbirth, r1.date, r2.date) "+
"from Person p "+
"left outer join RegistrationProcess r1 "+
"left outer join RegistrationProcess r2 "+
"where r1.person.id = p.id and r1.status = 'INAKTIV_REGISTRIERT' "+
"and r1.rank = "+
"(Select max(rank) from RegistrationProcess r where r.person.id = p.id and r.status = 'INAKTIV_REGISTRIERT') "+
"and r2.person.id = p.id and r2.status = 'INAKTIV_BESTAETIGT' "+
"and r2.rank = "+
"(Select max(rank) from RegistrationProcess re where re.person.id = p.id and re.status = 'INAKTIV_BESTAETIGT') "+
"and (p.status = 'INAKTIV_BESTAETIGT' or p.status = 'INAKTIV_ERINNERUNGSEMAIL_VERSCHICKT') "+
"and p.subtype = 'PROMOVIERENDENVERWALTUNG'";
Dabei gab es eine Fehlermeldung „path expected error“. Mich überkam das Gefühl, dass EJB 3 bzw. Hibernate bzgl. Left Outer Joins noch nicht ganz ausgereift ist. Meine letzte Idee führte dann endlich zu einem Erfolg. Ich beschloss, eine native Query einzusetzen, also das SQL (richtig: Nicht JPQL!) abzusetzen das auch im PgAdmin durchläuft:
private final static String query2 =
"select p.id as id, p.status as status, p.surname||', '||p.givenname as name, p.dateofbirth as birthdate, "+
"r1.date as registrationDate, r2.date as downloadDate "+
"from yaaff.yaaff_person p "+
"left outer join yaaff.gs_registrationprocess r1 on r1.person_id = p.id and r1.status = 'INAKTIV_REGISTRIERT' "+
"and r1.rank = "+
"(Select max(rank) from yaaff.gs_registrationprocess where person_id = p.id and status = 'INAKTIV_REGISTRIERT') "+
"left outer join yaaff.gs_registrationprocess r2 on r2.person_id = p.id and r2.status = 'INAKTIV_BESTAETIGT' "+
"and r2.rank = "+
"(Select max(rank) from yaaff.gs_registrationprocess where person_id = p.id and status = 'INAKTIV_BESTAETIGT') "+
"where (p.status = 'INAKTIV_BESTAETIGT' or p.status = 'INAKTIV_ERINNERUNGSEMAIL_VERSCHICKT') "+
"and p.subtype = 'PROMOVIERENDENVERWALTUNG'";
Diese Query steht im DAO, sowie folgender Code, um die native Query abzusetzen und die PersonGridDTOs zu bauen:
@Override
public List<PersonGridDTO> getConfirmedAndErinnerungsemailVerschicktePhds() {
List<PersonGridDTO> personGrids = new ArrayList<PersonGridDTO>();
Query q = em.createNativeQuery(query2);
List results = q.getResultList();
for (Object oRow : results) {
Object[] r = (Object[]) oRow;
PersonGridDTO grid = new PersonGridDTO(((BigInteger) r[0]).longValue(), (String) r[1], (String) r[2], (Date) r[3], (Date) r[4], (Date) r[5], false);
personGrids.add(grid);
}
return personGrids;
}
Im Manager wird einfach die DAO-Methode gerufen und ihr Ergebnis weiter an das Frontend gegeben. Das erstaunliche Ergebnis (nach Messung von Sven): 0,07s !!!
Also etwa um den Faktor 100 schneller, als die pure EJB-Lösung, bei der allerdings pro Satz zwei weitere DB-Zugriffe nötig sind. Diese Optimierungsarbeit hat sich wirklich gelohnt.