Author: echatellier Date: 2014-08-25 11:41:41 +0200 (Mon, 25 Aug 2014) New Revision: 590 Url: http://forge.codelutin.com/projects/faxtomail/repository/revisions/590 Log: refs #5655: Remove left outer join to speed up sql server query time Modified: trunk/faxtomail-persistence/src/main/java/com/franciaflex/faxtomail/persistence/entities/EmailTopiaDao.java trunk/faxtomail-service/src/main/java/com/franciaflex/faxtomail/services/service/EmailServiceImpl.java trunk/faxtomail-service/src/main/java/com/franciaflex/faxtomail/services/service/UserServiceImpl.java Modified: trunk/faxtomail-persistence/src/main/java/com/franciaflex/faxtomail/persistence/entities/EmailTopiaDao.java =================================================================== --- trunk/faxtomail-persistence/src/main/java/com/franciaflex/faxtomail/persistence/entities/EmailTopiaDao.java 2014-08-25 08:51:59 UTC (rev 589) +++ trunk/faxtomail-persistence/src/main/java/com/franciaflex/faxtomail/persistence/entities/EmailTopiaDao.java 2014-08-25 09:41:41 UTC (rev 590) @@ -35,6 +35,7 @@ import org.apache.commons.collections4.CollectionUtils; import org.apache.commons.lang3.StringUtils; import org.apache.commons.lang3.time.DateUtils; +import org.hibernate.Hibernate; import org.nuiton.topia.persistence.TopiaEntities; import org.nuiton.topia.persistence.TopiaEntity; import org.nuiton.util.pagination.PaginationParameter; @@ -60,8 +61,8 @@ */ public PaginationResult<Email> search(EmailFilter emailFilter, Set<MailFolder> readMailFolders, PaginationParameter pagination) { - //StringBuilder query = new StringBuilder("FROM " + Email.class.getName() + " E"); - StringBuilder query = new StringBuilder(); + StringBuilder query = new StringBuilder("FROM " + Email.class.getName() + " E"); + //StringBuilder query = new StringBuilder(); Map<String, Object> args = new HashMap<>(); // apply security @@ -365,7 +366,7 @@ } // add same fecth liste as emailService#getEmailForFolder() - String hqlForFetchStep1 = "select E." + TopiaEntity.PROPERTY_TOPIA_ID + " FROM " + Email.class.getName() + " E " + query.toString(); + /*String hqlForFetchStep1 = "select E." + TopiaEntity.PROPERTY_TOPIA_ID + " FROM " + Email.class.getName() + " E " + query.toString(); String hqlForFetchStep2 = "select distinct E FROM " + Email.class.getName() + " E " + addAllFecthes( Email.PROPERTY_PRIORITY, Email.PROPERTY_DEMAND_TYPE, @@ -396,7 +397,47 @@ } PaginationResult<Email> result = PaginationResult.of(sortedEntities, pageResult.getCount(), pageResult.getCurrentPage()); - //PaginationResult<Email> result = findPage(query.toString(), args, pagination); + PaginationResult<Email> result = findPage(query.toString(), args, pagination);*/ + + PaginationResult<Email> result = findPage(query.toString(), args, pagination); + // manual fetch + // Email.PROPERTY_PRIORITY, + // Email.PROPERTY_DEMAND_TYPE, + // Email.PROPERTY_CLIENT, + // Email.PROPERTY_RANGE_ROW + "." + RangeRow.PROPERTY_RANGE, + // Email.PROPERTY_ETAT_ATTENTE, + // Email.PROPERTY_TAKEN_BY, + // Email.PROPERTY_REPLIES, + // Email.PROPERTY_ATTACHMENT, + // Email.PROPERTY_HISTORY + "." + History.PROPERTY_FAX_TO_MAIL_USER, + // Email.PROPERTY_HISTORY + "." + History.PROPERTY_FIELDS, + // Email.PROPERTY_EMAIL_GROUP + "." + EmailGroup.PROPERTY_EMAIL) + for (Email email : result.getElements()) { + Hibernate.initialize(email.getPriority()); + Hibernate.initialize(email.getDemandType()); + Hibernate.initialize(email.getClient()); + List<RangeRow> rangeRows = email.getRangeRow(); + if (rangeRows != null) { + for (RangeRow rangeRow : rangeRows) { + Hibernate.initialize(rangeRow.getRange()); + } + } + Hibernate.initialize(email.getEtatAttente()); + Hibernate.initialize(email.getTakenBy()); + Hibernate.initialize(email.getReplies()); + Hibernate.initialize(email.getAttachment()); + List<History> histories = email.getHistory(); + if (histories != null) { + for (History history : histories) { + Hibernate.initialize(history.getFaxToMailUser()); + Hibernate.initialize(history.getFields()); + } + } + EmailGroup emailGroup = email.getEmailGroup(); + if (emailGroup != null) { + Hibernate.initialize(emailGroup.getEmail()); + } + } return result; } @@ -471,9 +512,9 @@ // List<Pair<String, FaxToMailUser>> result = findAll(query, params); // } - /** + /* * @deprecated beacause this need to be supported by topia (see http://forge.nuiton.org/issues/3427) - */ + * @Deprecated protected String addAllFecthes(String... fetchProperties) { StringBuilder hqlFromClauseBuilder = new StringBuilder(); @@ -520,9 +561,9 @@ return hqlFromClauseBuilder.toString(); } - /** + /* * @deprecated beacause this need to be supported by topia (see http://forge.nuiton.org/issues/3427) - */ + * @Deprecated protected <O extends TopiaEntity> List<O> sortAccordingToIds(List<O> entities, final List<String> idsList) { @@ -537,5 +578,5 @@ List<O> result = Lists.newArrayList(transformed); return result; - } + }*/ } Modified: trunk/faxtomail-service/src/main/java/com/franciaflex/faxtomail/services/service/EmailServiceImpl.java =================================================================== --- trunk/faxtomail-service/src/main/java/com/franciaflex/faxtomail/services/service/EmailServiceImpl.java 2014-08-25 08:51:59 UTC (rev 589) +++ trunk/faxtomail-service/src/main/java/com/franciaflex/faxtomail/services/service/EmailServiceImpl.java 2014-08-25 09:41:41 UTC (rev 590) @@ -42,7 +42,6 @@ import java.util.Enumeration; import java.util.HashMap; import java.util.HashSet; -import java.util.LinkedHashMap; import java.util.List; import java.util.Map; import java.util.Map.Entry; @@ -122,21 +121,48 @@ public Email getFullEmailById(String id) { Email email = getPersistenceContext().getEmailDao() .forTopiaIdEquals(id) - .addAllFetches(Email.PROPERTY_DEMAND_TYPE, + /*.addAllFetches(Email.PROPERTY_DEMAND_TYPE, Email.PROPERTY_RANGE_ROW + "." + RangeRow.PROPERTY_RANGE, - Email.PROPERTY_HISTORY, Email.PROPERTY_HISTORY + "." + History.PROPERTY_FAX_TO_MAIL_USER, Email.PROPERTY_HISTORY + "." + History.PROPERTY_FIELDS, Email.PROPERTY_REPLIES, Email.PROPERTY_ATTACHMENT, Email.PROPERTY_TAKEN_BY, Email.PROPERTY_CLIENT, - Email.PROPERTY_EMAIL_GROUP, Email.PROPERTY_EMAIL_GROUP + "." + EmailGroup.PROPERTY_EMAIL, - Email.PROPERTY_EMAIL_GROUP + "." + EmailGroup.PROPERTY_EMAIL +"." + Email.PROPERTY_MAIL_FOLDER, + Email.PROPERTY_EMAIL_GROUP + "." + EmailGroup.PROPERTY_EMAIL + "." + Email.PROPERTY_MAIL_FOLDER, Email.PROPERTY_ETAT_ATTENTE, - Email.PROPERTY_PRIORITY).findUnique(); + Email.PROPERTY_PRIORITY)*/.findUnique(); + // manual fetch + Hibernate.initialize(email.getPriority()); + Hibernate.initialize(email.getDemandType()); + Hibernate.initialize(email.getClient()); + List<RangeRow> rangeRows = email.getRangeRow(); + if (rangeRows != null) { + for (RangeRow rangeRow : rangeRows) { + Hibernate.initialize(rangeRow.getRange()); + } + } + Hibernate.initialize(email.getEtatAttente()); + Hibernate.initialize(email.getTakenBy()); + Hibernate.initialize(email.getReplies()); + Hibernate.initialize(email.getAttachment()); + List<History> histories = email.getHistory(); + if (histories != null) { + for (History history : histories) { + Hibernate.initialize(history.getFaxToMailUser()); + Hibernate.initialize(history.getFields()); + } + } + EmailGroup emailGroup = email.getEmailGroup(); + if (emailGroup != null) { + Collection<Email> emails = emailGroup.getEmail(); + for (Email email2 : emails) { + Hibernate.initialize(email2.getMailFolder()); + } + } + return email; } @@ -573,7 +599,7 @@ result = dao.forMailFolderEquals(folder) .addNull(Email.PROPERTY_ARCHIVE_DATE) .addNotEquals(Email.PROPERTY_DEMAND_STATUS, DemandStatus.ARCHIVED) - .addAllFetches(Email.PROPERTY_PRIORITY, + /*.addAllFetches(Email.PROPERTY_PRIORITY, Email.PROPERTY_DEMAND_TYPE, Email.PROPERTY_CLIENT, Email.PROPERTY_RANGE_ROW + "." + RangeRow.PROPERTY_RANGE, @@ -582,8 +608,33 @@ Email.PROPERTY_REPLIES, Email.PROPERTY_ATTACHMENT, Email.PROPERTY_HISTORY + "." + History.PROPERTY_FAX_TO_MAIL_USER, - Email.PROPERTY_HISTORY + "." + History.PROPERTY_FIELDS) + Email.PROPERTY_HISTORY + "." + History.PROPERTY_FIELDS)*/ .findPage(page); + + + // manual fetch + for (Email email : result.getElements()) { + Hibernate.initialize(email.getPriority()); + Hibernate.initialize(email.getDemandType()); + Hibernate.initialize(email.getClient()); + List<RangeRow> rangeRows = email.getRangeRow(); + if (rangeRows != null) { + for (RangeRow rangeRow : rangeRows) { + Hibernate.initialize(rangeRow.getRange()); + } + } + Hibernate.initialize(email.getEtatAttente()); + Hibernate.initialize(email.getTakenBy()); + Hibernate.initialize(email.getReplies()); + Hibernate.initialize(email.getAttachment()); + List<History> histories = email.getHistory(); + if (histories != null) { + for (History history : histories) { + Hibernate.initialize(history.getFaxToMailUser()); + Hibernate.initialize(history.getFields()); + } + } + } } else { List<Email> elements = Collections.emptyList(); Modified: trunk/faxtomail-service/src/main/java/com/franciaflex/faxtomail/services/service/UserServiceImpl.java =================================================================== --- trunk/faxtomail-service/src/main/java/com/franciaflex/faxtomail/services/service/UserServiceImpl.java 2014-08-25 08:51:59 UTC (rev 589) +++ trunk/faxtomail-service/src/main/java/com/franciaflex/faxtomail/services/service/UserServiceImpl.java 2014-08-25 09:41:41 UTC (rev 590) @@ -26,6 +26,8 @@ import java.util.List; +import org.hibernate.Hibernate; + import com.franciaflex.faxtomail.persistence.entities.FaxToMailUser; import com.franciaflex.faxtomail.persistence.entities.FaxToMailUserGroup; import com.franciaflex.faxtomail.persistence.entities.FaxToMailUserGroupTopiaDao; @@ -85,8 +87,9 @@ public FaxToMailUser getUser(String topiaId) { FaxToMailUserTopiaDao dao = getPersistenceContext().getFaxToMailUserDao(); FaxToMailUser user = dao.forTopiaIdEquals(topiaId) - .addAllFetches(FaxToMailUser.PROPERTY_AFFECTED_FOLDERS) + /*.addAllFetches(FaxToMailUser.PROPERTY_AFFECTED_FOLDERS)*/ .findUnique(); + Hibernate.initialize(user.getAffectedFolders()); return user; } }