Fixes issue 32023 & Fixes issue 32190: Improve PriceDifference performance
authorAlvaro Ferraz <alvaro.ferraz@openbravo.com>
Mon, 15 Feb 2016 19:53:12 +0100
changeset 28623 ca36991325a4
parent 28622 815ba738d4dc
child 28624 f58e58d7e308
Fixes issue 32023 & Fixes issue 32190: Improve PriceDifference performance

Some improvements have been done in CostingUtils.getCurrentValuedStock, CostAdjustmentUtils.getValuedStockOnMovementDateByAttrAndLocator, CostAdjustmentUtils.getValuedStockOnTransactionDate and CostingMigrationProcess.getCurrentValuedStock methods:
- Use TransactionCost.PROPERTY_ACCOUNTINGDATE field instead of coalesce(ShipmentInOut.PROPERTY_ACCOUNTINGDATE, MaterialTransaction.PROPERTY_MOVEMENTDATE) to avoid join with ShipmentInOutLine and ShipmentInOut tables.
- Use a scroll instead of looping through a list.
- Loop can not be avoid due to the need of calculate conversion rate recursively.
- Clear every 100 loops will not be done as DAL objects will not be loaded in memory and to avoid session cleared errors.
Retrieve only one or two results in some queries where more results were not needed.
Use isEmpty() instead of size().
Retrieve only lineno field instead of full object in CostAdjustmentUtils.getNewLineNo method.
Improve CostAdjustmentUtils.getTrxCost method.
Do a clear every 100 loops in PriceDifferenceProcess.processPriceDifference method to improve performance.
src/org/openbravo/costing/AverageAlgorithm.java
src/org/openbravo/costing/AverageCostAdjustment.java
src/org/openbravo/costing/CostAdjustmentUtils.java
src/org/openbravo/costing/CostingMigrationProcess.java
src/org/openbravo/costing/CostingUtils.java
src/org/openbravo/costing/PriceDifferenceProcess.java
--- a/src/org/openbravo/costing/AverageAlgorithm.java	Fri Feb 19 11:31:51 2016 +0100
+++ b/src/org/openbravo/costing/AverageAlgorithm.java	Mon Feb 15 19:53:12 2016 +0100
@@ -11,7 +11,7 @@
  * under the License.
  * The Original Code is Openbravo ERP.
  * The Initial Developer of the Original Code is Openbravo SLU
- * All portions are Copyright (C) 2012-2015 Openbravo SLU
+ * All portions are Copyright (C) 2012-2016 Openbravo SLU
  * All Rights Reserved.
  * Contributor(s):  ______________________________________.
  *************************************************************************
@@ -187,13 +187,15 @@
     } else {
       costQry.setNamedParameter("org", costOrg.getId());
     }
+    costQry.setMaxResult(2);
 
     List<Costing> costList = costQry.list();
+    int size = costList.size();
     // If no average cost is found return null.
-    if (costList.size() == 0) {
+    if (size == 0) {
       return null;
     }
-    if (costList.size() > 1) {
+    if (size > 1) {
       log4j.warn("More than one cost found for same date: " + OBDateUtils.formatDate(date)
           + " for product: " + product.getName() + " (" + product.getId() + ")");
     }
@@ -239,13 +241,15 @@
     } else {
       costQry.setNamedParameter("org", costOrg.getId());
     }
+    costQry.setMaxResult(2);
 
     List<Costing> costList = costQry.list();
+    int size = costList.size();
     // If no average cost is found return null.
-    if (costList.size() == 0) {
+    if (size == 0) {
       return null;
     }
-    if (costList.size() > 1) {
+    if (size > 1) {
       log4j.warn("More than one cost found for same date: " + OBDateUtils.formatDate(date)
           + " for product: " + product.getName() + " (" + product.getId() + ")");
     }
--- a/src/org/openbravo/costing/AverageCostAdjustment.java	Fri Feb 19 11:31:51 2016 +0100
+++ b/src/org/openbravo/costing/AverageCostAdjustment.java	Mon Feb 15 19:53:12 2016 +0100
@@ -11,7 +11,7 @@
  * under the License.
  * The Original Code is Openbravo ERP.
  * The Initial Developer of the Original Code is Openbravo SLU
- * All portions are Copyright (C) 2014-2015 Openbravo SLU
+ * All portions are Copyright (C) 2014-2016 Openbravo SLU
  * All Rights Reserved.
  * Contributor(s):  ______________________________________.
  *************************************************************************
@@ -172,7 +172,7 @@
         log.debug("Negative stock correction. Amount: {}, new cost {}", negCorrAmt.toPlainString(),
             cost.toPlainString());
       }
-      if (basetrx.getMaterialMgmtCostingList().size() == 0) {
+      if (basetrx.getMaterialMgmtCostingList().isEmpty()) {
         Date newDate = new Date();
         Date dateTo = costing.getEndingDate();
         costing.setEndingDate(newDate);
@@ -388,7 +388,7 @@
                   .toPlainString());
             }
           }
-          if (trx.getMaterialMgmtCostingList().size() != 0) {
+          if (!trx.getMaterialMgmtCostingList().isEmpty()) {
             Costing curCosting = trx.getMaterialMgmtCostingList().get(0);
             if (currentStock.signum() != 0) {
               cost = currentValueAmt.add(adjustmentBalance).divide(currentStock, costCurPrecission,
@@ -943,10 +943,7 @@
         CostAdjustmentLine.class);
     critLines.add(Restrictions.eq(CostAdjustmentLine.PROPERTY_INVENTORYTRANSACTION, trx));
     critLines.add(Restrictions.eq(CostAdjustmentLine.PROPERTY_ISBACKDATEDTRX, true));
-    final List<CostAdjustmentLine> critLinesList = critLines.list();
-    if (critLinesList.size() > 0) {
-      return true;
-    }
-    return false;
+    critLines.setMaxResults(1);
+    return critLines.uniqueResult() != null;
   }
 }
--- a/src/org/openbravo/costing/CostAdjustmentUtils.java	Fri Feb 19 11:31:51 2016 +0100
+++ b/src/org/openbravo/costing/CostAdjustmentUtils.java	Mon Feb 15 19:53:12 2016 +0100
@@ -11,7 +11,7 @@
  * under the License.
  * The Original Code is Openbravo ERP.
  * The Initial Developer of the Original Code is Openbravo SLU
- * All portions are Copyright (C) 2014-2015 Openbravo SLU
+ * All portions are Copyright (C) 2014-2016 Openbravo SLU
  * All Rights Reserved.
  * Contributor(s):  ______________________________________.
  *************************************************************************
@@ -22,10 +22,12 @@
 import java.math.RoundingMode;
 import java.util.Date;
 import java.util.HashMap;
-import java.util.List;
 import java.util.Set;
 
+import org.apache.commons.lang.StringUtils;
 import org.hibernate.Query;
+import org.hibernate.ScrollMode;
+import org.hibernate.ScrollableResults;
 import org.hibernate.criterion.Restrictions;
 import org.openbravo.advpaymentmngt.utility.FIN_Utility;
 import org.openbravo.base.exception.OBException;
@@ -52,8 +54,6 @@
 import org.openbravo.model.materialmgmt.transaction.InventoryCount;
 import org.openbravo.model.materialmgmt.transaction.InventoryCountLine;
 import org.openbravo.model.materialmgmt.transaction.MaterialTransaction;
-import org.openbravo.model.materialmgmt.transaction.ShipmentInOut;
-import org.openbravo.model.materialmgmt.transaction.ShipmentInOutLine;
 import org.slf4j.Logger;
 import org.slf4j.LoggerFactory;
 
@@ -202,17 +202,18 @@
 
   private static Long getNewLineNo(CostAdjustment cadj) {
     StringBuffer where = new StringBuffer();
-    where.append(" as cal");
+    where.append(" select " + CostAdjustmentLine.PROPERTY_LINENO);
+    where.append(" from " + CostAdjustmentLine.ENTITY_NAME + " as cal");
     where.append(" where cal." + CostAdjustmentLine.PROPERTY_COSTADJUSTMENT
         + ".id = :costAdjustment");
     where.append(" order by cal." + CostAdjustmentLine.PROPERTY_LINENO + " desc");
-    OBQuery<CostAdjustmentLine> calQry = OBDal.getInstance().createQuery(CostAdjustmentLine.class,
-        where.toString());
-    calQry.setNamedParameter("costAdjustment", cadj.getId());
-    calQry.setMaxResult(1);
+    Query calQry = OBDal.getInstance().getSession().createQuery(where.toString());
+    calQry.setParameter("costAdjustment", cadj.getId());
+    calQry.setMaxResults(1);
 
-    if (calQry.uniqueResult() != null) {
-      return calQry.uniqueResult().getLineNo() + 10L;
+    Long lineNo = (Long) calQry.uniqueResult();
+    if (lineNo != null) {
+      return lineNo + 10L;
     }
     return 10L;
   }
@@ -225,17 +226,41 @@
           trx.getId());
       throw new OBException("@NoCostFoundForTrxOnDate@ @Transaction@: " + trx.getIdentifier());
     }
+
+    StringBuffer select = new StringBuffer();
+    select.append(" select sum(tc." + TransactionCost.PROPERTY_COST + ") as cost");
+    select.append(" , tc." + TransactionCost.PROPERTY_CURRENCY + ".id as currency");
+    select.append(" , tc." + TransactionCost.PROPERTY_COSTDATE + " as date");
+    select.append(" from " + TransactionCost.ENTITY_NAME + " as tc");
+    select.append(" where tc." + TransactionCost.PROPERTY_INVENTORYTRANSACTION + ".id = :trxId");
+    if (justUnitCost) {
+      select.append(" and tc." + TransactionCost.PROPERTY_UNITCOST + " = true");
+    }
+    select.append(" group by tc." + TransactionCost.PROPERTY_CURRENCY);
+    select.append(" , tc." + TransactionCost.PROPERTY_COSTDATE);
+
+    Query qry = OBDal.getInstance().getSession().createQuery(select.toString());
+    qry.setParameter("trxId", trx.getId());
+    ScrollableResults scroll = qry.scroll(ScrollMode.FORWARD_ONLY);
+
     BigDecimal cost = BigDecimal.ZERO;
-    for (TransactionCost trxCost : trx.getTransactionCostList()) {
-      if (!justUnitCost || trxCost.isUnitCost()) {
-        if (trxCost.getCurrency().getId().equals(currency.getId())) {
-          cost = cost.add(trxCost.getCost());
+    try {
+      while (scroll.next()) {
+        Object[] resultSet = scroll.get();
+        BigDecimal costAmt = (BigDecimal) resultSet[0];
+        String origCurId = (String) resultSet[1];
+
+        if (StringUtils.equals(origCurId, currency.getId())) {
+          cost = cost.add(costAmt);
         } else {
-          cost = cost.add(FinancialUtils.getConvertedAmount(trxCost.getCost(),
-              trxCost.getCurrency(), currency, trxCost.getCostDate(), trxCost.getOrganization(),
-              FinancialUtils.PRECISION_COSTING));
+          Currency origCur = OBDal.getInstance().get(Currency.class, origCurId);
+          Date convDate = (Date) resultSet[2];
+          cost = cost.add(FinancialUtils.getConvertedAmount(costAmt, origCur, currency, convDate,
+              trx.getOrganization(), FinancialUtils.PRECISION_COSTING));
         }
       }
+    } finally {
+      scroll.close();
     }
     return cost;
   }
@@ -504,15 +529,12 @@
         + " < 0 then -tc." + TransactionCost.PROPERTY_COST);
     select.append("     else tc." + TransactionCost.PROPERTY_COST + " end ) as cost");
     select.append(" , tc." + TransactionCost.PROPERTY_CURRENCY + ".id as currency");
-    select.append(" , coalesce(sr." + ShipmentInOut.PROPERTY_ACCOUNTINGDATE + ", trx."
-        + MaterialTransaction.PROPERTY_MOVEMENTDATE + ") as mdate");
+    select.append(" , tc." + TransactionCost.PROPERTY_ACCOUNTINGDATE + " as mdate");
     select.append(" , sum(trx." + MaterialTransaction.PROPERTY_MOVEMENTQUANTITY + ") as stock");
 
     select.append(" from " + TransactionCost.ENTITY_NAME + " as tc");
     select.append("  join tc." + TransactionCost.PROPERTY_INVENTORYTRANSACTION + " as trx");
     select.append("  join trx." + MaterialTransaction.PROPERTY_STORAGEBIN + " as locator");
-    select.append("  left join trx." + MaterialTransaction.PROPERTY_GOODSSHIPMENTLINE + " as line");
-    select.append("  left join line." + ShipmentInOutLine.PROPERTY_SHIPMENTRECEIPT + " as sr");
 
     Date backdatedTrxFrom = null;
     if (backdatedTransactionsFixed) {
@@ -549,8 +571,7 @@
     select.append("   and trx." + MaterialTransaction.PROPERTY_ORGANIZATION + ".id in (:orgs)");
 
     select.append(" group by tc." + TransactionCost.PROPERTY_CURRENCY);
-    select.append("   , coalesce(sr." + ShipmentInOut.PROPERTY_ACCOUNTINGDATE + ", trx."
-        + MaterialTransaction.PROPERTY_MOVEMENTDATE + ")");
+    select.append("   , tc." + TransactionCost.PROPERTY_ACCOUNTINGDATE);
 
     Query trxQry = OBDal.getInstance().getSession().createQuery(select.toString());
     trxQry.setParameter("product", product);
@@ -565,22 +586,28 @@
       trxQry.setParameter("asi", asi);
     }
     trxQry.setParameterList("orgs", orgs);
-    @SuppressWarnings("unchecked")
-    List<Object[]> o = trxQry.list();
-    BigDecimal costsum = BigDecimal.ZERO;
-    for (Object[] resultSet : o) {
-      BigDecimal origAmt = (BigDecimal) resultSet[0];
-      Currency origCur = OBDal.getInstance().get(Currency.class, resultSet[1]);
-      Date convDate = (Date) resultSet[2];
 
-      if (origCur != currency) {
-        costsum = costsum.add(FinancialUtils.getConvertedAmount(origAmt, origCur, currency,
-            convDate, org, FinancialUtils.PRECISION_COSTING));
-      } else {
-        costsum = costsum.add(origAmt);
+    ScrollableResults scroll = trxQry.scroll(ScrollMode.FORWARD_ONLY);
+    BigDecimal sum = BigDecimal.ZERO;
+    try {
+      while (scroll.next()) {
+        Object[] resultSet = scroll.get();
+        BigDecimal origAmt = (BigDecimal) resultSet[0];
+        String origCurId = (String) resultSet[1];
+
+        if (StringUtils.equals(origCurId, currency.getId())) {
+          sum = sum.add(origAmt);
+        } else {
+          Currency origCur = OBDal.getInstance().get(Currency.class, origCurId);
+          Date convDate = (Date) resultSet[2];
+          sum = sum.add(FinancialUtils.getConvertedAmount(origAmt, origCur, currency, convDate,
+              org, FinancialUtils.PRECISION_COSTING));
+        }
       }
+    } finally {
+      scroll.close();
     }
-    return costsum;
+    return sum;
   }
 
   /**
@@ -698,24 +725,29 @@
       trxQry.setParameter("mvtdate", trx.getMovementDate());
       trxQry.setParameter("fixbdt", CostingUtils.getCostingRuleFixBackdatedFrom(costingRule));
     }
+    trxQry.setParameterList("orgs", orgs);
 
-    trxQry.setParameterList("orgs", orgs);
-    @SuppressWarnings("unchecked")
-    List<Object[]> o = trxQry.list();
-    BigDecimal costsum = BigDecimal.ZERO;
-    for (Object[] resultSet : o) {
-      BigDecimal origAmt = (BigDecimal) resultSet[0];
-      Currency origCur = OBDal.getInstance().get(Currency.class, resultSet[1]);
-      Date convDate = (Date) resultSet[2];
+    ScrollableResults scroll = trxQry.scroll(ScrollMode.FORWARD_ONLY);
+    BigDecimal sum = BigDecimal.ZERO;
+    try {
+      while (scroll.next()) {
+        Object[] resultSet = scroll.get();
+        BigDecimal origAmt = (BigDecimal) resultSet[0];
+        String origCurId = (String) resultSet[1];
 
-      if (origCur != currency) {
-        costsum = costsum.add(FinancialUtils.getConvertedAmount(origAmt, origCur, currency,
-            convDate, costorg, FinancialUtils.PRECISION_COSTING));
-      } else {
-        costsum = costsum.add(origAmt);
+        if (StringUtils.equals(origCurId, currency.getId())) {
+          sum = sum.add(origAmt);
+        } else {
+          Currency origCur = OBDal.getInstance().get(Currency.class, origCurId);
+          Date convDate = (Date) resultSet[2];
+          sum = sum.add(FinancialUtils.getConvertedAmount(origAmt, origCur, currency, convDate,
+              costorg, FinancialUtils.PRECISION_COSTING));
+        }
       }
+    } finally {
+      scroll.close();
     }
-    return costsum;
+    return sum;
   }
 
   /**
--- a/src/org/openbravo/costing/CostingMigrationProcess.java	Fri Feb 19 11:31:51 2016 +0100
+++ b/src/org/openbravo/costing/CostingMigrationProcess.java	Mon Feb 15 19:53:12 2016 +0100
@@ -26,6 +26,7 @@
 import java.util.List;
 import java.util.Set;
 
+import org.apache.commons.lang.StringUtils;
 import org.apache.commons.lang.time.DateUtils;
 import org.apache.log4j.Logger;
 import org.hibernate.Query;
@@ -573,49 +574,49 @@
         + " < 0 then -tc." + TransactionCost.PROPERTY_COST);
     select.append("     else tc." + TransactionCost.PROPERTY_COST + " end ) as cost,");
     select.append("  tc." + TransactionCost.PROPERTY_CURRENCY + ".id as currency,");
-    select.append("  coalesce(sr." + ShipmentInOut.PROPERTY_ACCOUNTINGDATE + ", trx."
-        + MaterialTransaction.PROPERTY_MOVEMENTDATE + ") as mdate,");
+    select.append("  tc." + TransactionCost.PROPERTY_ACCOUNTINGDATE + " as mdate,");
     select.append("  sum(trx." + MaterialTransaction.PROPERTY_MOVEMENTQUANTITY + ") as stock");
 
     select.append(" from " + TransactionCost.ENTITY_NAME + " as tc");
     select.append("  join tc." + TransactionCost.PROPERTY_INVENTORYTRANSACTION + " as trx");
     select.append("  join trx." + MaterialTransaction.PROPERTY_STORAGEBIN + " as locator");
-    select.append("  left join trx." + MaterialTransaction.PROPERTY_GOODSSHIPMENTLINE + " as line");
-    select.append("  left join line." + ShipmentInOutLine.PROPERTY_SHIPMENTRECEIPT + " as sr");
 
     select.append(" where trx." + MaterialTransaction.PROPERTY_PRODUCT + ".id = :product");
     // Include only transactions that have its cost calculated
     select.append("   and trx." + MaterialTransaction.PROPERTY_ISCOSTCALCULATED + " = true");
     select.append("   and trx." + MaterialTransaction.PROPERTY_ORGANIZATION + ".id in (:orgs)");
     select.append(" group by tc." + TransactionCost.PROPERTY_CURRENCY + ",");
-    select.append("   coalesce(sr." + ShipmentInOut.PROPERTY_ACCOUNTINGDATE + ", trx."
-        + MaterialTransaction.PROPERTY_MOVEMENTDATE + ")");
+    select.append("   tc." + TransactionCost.PROPERTY_ACCOUNTINGDATE);
 
     Query trxQry = OBDal.getInstance().getSession().createQuery(select.toString());
     trxQry.setParameter("product", productId);
     trxQry.setParameterList("orgs", orgs);
-    @SuppressWarnings("unchecked")
-    List<Object[]> stocks = trxQry.list();
+
+    ScrollableResults scroll = trxQry.scroll(ScrollMode.FORWARD_ONLY);
     BigDecimal totalAmt = BigDecimal.ZERO;
     BigDecimal totalQty = BigDecimal.ZERO;
-    HashMap<String, BigDecimal> retStock = new HashMap<String, BigDecimal>();
-    if (stocks.size() > 0) {
-      for (Object[] resultSet : stocks) {
+    try {
+      while (scroll.next()) {
+        Object[] resultSet = scroll.get();
         BigDecimal costAmt = (BigDecimal) resultSet[0];
-        Currency origCur = OBDal.getInstance().get(Currency.class, resultSet[1]);
-        Date convDate = (Date) resultSet[2];
+        String origCurId = (String) resultSet[1];
         BigDecimal qty = (BigDecimal) resultSet[3];
 
-        if (origCur != currency) {
+        if (StringUtils.equals(origCurId, currency.getId())) {
+          totalAmt = totalAmt.add(costAmt);
+        } else {
+          Currency origCur = OBDal.getInstance().get(Currency.class, origCurId);
+          Date convDate = (Date) resultSet[2];
           totalAmt = totalAmt.add(FinancialUtils.getConvertedAmount(costAmt, origCur, currency,
               convDate, OBDal.getInstance().get(Organization.class, orgId),
               FinancialUtils.PRECISION_COSTING));
-        } else {
-          totalAmt = totalAmt.add(costAmt);
         }
         totalQty = totalQty.add(qty);
       }
+    } finally {
+      scroll.close();
     }
+    HashMap<String, BigDecimal> retStock = new HashMap<String, BigDecimal>();
     retStock.put("cost", totalAmt);
     retStock.put("stock", totalQty);
     return retStock;
--- a/src/org/openbravo/costing/CostingUtils.java	Fri Feb 19 11:31:51 2016 +0100
+++ b/src/org/openbravo/costing/CostingUtils.java	Mon Feb 15 19:53:12 2016 +0100
@@ -11,7 +11,7 @@
  * under the License.
  * The Original Code is Openbravo ERP.
  * The Initial Developer of the Original Code is Openbravo SLU
- * All portions are Copyright (C) 2012-2015 Openbravo SLU
+ * All portions are Copyright (C) 2012-2016 Openbravo SLU
  * All Rights Reserved.
  * Contributor(s):  ______________________________________.
  *************************************************************************
@@ -31,6 +31,8 @@
 import org.apache.commons.lang.StringUtils;
 import org.apache.log4j.Logger;
 import org.hibernate.Query;
+import org.hibernate.ScrollMode;
+import org.hibernate.ScrollableResults;
 import org.hibernate.criterion.Restrictions;
 import org.openbravo.base.exception.OBException;
 import org.openbravo.base.session.OBPropertiesProvider;
@@ -64,8 +66,6 @@
 import org.openbravo.model.materialmgmt.transaction.InventoryCount;
 import org.openbravo.model.materialmgmt.transaction.InventoryCountLine;
 import org.openbravo.model.materialmgmt.transaction.MaterialTransaction;
-import org.openbravo.model.materialmgmt.transaction.ShipmentInOut;
-import org.openbravo.model.materialmgmt.transaction.ShipmentInOutLine;
 import org.openbravo.model.pricing.pricelist.PriceList;
 import org.openbravo.model.pricing.pricelist.ProductPrice;
 import org.openbravo.service.db.DalConnectionProvider;
@@ -320,12 +320,16 @@
     }
     obcCosting.add(Restrictions.eq(Costing.PROPERTY_ORGANIZATION, org));
     obcCosting.setFilterOnReadableOrganization(false);
-    if (obcCosting.count() > 0) {
-      if (obcCosting.count() > 1) {
+    obcCosting.setMaxResults(2);
+
+    List<Costing> obcCostingList = obcCosting.list();
+    int size = obcCostingList.size();
+    if (size != 0) {
+      if (size > 1) {
         log4j.warn("More than one cost found for same date: " + OBDateUtils.formatDate(date)
             + " for product: " + product.getName() + " (" + product.getId() + ")");
       }
-      return obcCosting.list().get(0);
+      return obcCostingList.get(0);
     } else if (recheckWithoutDimensions) {
       return getStandardCostDefinition(product, org, date, getEmptyDimensions(), false);
     }
@@ -396,14 +400,11 @@
         + " < 0 then -tc." + TransactionCost.PROPERTY_COST);
     select.append("     else tc." + TransactionCost.PROPERTY_COST + " end ) as cost,");
     select.append("  tc." + TransactionCost.PROPERTY_CURRENCY + ".id as currency,");
-    select.append("  coalesce(sr." + ShipmentInOut.PROPERTY_ACCOUNTINGDATE + ", trx."
-        + MaterialTransaction.PROPERTY_MOVEMENTDATE + ") as mdate");
+    select.append("  tc." + TransactionCost.PROPERTY_ACCOUNTINGDATE + " as mdate");
 
     select.append(" from " + TransactionCost.ENTITY_NAME + " as tc");
     select.append("  join tc." + TransactionCost.PROPERTY_INVENTORYTRANSACTION + " as trx");
     select.append("  join trx." + MaterialTransaction.PROPERTY_STORAGEBIN + " as locator");
-    select.append("  left join trx." + MaterialTransaction.PROPERTY_GOODSSHIPMENTLINE + " as line");
-    select.append("  left join line." + ShipmentInOutLine.PROPERTY_SHIPMENTRECEIPT + " as sr");
 
     select.append(" where trx." + MaterialTransaction.PROPERTY_PRODUCT + ".id = :product");
     select.append("  and trx." + MaterialTransaction.PROPERTY_TRANSACTIONPROCESSDATE + " <= :date");
@@ -414,8 +415,7 @@
     }
     select.append("   and trx." + MaterialTransaction.PROPERTY_ORGANIZATION + ".id in (:orgs)");
     select.append(" group by tc." + TransactionCost.PROPERTY_CURRENCY + ",");
-    select.append("   coalesce(sr." + ShipmentInOut.PROPERTY_ACCOUNTINGDATE + ", trx."
-        + MaterialTransaction.PROPERTY_MOVEMENTDATE + ")");
+    select.append("   tc." + TransactionCost.PROPERTY_ACCOUNTINGDATE);
 
     Query trxQry = OBDal.getInstance().getSession().createQuery(select.toString());
     trxQry.setParameter("product", product.getId());
@@ -424,24 +424,28 @@
       trxQry.setParameter("warehouse", costDimensions.get(CostDimension.Warehouse).getId());
     }
     trxQry.setParameterList("orgs", orgs);
-    @SuppressWarnings("unchecked")
-    List<Object[]> o = trxQry.list();
+
+    ScrollableResults scroll = trxQry.scroll(ScrollMode.FORWARD_ONLY);
     BigDecimal sum = BigDecimal.ZERO;
-    if (o.size() == 0) {
-      return sum;
+    try {
+      while (scroll.next()) {
+        Object[] resultSet = scroll.get();
+        BigDecimal origAmt = (BigDecimal) resultSet[0];
+        String origCurId = (String) resultSet[1];
+
+        if (StringUtils.equals(origCurId, currency.getId())) {
+          sum = sum.add(origAmt);
+        } else {
+          Currency origCur = OBDal.getInstance().get(Currency.class, origCurId);
+          Date convDate = (Date) resultSet[2];
+          sum = sum.add(FinancialUtils.getConvertedAmount(origAmt, origCur, currency, convDate,
+              org, FinancialUtils.PRECISION_COSTING));
+        }
+      }
+    } finally {
+      scroll.close();
     }
-    for (Object[] resultSet : o) {
-      BigDecimal origAmt = (BigDecimal) resultSet[0];
-      Currency origCur = OBDal.getInstance().get(Currency.class, resultSet[1]);
-      Date convDate = (Date) resultSet[2];
 
-      if (origCur != currency) {
-        sum = sum.add(FinancialUtils.getConvertedAmount(origAmt, origCur, currency, convDate, org,
-            FinancialUtils.PRECISION_COSTING));
-      } else {
-        sum = sum.add(origAmt);
-      }
-    }
     return sum;
   }
 
@@ -505,12 +509,12 @@
     crQry.setNamedParameter("startdate", date);
     crQry.setNamedParameter("enddate", date);
     crQry.setMaxResult(1);
-    List<CostingRule> costRules = crQry.list();
-    if (costRules.size() == 0) {
+    CostingRule costRule = crQry.uniqueResult();
+    if (costRule == null) {
       throw new OBException("@NoCostingRuleFoundForOrganizationAndDate@ @Organization@: "
           + org.getName() + ", @Date@: " + OBDateUtils.formatDate(date));
     }
-    return costRules.get(0);
+    return costRule;
   }
 
   /**
--- a/src/org/openbravo/costing/PriceDifferenceProcess.java	Fri Feb 19 11:31:51 2016 +0100
+++ b/src/org/openbravo/costing/PriceDifferenceProcess.java	Mon Feb 15 19:53:12 2016 +0100
@@ -11,7 +11,7 @@
  * under the License.
  * The Original Code is Openbravo ERP.
  * The Initial Developer of the Original Code is Openbravo SLU
- * All portions are Copyright (C) 2014-2015 Openbravo SLU
+ * All portions are Copyright (C) 2014-2016 Openbravo SLU
  * All Rights Reserved.
  * Contributor(s):  ______________________________________.
  *************************************************************************
@@ -231,6 +231,7 @@
     mTrxs.addOrderBy(MaterialTransaction.PROPERTY_TRANSACTIONPROCESSDATE, true);
     ScrollableResults lines = mTrxs.scroll(ScrollMode.FORWARD_ONLY);
 
+    int i = 0;
     try {
       while (lines.next()) {
         MaterialTransaction line = (MaterialTransaction) lines.get(0);
@@ -239,6 +240,12 @@
           count++;
         }
 
+        i++;
+        if (i % 100 == 0) {
+          // Not needed to do flush because it is already done at the end of
+          // calculateTransactionPriceDifference method
+          OBDal.getInstance().getSession().clear();
+        }
       }
     } finally {
       lines.close();