details: https://code.openbravo.com/erp/devel/pi/rev/92fe35392dd6 changeset: 29805:92fe35392dd6 user: Atul Gaware <atul.gaware <at> openbravo.com> date: Tue Jul 19 19:51:14 2016 +0200 summary: Fixes issue 33319: Performance problems in Create Shipments from Orders process
Problem was with use of view m_inout_candidate_v in the query. Fix implements the data fetching logic in query itself. details: https://code.openbravo.com/erp/devel/pi/rev/734222ff0bdd changeset: 29806:734222ff0bdd user: Atul Gaware <atul.gaware <at> openbravo.com> date: Tue Jul 19 20:20:24 2016 +0530 summary: Fixes Issue 33320: Performance problems in Create Lines From process Problem was with loading invoices and orders in the respective combos. Changed both queries to improve the performance. diffstat: src/org/openbravo/erpCommon/ad_actionButton/CreateFrom_Shipment_data.xsql | 54 ++++++-- src/org/openbravo/erpCommon/ad_forms/GenerateShipmentsmanual_data.xsql | 61 +++++++-- 2 files changed, 82 insertions(+), 33 deletions(-) diffs (170 lines): diff -r fcfdf220cf0e -r 734222ff0bdd src/org/openbravo/erpCommon/ad_actionButton/CreateFrom_Shipment_data.xsql --- a/src/org/openbravo/erpCommon/ad_actionButton/CreateFrom_Shipment_data.xsql Fri Jul 15 08:49:04 2016 +0200 +++ b/src/org/openbravo/erpCommon/ad_actionButton/CreateFrom_Shipment_data.xsql Tue Jul 19 20:20:24 2016 +0530 @@ -12,7 +12,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) 2001-2014 Openbravo SLU + * All portions are Copyright (C) 2001-2016 Openbravo SLU * All Rights Reserved. * Contributor(s): ______________________________________. ************************************************************************ @@ -140,19 +140,33 @@ <Sql> <![CDATA[ SELECT o.C_ORDER_ID as ID, Ad_Column_Identifier(TO_CHAR('C_Order'), TO_CHAR(o.c_order_id), TO_CHAR(?)) AS NAME - FROM M_INOUT_CANDIDATE_V ic, C_ORDER o JOIN C_DOCTYPE dt ON o.c_doctype_id = dt.c_doctype_id AND dt.isreturn='N' - WHERE o.C_ORDER_ID = ic.C_ORDER_ID - AND ic.AD_Client_ID IN ('1') - AND ic.AD_Org_ID IN ('1') - AND ic.C_BPartner_ID = ? - GROUP BY o.C_ORDER_ID, o.DocumentNo, o.DateOrdered, o.GrandTotal + FROM C_ORDER o + JOIN C_DOCTYPE dt + ON o.c_doctype_id = dt.c_doctype_id + WHERE o.docstatus = 'CO' + AND o.isdelivered = 'N' + AND o.AD_Client_ID IN ('1') + AND o.AD_Org_ID IN ('1') + AND o.C_BPartner_ID = ? + AND dt.isreturn = 'N' + AND dt.docbasetype = 'SOO' + AND dt.docsubtypeso NOT IN ('ON', 'OB') + AND EXISTS ( + SELECT 1 + FROM C_ORDERLINE ol + WHERE ol.c_order_id = o.c_order_id + AND ol.qtyordered <> ol.qtydelivered + AND ol.directship = 'N' + AND ol.m_product_id IS NOT NULL + ) + GROUP BY o.C_ORDER_ID, o.DocumentNo, o.DateOrdered, o.GrandTotal ORDER BY NAME ]]> </Sql> <Field name="rownum" value="count"/> <Parameter name="language"/> - <Parameter name="adUserClient" type="replace" optional="true" after="ic.AD_Client_ID IN (" text="'1'"/> - <Parameter name="adOrgClient" type="replace" optional="true" after="ic.AD_Org_ID IN (" text="'1'"/> + <Parameter name="adUserClient" type="replace" optional="true" after="o.AD_Client_ID IN (" text="'1'"/> + <Parameter name="adOrgClient" type="replace" optional="true" after="o.AD_Org_ID IN (" text="'1'"/> <Parameter name="cBpartnerId"/> </SqlMethod> <SqlMethod name="selectFromPOTrl" type="preparedStatement" return="multiple"> @@ -407,17 +421,27 @@ <Sql> <![CDATA[ SELECT i.C_Invoice_ID AS ID, Ad_Column_Identifier(TO_CHAR('C_Invoice'), TO_CHAR(i.C_Invoice_ID), TO_CHAR(?)) AS NAME - FROM C_INVOICE i JOIN C_DOCTYPE dt ON i.c_doctype_id = dt.c_doctype_id AND dt.isreturn='N' + FROM C_INVOICE i + JOIN C_DOCTYPE dt + ON i.c_doctype_id = dt.c_doctype_id WHERE i.DocStatus IN ('CL','CO') AND i.AD_Client_ID IN ('1') AND i.AD_Org_ID IN ('1') AND i.IsSOTrx='Y' AND i.C_BPartner_ID = ? - AND EXISTS (SELECT 1 - FROM C_INVOICELINE l LEFT JOIN M_INOUTLINE MI ON l.M_INOUTLINE_ID = MI.M_INOUTLINE_ID - WHERE l.C_INVOICE_ID = i.C_INVOICE_ID - GROUP BY l.C_InvoiceLine_ID ,l.QtyInvoiced - HAVING (l.QtyInvoiced-SUM(COALESCE(mi.MOVEMENTQTY,0))) <> 0 ) + AND dt.isreturn = 'N' + AND EXISTS ( + SELECT 1 + FROM C_INVOICELINE il + WHERE il.C_INVOICE_ID = i.C_INVOICE_ID + AND ((il.M_INOUTLINE_ID IS NULL AND il.QtyInvoiced <> 0) + OR (il.M_INOUTLINE_ID IS NOT NULL AND EXISTS ( + SELECT 1 + FROM M_INOUTLINE iol + WHERE iol.M_INOUTLINE_ID = il.M_INOUTLINE_ID + AND (il.QtyInvoiced - iol.MOVEMENTQTY) <> 0 + ))) + ) ORDER BY NAME ]]> </Sql> diff -r fcfdf220cf0e -r 734222ff0bdd src/org/openbravo/erpCommon/ad_forms/GenerateShipmentsmanual_data.xsql --- a/src/org/openbravo/erpCommon/ad_forms/GenerateShipmentsmanual_data.xsql Fri Jul 15 08:49:04 2016 +0200 +++ b/src/org/openbravo/erpCommon/ad_forms/GenerateShipmentsmanual_data.xsql Tue Jul 19 20:20:24 2016 +0530 @@ -12,7 +12,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) 2001-2010 Openbravo SLU + * All portions are Copyright (C) 2001-2016 Openbravo SLU * All Rights Reserved. * Contributor(s): ______________________________________. ************************************************************************ @@ -28,27 +28,52 @@ <SqlMethodComment></SqlMethodComment> <Sql> <![CDATA[ - SELECT C_Order_ID, o.Name as adorgname, COALESCE(dttrl.Name, dt.Name) as cdoctypename, DocumentNo, bp.Name as cbpartnername, DateOrdered, - amountlines, TotalLines - FROM M_InOut_Candidate_v ic, AD_Org o, C_BPartner bp, C_DocType dt left join C_DocType_trl dttrl on dt.C_DocType_ID=dttrl.C_DocType_ID AND dttrl.ad_language=? - WHERE ic.AD_Org_ID=o.AD_Org_ID - AND ic.C_BPartner_ID=bp.C_BPartner_ID - AND ic.C_DocType_ID=dt.C_DocType_ID - AND dt.isreturn='N' - AND ic.ad_client_id in ('1') - AND ic.ad_org_id in ('1') + SELECT o.C_Order_ID, org.Name as adorgname, COALESCE(dttrl.Name, dt.Name) as cdoctypename, o.DocumentNo, bp.Name as cbpartnername, o.DateOrdered, o.totallines as amountlines, + ( + SELECT sum((ol.qtyordered - ol.qtydelivered) * ol.priceactual) + FROM c_orderline ol + WHERE ol.c_order_id = o.c_order_id + AND ol.qtyordered <> ol.qtydelivered + AND ol.directship = 'N' + AND ol.m_product_id IS NOT NULL + ) as TotalLines + FROM C_Order o + JOIN AD_Org org + ON o.AD_Org_ID = org.AD_Org_ID + JOIN C_BPartner bp + ON o.C_BPartner_ID = bp.C_BPartner_ID + JOIN C_DocType dt + ON o.C_DocType_ID = dt.C_DocType_ID + LEFT JOIN C_DocType_trl dttrl + ON dt.C_DocType_ID = dttrl.C_DocType_ID + AND dttrl.ad_language = ? + WHERE o.docstatus = 'CO' + AND o.isdelivered = 'N' + AND dt.isreturn = 'N' + AND dt.docbasetype = 'SOO' + AND dt.docsubtypeso NOT IN ('ON', 'OB') + AND EXISTS ( + SELECT 1 + FROM C_ORDERLINE ol + WHERE ol.c_order_id = o.c_order_id + AND ol.qtyordered <> ol.qtydelivered + AND ol.directship = 'N' + AND ol.m_product_id IS NOT NULL + ) + AND o.ad_client_id IN ('1') + AND o.ad_org_id IN ('1') AND 1=1 - AND 2=2 AND ic.ad_org_id in ('1') - ORDER BY o.Name,bp.Name,DateOrdered + AND 2=2 AND o.ad_org_id IN ('1') + ORDER BY org.Name, bp.Name, DateOrdered ]]></Sql> <Field name="rownum" value="count"/> <Parameter name="language"/> - <Parameter name="adUserClient" type="replace" optional="true" after="AND ic.ad_client_id in (" text="'1'"/> - <Parameter name="adUserOrg" type="replace" optional="true" after="AND ic.ad_org_id in (" text="'1'"/> - <Parameter name="parBPartner" optional="true" after="AND 1=1">AND ic.C_BPartner_ID= ?</Parameter> - <Parameter name="parDateFrom" optional="true" after="AND 1=1"><![CDATA[ AND ic.DateOrdered >= TO_DATE(?)]]></Parameter> - <Parameter name="parDateTo" optional="true" after="AND 1=1"><![CDATA[ AND ic.DateOrdered < TO_DATE(?)]]></Parameter> - <Parameter name="adOrgId" optional="true" type="replace" after="AND 2=2 AND ic.ad_org_id in (" text="'1'"/> + <Parameter name="adUserClient" type="replace" optional="true" after="AND o.ad_client_id IN (" text="'1'"/> + <Parameter name="adUserOrg" type="replace" optional="true" after="AND o.ad_org_id IN (" text="'1'"/> + <Parameter name="parBPartner" optional="true" after="AND 1=1">AND o.C_BPartner_ID = ?</Parameter> + <Parameter name="parDateFrom" optional="true" after="AND 1=1"><![CDATA[ AND o.DateOrdered >= TO_DATE(?)]]></Parameter> + <Parameter name="parDateTo" optional="true" after="AND 1=1"><![CDATA[ AND o.DateOrdered < TO_DATE(?)]]></Parameter> + <Parameter name="adOrgId" optional="true" type="replace" after="AND 2=2 AND o.ad_org_id IN (" text="'1'"/> </SqlMethod> <SqlMethod name="set" type="constant" return="multiple"> <SqlMethodComment></SqlMethodComment> ------------------------------------------------------------------------------ What NetFlow Analyzer can do for you? Monitors network bandwidth and traffic patterns at an interface-level. Reveals which users, apps, and protocols are consuming the most bandwidth. Provides multi-vendor support for NetFlow, J-Flow, sFlow and other flows. Make informed decisions using capacity planning reports.http://sdm.link/zohodev2dev _______________________________________________ Openbravo-commits mailing list Openbravo-commits@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/openbravo-commits
