details: https://code.openbravo.com/erp/devel/pi/rev/1a7aad006212
changeset: 32378:1a7aad006212
user: Víctor Martínez Romanos <victor.martinez <at> openbravo.com>
date: Wed Jun 28 14:57:06 2017 +0200
summary: Fixed issue 36368: Performance problem in C_Invoice_Post
Changeset pushed on behalf of EAR
AD_ORG_GETCALENDAROWNER function was creating performance problems in queries
related to high volume tables, as it was evaluated per each record.
Solution extracts the function call in a separate query that is executed just
one time.
The result is passed to the where clause using a variable thus improving
performance.
diffstat:
src-db/database/model/functions/C_INVOICE_POST.xml | 10 ++++------
1 files changed, 4 insertions(+), 6 deletions(-)
diffs (41 lines):
diff -r 0b1137c1f062 -r 1a7aad006212
src-db/database/model/functions/C_INVOICE_POST.xml
--- a/src-db/database/model/functions/C_INVOICE_POST.xml Tue Jun 27
23:31:33 2017 +0000
+++ b/src-db/database/model/functions/C_INVOICE_POST.xml Wed Jun 28
14:57:06 2017 +0200
@@ -176,6 +176,7 @@
v_prepaymentamt NUMBER;
v_prepayment_inorders NUMBER;
v_hasTaxes NUMBER;
+ v_calendarId C_CALENDAR.C_CALENDAR_ID%TYPE;
BEGIN
IF (p_PInstance_ID IS NOT NULL) THEN
@@ -344,6 +345,7 @@
/*
* Avoids repeating the same documentno for the same organization tree
within the same fiscal year
*/
+ SELECT C_CALENDAR_ID INTO v_calendarId FROM AD_ORG WHERE AD_ORG_ID =
AD_ORG_GETCALENDAROWNER(v_Org_ID);
SELECT COUNT(*) INTO v_count
FROM (SELECT Y.C_CALENDAR_ID, Y.C_YEAR_ID,
MIN(P.STARTDATE) AS PERIODSTARTDATE, MAX(P.ENDDATE) AS
PERIODENDDATE
@@ -351,9 +353,7 @@
WHERE Y.C_YEAR_ID = P.C_YEAR_ID
AND Y.ISACTIVE = 'Y'
AND P.ISACTIVE = 'Y'
- AND Y.C_CALENDAR_ID = (SELECT O.C_CALENDAR_ID
- FROM AD_ORG O
- WHERE AD_ORG_ID =
AD_ORG_GETCALENDAROWNER(v_Org_ID))
+ AND Y.C_CALENDAR_ID = v_calendarId
GROUP BY Y.C_CALENDAR_ID, Y.C_YEAR_ID) A
WHERE PERIODSTARTDATE <= v_DateInvoiced
AND PERIODENDDATE+1 > v_DateInvoiced ;
@@ -366,9 +366,7 @@
WHERE Y.C_YEAR_ID = P.C_YEAR_ID
AND Y.ISACTIVE = 'Y'
AND P.ISACTIVE = 'Y'
- AND Y.C_CALENDAR_ID = (SELECT O.C_CALENDAR_ID
- FROM AD_ORG O
- WHERE AD_ORG_ID =
AD_ORG_GETCALENDAROWNER(v_Org_ID))
+ AND Y.C_CALENDAR_ID = v_calendarId
GROUP BY Y.C_CALENDAR_ID, Y.C_YEAR_ID) A
WHERE PERIODSTARTDATE <= v_DateInvoiced
AND PERIODENDDATE+1 > v_DateInvoiced ;
------------------------------------------------------------------------------
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
Openbravo-commits@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/openbravo-commits
