# HG changeset patch # User Víctor Martínez Romanos # Date 1454256332 -3600 # Sun Jan 31 17:05:32 2016 +0100 # Node ID 5539754e1f10bf4cf388d14374486663e4036f73 # Parent b6f6e3509b632b71b1bd4398a02ae12f03cc6d0b Fixed bug 31977: Performance problem in ReturnMaterialShipmentPickEdit The "HQL where" and "HQL order by" clauses in RM Shipment Pick and Edit | Pick Edit Lines tab created a non-optimal SQL query with important performance issues in high volume environments, specially in the ones with many order lines and order headers. In particular, the query included unnecessary: * Left join to m_product * Cross join to c_orderline * Cross join to c_order The solution: * Add the c_bpartner_id column to the M_RM_SHIPMENT_PICK_EDIT view and filter by this column in the tab's HQL where clause, so we avoid the need to cross join over c_orderline and c_order tables. * The "HQL order by" orders by lineNo (instead of product), so we avoid the need to left join over m_product and we show a better default order (selected, RM Order No. and Line No.) With these changes, the SQL cost calculated by the planner has been reduced a 88% in our internal testing (from 5100 to 609) diff --git a/src-db/database/model/views/M_RM_SHIPMENT_PICK_EDIT.xml b/src-db/database/model/views/M_RM_SHIPMENT_PICK_EDIT.xml --- a/src-db/database/model/views/M_RM_SHIPMENT_PICK_EDIT.xml +++ b/src-db/database/model/views/M_RM_SHIPMENT_PICK_EDIT.xml @@ -1,4 +1,4 @@ - 0 LEFT JOIN m_locator l ON sd.m_locator_id = l.m_locator_id JOIN m_product p ON p.m_product_id = ol.m_product_id JOIN c_doctype dt ON o.c_doctypetarget_id = dt.c_doctype_id AND dt.isreturn = 'Y' LEFT JOIN (SELECT iol2.m_inoutline_id, iol2.m_inout_id, iol2.movementqty, iol2.c_orderline_id, iol2.ad_org_id, iol2.m_locator_id FROM m_inoutline iol2 JOIN m_inout io2 ON iol2.m_inout_id = io2.m_inout_id AND io2.processed = 'N') iol ON iol.c_orderline_id = ol.c_orderline_id AND iol.m_locator_id = sd.m_locator_id WHERE o.docstatus = 'CO']]> + 0 LEFT JOIN m_locator l ON sd.m_locator_id = l.m_locator_id JOIN m_product p ON p.m_product_id = ol.m_product_id JOIN c_doctype dt ON o.c_doctypetarget_id = dt.c_doctype_id AND dt.isreturn = 'Y' LEFT JOIN (SELECT iol2.m_inoutline_id, iol2.m_inout_id, iol2.movementqty, iol2.c_orderline_id, iol2.ad_org_id, iol2.m_locator_id FROM m_inoutline iol2 JOIN m_inout io2 ON iol2.m_inout_id = io2.m_inout_id AND io2.processed = 'N') iol ON iol.c_orderline_id = ol.c_orderline_id AND iol.m_locator_id = sd.m_locator_id WHERE o.docstatus = 'CO']]> diff --git a/src-db/database/sourcedata/AD_COLUMN.xml b/src-db/database/sourcedata/AD_COLUMN.xml --- a/src-db/database/sourcedata/AD_COLUMN.xml +++ b/src-db/database/sourcedata/AD_COLUMN.xml @@ -251363,6 +251363,44 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + @@ -341074,6 +341112,7 @@ + diff --git a/src-db/database/sourcedata/AD_TAB.xml b/src-db/database/sourcedata/AD_TAB.xml --- a/src-db/database/sourcedata/AD_TAB.xml +++ b/src-db/database/sourcedata/AD_TAB.xml @@ -17355,9 +17355,9 @@ 0 AND (e.storageBin is null OR ad_org_isinnaturaltree(@MaterialMgmtShipmentInOut.organization@, (select organization.id from Locator a where a.id = e.storageBin.id), @MaterialMgmtShipmentInOut.client@) = 'Y')]]> - +