|
|
Test plan 1:
As Openbravo Admin, open the Balance Sheet and PL structure window.
Verify the time spent to open it is in average with other windows in the same instance.
Test plan 2:
Run these two queries and verify no rows are returned:
SELECT O.AD_ORG_ID AS PADRE, Y.C_YEAR_ID AS ID, Y.YEAR || ' (' || C.NAME || ')' AS NAME
FROM C_YEAR Y, C_CALENDAR C, AD_ORG O
WHERE C.C_CALENDAR_ID = Y.C_CALENDAR_ID
and c.ad_client_id = '23C59575B9CF467C9620760EB255B389'
AND EXISTS (
SELECT 1 FROM AD_ORG ORG
WHERE AD_ORG_ISINNATURALTREE(O.AD_ORG_ID, ORG.AD_ORG_ID, '23C59575B9CF467C9620760EB255B389') = 'Y'
AND C.C_CALENDAR_ID = ORG.C_CALENDAR_ID
)
EXCEPT
SELECT O.AD_ORG_ID AS PADRE, Y.C_YEAR_ID AS ID, Y.YEAR || ' (' || C.NAME || ')' AS NAME
FROM C_YEAR Y, C_CALENDAR C, AD_ORG O
WHERE C.C_CALENDAR_ID = Y.C_CALENDAR_ID
AND C.C_CALENDAR_ID IN (
SELECT ORG.C_CALENDAR_ID FROM AD_ORG ORG
WHERE AD_ISORGINCLUDED(O.AD_ORG_ID, ORG.AD_ORG_ID, '23C59575B9CF467C9620760EB255B389') <> -1
AND ORG.C_CALENDAR_ID IS NOT NULL
UNION
SELECT ORG.C_CALENDAR_ID FROM AD_ORG ORG
WHERE AD_ISORGINCLUDED(ORG.AD_ORG_ID,O.AD_ORG_ID, '23C59575B9CF467C9620760EB255B389') <> -1
AND ORG.C_CALENDAR_ID IS NOT NULL
);
SELECT O.AD_ORG_ID AS PADRE, Y.C_YEAR_ID AS ID, Y.YEAR || ' (' || C.NAME || ')' AS NAME
FROM C_YEAR Y, C_CALENDAR C, AD_ORG O
WHERE C.C_CALENDAR_ID = Y.C_CALENDAR_ID
AND C.C_CALENDAR_ID IN (
SELECT ORG.C_CALENDAR_ID FROM AD_ORG ORG
WHERE AD_ISORGINCLUDED(O.AD_ORG_ID, ORG.AD_ORG_ID, '23C59575B9CF467C9620760EB255B389') <> -1
AND ORG.C_CALENDAR_ID IS NOT NULL
UNION
SELECT ORG.C_CALENDAR_ID FROM AD_ORG ORG
WHERE AD_ISORGINCLUDED(ORG.AD_ORG_ID,O.AD_ORG_ID, '23C59575B9CF467C9620760EB255B389') <> -1
AND ORG.C_CALENDAR_ID IS NOT NULL
)
EXCEPT
SELECT O.AD_ORG_ID AS PADRE, Y.C_YEAR_ID AS ID, Y.YEAR || ' (' || C.NAME || ')' AS NAME
FROM C_YEAR Y, C_CALENDAR C, AD_ORG O
WHERE C.C_CALENDAR_ID = Y.C_CALENDAR_ID
and c.ad_client_id = '23C59575B9CF467C9620760EB255B389'
AND EXISTS (
SELECT 1 FROM AD_ORG ORG
WHERE AD_ORG_ISINNATURALTREE(O.AD_ORG_ID, ORG.AD_ORG_ID, '23C59575B9CF467C9620760EB255B389') = 'Y'
AND C.C_CALENDAR_ID = ORG.C_CALENDAR_ID
); |
|
|
(0082979)
|
|
hgbot
|
|
2015-12-24 11:27
|
|
Repository: erp/devel/pi
Changeset: 0827ac596ad2fe52d55b100b6951645a50149eeb
Author: Víctor Martínez Romanos <victor.martinez <at> openbravo.com>
Date: Wed Dec 23 13:42:24 2015 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/0827ac596ad2fe52d55b100b6951645a50149eeb [^]
Fixed bug 31649: Performance problems opening Balance Sheet and PL structure
selectYearsDouble query was consuming most of the time required for launching the window.
Rewritten query to use EXISTS clause, to filter by AD_CLIENT_ID and to call AD_ORG_ISINNATURALTREE() function, which reduces the query cost from 126320.46 to 2527.97, and the time from 20 secs to 0,8 secs
---
M src/org/openbravo/erpCommon/ad_reports/GeneralAccountingReports_data.xsql
---
|
|