details: https://code.openbravo.com/erp/devel/pi/rev/b13652428b1c
changeset: 33491:b13652428b1c
user: Armaignac <collazoandy4 <at> gmail.com>
date: Fri Feb 09 18:44:25 2018 -0500
summary: Fixes issue 37835: Performance issue in Generate Invoices process
Generate Invoices process takes too long to load in environments with high
amount
of data
The query to get all the business partner with pending sales order to invoice
was
changed using the index created to minimize the time that the process take to
load
diffstat:
src-db/database/model/tables/C_ORDERLINE.xml | 3 +++
src-db/database/sourcedata/AD_VAL_RULE.xml | 23 +++++++++++------------
2 files changed, 14 insertions(+), 12 deletions(-)
diffs (46 lines):
diff -r 5b91e028f16d -r b13652428b1c
src-db/database/model/tables/C_ORDERLINE.xml
--- a/src-db/database/model/tables/C_ORDERLINE.xml Mon Feb 19 18:38:44
2018 +0100
+++ b/src-db/database/model/tables/C_ORDERLINE.xml Fri Feb 09 18:44:25
2018 -0500
@@ -396,6 +396,9 @@
<index-column name="C_ORDER_ID"/>
<index-column name="functionBasedColumn"
functionExpression="OBEQUALS(QTYORDERED,QTYINVOICED)"/>
</index>
+ <index name="C_ORDERLINE_ORDERED_INVOICED" unique="false">
+ <index-column name="functionBasedColumn"
functionExpression="OBEQUALS(QTYORDERED,QTYINVOICED)"/>
+ </index>
<index name="C_ORDERLINE_PRODUCT" unique="false">
<index-column name="M_PRODUCT_ID"/>
</index>
diff -r 5b91e028f16d -r b13652428b1c src-db/database/sourcedata/AD_VAL_RULE.xml
--- a/src-db/database/sourcedata/AD_VAL_RULE.xml Mon Feb 19 18:38:44
2018 +0100
+++ b/src-db/database/sourcedata/AD_VAL_RULE.xml Fri Feb 09 18:44:25
2018 -0500
@@ -311,18 +311,17 @@
<!--135--> <NAME><![CDATA[C_BPartner w SO to invoice]]></NAME>
<!--135--> <DESCRIPTION><![CDATA[BPartners with invoices to be
invoiced]]></DESCRIPTION>
<!--135--> <TYPE><![CDATA[S]]></TYPE>
-<!--135--> <CODE><![CDATA[C_BPartner_ID IN (
-SELECT DISTINCT C_BPartner_ID
-FROM c_order
-WHERE IsSOTrx='Y'
-AND DocStatus NOT IN ('DR','IP')
-AND ((InvoiceRule = 'O'
-AND NOT EXISTS (SELECT 1 FROM C_OrderLine WHERE
C_Order.C_Order_ID=C_OrderLine.C_Order_ID AND QtyOrdered <> QtyDelivered)
-AND EXISTS (SELECT 1 FROM C_OrderLine WHERE
C_Order.C_Order_ID=C_OrderLine.C_Order_ID AND QtyOrdered <> QtyInvoiced))
-OR (InvoiceRule IN ('D', 'S')
-AND EXISTS (SELECT 1 FROM C_OrderLine WHERE
C_Order.C_Order_ID=C_OrderLine.C_Order_ID AND QtyOrdered <> QtyInvoiced AND
QtyDelivered <> QtyInvoiced))
-OR (InvoiceRule = 'I'
-AND EXISTS (SELECT 1 FROM C_OrderLine WHERE
C_Order.C_Order_ID=C_OrderLine.C_Order_ID AND QtyOrdered <>
QtyInvoiced))))]]></CODE>
+<!--135--> <CODE><![CDATA[EXISTS (
+SELECT 1
+FROM c_order o
+JOIN c_orderline ol ON (o.c_order_id = ol.c_order_id)
+WHERE o.issotrx = 'Y'
+AND o.c_bpartner_id = td0.c_bpartner_id
+AND o.docstatus NOT IN ( 'DR', 'IP', 'CL' )
+AND obequals(ol.qtyordered, ol.qtyinvoiced) = 'N'
+AND ( ( o.invoicerule = 'O' AND obequals(ol.qtyordered, ol.qtydelivered) =
'Y')
+OR ( o.invoicerule IN ( 'D', 'S' ) AND obequals(ol.qtydelivered,
ol.qtyinvoiced) = 'N' )
+OR ( o.invoicerule = 'I') ))]]></CODE>
<!--135--> <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID>
<!--135--></AD_VAL_RULE>
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Openbravo-commits mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/openbravo-commits
