Project:
| View Issue Details[ Jump to Notes ] | [ Issue History ] [ Print ] | |||||||
| ID | ||||||||
| 0030503 | ||||||||
| Type | Category | Severity | Reproducibility | Date Submitted | Last Update | |||
| defect | [Openbravo ERP] I. Performance | major | always | 2015-08-05 11:19 | 2015-08-25 15:48 | |||
| Reporter | VictorVillar | View Status | public | |||||
| Assigned To | aferraz | |||||||
| Priority | urgent | Resolution | fixed | Fixed in Version | ||||
| Status | closed | Fix in branch | Fixed in SCM revision | 00b72bf1ea14 | ||||
| Projection | none | ETA | none | Target Version | 3.0PR15Q4 | |||
| OS | Any | Database | Any | Java version | ||||
| OS Version | Database version | Ant version | ||||||
| Product Version | 3.0PR15Q2.2 | SCM revision | ||||||
| Merge Request Status | ||||||||
| Review Assigned To | vmromanos | |||||||
| OBNetwork customer | OBPS | |||||||
| Web browser | ||||||||
| Modules | Core | |||||||
| Support ticket | 37623 | |||||||
| Regression level | ||||||||
| Regression date | ||||||||
| Regression introduced in release | ||||||||
| Regression introduced by commit | ||||||||
| Triggers an Emergency Pack | No | |||||||
| Summary | 0030503: Performance problems in the Acct Server process | |||||||
| Description | Performance problems in the Acct Server process with a client with huge amount of data. For instance: more than 500000 registers in the fin_payment table where 250000 register were posted = 'N, 25000 registers in the c_periodcontrol | |||||||
| Steps To Reproduce | we have detected that the following query takes 80 seconds to be executed. select 1 as id from dual where exists ( select 1 from ( SELECT a.Paymentdate as dateacct from FIN_Payment a, c_periodcontrol, c_period WHERE a.ad_client_id = '9718AC9640AE46A995EA3906D8577B57' AND a.ad_org_id in ('78ECECB031B040D8B92F2A386122EB8A', 'A7C52F17532B421A85510826CC533A56', 'A8428F67B4FB4DE39858ED7961664642', '12D699E251A14BACA4AD467C0C7BF740', 'C4E6CDEDB7E846468E49AD857074E05F', 'CA2C1E01F3F8429DB53A4B38B0359584', '9B37FD5895F6423AA7057CC98BEDF8F2', '6B1E650FE6B14B6D8B1077321ACD2E33', '1E92FDE556754B20A5006347DCD774C8', 'D2281E3729FC46E6823447B86F7A93CD', '2E405F05605F4DFE962B645DAA9B1BED', '789F6A29473C40A89286644E541FE27F', '0FCC1AF0592645C787D440672831DD3F', 'BA947E63D9A14A6BBC5FEC0390211F14', 'AFBE100B46334B5689E0BF3DE184B347', 'E009ABF05825407597475082CC94F730', '8EB0E39BAB82401FA2B588C3C7E72E21', '8718431D9D354531BD8738CC6D68DDB3', 'FE508C08DE544254B2F05FC7209F3822', 'AFEC47C91EBD476AB15E57BF1F6CBC13', 'C84FC775D2314020966726D0D6F67E62', 'C3D8332AAFA541C681BAD55F88578201', 'B8EBDAC4EAA3443687CE90017308A7B0', '7C1EA8EDB5604C75940C7B7BB01924F7', '0', 'FE6382F18D3B43F18FC1AB2715A4D581', 'C34408E3763C40BCAD126A1F68254E90', '3F567EF8CABB4D999331B911585D1778', 'E26A832E0FD940F09633DB7FD061C97E', '23ADA885129149DC972870124B59A72E', '782A82AED7984D5A803C0633B7A5B3D2', '35EE4B7E0DB244CA9795E288C5665DCD', '1CC2678CAE0D4D21B87C9533E0BADD2C', 'FEA5E2E616684B86BD35ED5DDF554052', '841DB3F9C1B84DE99C75534C08B21BDE', '1C5F9E2C8AAF4476A59502F3163968E9', '7657FC7FF6B647689B1034847130ADAB', '967469128B5A4F14A0F8F7DEEA50F6BF', 'A4524702F9A9470E84CD478A7F1FA87D', '768E33F2A4544D1E8F1B79CB737F5D73', '16803346D1B441E6BDCC95C5A6262165', 'E0A04220DF9C4CC7974CAA1AB2B4E33B', 'C6600C3D9DAD4A4086B5E7DEED8F43CC', '9425B0E5552A446BA7FA51CEEF790220', 'FC5438951DB04D4B861C546E33D9873A', '53B00F0067B84C15BF25FBA1C57407FB', '51D2661E032D4BB7B1DB52188A594791', '3086199143774727BCF56CBF0FCA4548', '51F9ECC16D1C46B89A17FABF017EC48E', '9795575573F141078C085C1931938718', '202294ACE8504545BF17A1661E3B10E8', '8A5FB32697D04C66921046E5DA04144F', 'CD57526F95934A0B9DAD8531A3E11781', '34D18289081A453B99D090059264EBEF', '48830FF3B878455498AAD7DCB3DB821D', 'D60C026E4C704BDBA1ED6A47868554C0', '93362394887045C39E0804D13654EBB3', '140461D18F474F05852320F228DF1765', '67BF6D38B523421483E3E68BB61B2199', 'F0487725FAB84F4AB61F4E4750FE481E', '09D0E20020B048EC81E730F641896FC5', 'D5ED79BC46AA4C9494400EAE07A73C25', '049154C13FD044178B3DCA9854DA7097', '4BF951FCB1E347EAA021A5B1E8597EC4', 'C34FC9B26126418693723213FB9F110F') AND a.posted = 'N' AND a.processed = 'Y' AND (a.processing = 'N' OR a.processing IS NULL) and c_periodcontrol.c_period_id = c_period.c_period_id AND c_periodcontrol.docbasetype = 'ARR' AND c_periodcontrol.periodstatus = 'O' AND ad_org_getperiodcontrolallow(a.ad_org_id) = c_periodcontrol.ad_org_id AND a.Paymentdate >= c_period.StartDate AND a.Paymentdate < c_period.EndDate + 1 ) b where 1=1 ); We have created an index for the posted column, and the query takes 3 seconds: CREATE INDEX fin_payment_posted ON fin_payment USING btree (posted COLLATE pg_catalog."default"); | |||||||
| Proposed Solution | It would be interesting to think to create an index for posted column for every table that is taken into account in the Acct server | |||||||
| Tags | Performance | |||||||
| Attached Files | ||||||||
 Relationships [ Relation Graph ]
[ Dependency Graph ]
|
|
 Relationships |
|
Notes |
|
|
(0079210) VictorVillar (viewer) 2015-08-06 08:36 |
Besides, in Support we have the client instance where we reproduce the problem. Please contact with Egoitz or Víctor. |
|
(0079637) vmromanos (viewer) 2015-08-24 15:05 |
The proposed solution of adding an index on the Posted column might have collateral effects, like increasing the physical size in the hard disk of the affected tables, slower inserts/updates... However, we think it makes sense to add them, as the performance increase in the posting process is very significant. The fix will include an index for all the tables with a Posted column. This includes unused tables belonging to the old payment flow or tables where the number of records is usually not too high. The reason to do that is to be consistency in all the application, so all the Posted columns (regardless the table) must have an index over it. |
|
(0079638) hgbot (developer) 2015-08-24 15:07 |
Repository: erp/devel/pi Changeset: 00b72bf1ea141026307018796429830a8cf67a1c Author: Alvaro Ferraz <alvaro.ferraz <at> openbravo.com> Date: Fri Aug 21 17:12:24 2015 +0200 URL: http://code.openbravo.com/erp/devel/pi/rev/00b72bf1ea141026307018796429830a8cf67a1c [^] Fixes issue 30503: Performance problems in Acct Server process Added indexes in tables with Posted column --- M src-db/database/model/tables/A_AMORTIZATION.xml M src-db/database/model/tables/C_BANKSTATEMENT.xml M src-db/database/model/tables/C_CASH.xml M src-db/database/model/tables/C_DP_MANAGEMENT.xml M src-db/database/model/tables/C_INVOICE.xml M src-db/database/model/tables/C_ORDER.xml M src-db/database/model/tables/C_PROJECTISSUE.xml M src-db/database/model/tables/C_SETTLEMENT.xml M src-db/database/model/tables/FIN_BANKSTATEMENT.xml M src-db/database/model/tables/FIN_DOUBTFUL_DEBT.xml M src-db/database/model/tables/FIN_PAYMENT.xml M src-db/database/model/tables/FIN_RECONCILIATION.xml M src-db/database/model/tables/GL_JOURNAL.xml M src-db/database/model/tables/M_COSTADJUSTMENT.xml M src-db/database/model/tables/M_INOUT.xml M src-db/database/model/tables/M_INTERNAL_CONSUMPTION.xml M src-db/database/model/tables/M_INVENTORY.xml M src-db/database/model/tables/M_LANDEDCOST.xml M src-db/database/model/tables/M_LC_COST.xml M src-db/database/model/tables/M_MATCHINV.xml M src-db/database/model/tables/M_MATCHPO.xml M src-db/database/model/tables/M_MOVEMENT.xml M src-db/database/model/tables/M_PRODUCTION.xml M src-db/database/model/tables/S_TIMEEXPENSE.xml --- |
|
(0079639) vmromanos (viewer) 2015-08-24 15:09 |
Test plan: Verify tables which generate accounting have an index over the Posted column. Affected tables can be gotten using this SQL (Postgres only): select table_name from information_schema.columns where column_name ilike 'posted' and is_updatable = 'YES' order by table_name; |
|
(0079640) vmromanos (viewer) 2015-08-24 15:09 |
Code review OK |
|
(0079724) hudsonbot (viewer) 2015-08-25 15:48 |
A changeset related to this issue has been promoted main and to the Central Repository, after passing a series of tests. Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/e847ea2dfcc5 [^] Maturity status: Test |
|
Notes |
|
Issue History |
|||
| Date Modified | Username | Field | Change |
| 2015-08-05 11:19 | VictorVillar | New Issue | |
| 2015-08-05 11:19 | VictorVillar | Assigned To | => platform |
| 2015-08-05 11:19 | VictorVillar | OBNetwork customer | => Yes |
| 2015-08-05 11:19 | VictorVillar | Modules | => Core |
| 2015-08-05 11:19 | VictorVillar | Support ticket | => 37623 |
| 2015-08-05 11:19 | VictorVillar | Resolution time | => 1441058400 |
| 2015-08-05 11:19 | VictorVillar | Triggers an Emergency Pack | => No |
| 2015-08-05 12:01 | VictorVillar | Issue Monitored: networkb | |
| 2015-08-05 12:33 | alostale | Assigned To | platform => Triage Finance |
| 2015-08-05 12:33 | alostale | Tag Attached: Performance | |
| 2015-08-06 08:36 | VictorVillar | Note Added: 0079210 | |
| 2015-08-20 11:56 | aferraz | Assigned To | Triage Finance => aferraz |
| 2015-08-20 11:56 | aferraz | Status | new => scheduled |
| 2015-08-24 15:05 | vmromanos | Note Added: 0079637 | |
| 2015-08-24 15:07 | hgbot | Checkin | |
| 2015-08-24 15:07 | hgbot | Note Added: 0079638 | |
| 2015-08-24 15:07 | hgbot | Status | scheduled => resolved |
| 2015-08-24 15:07 | hgbot | Resolution | open => fixed |
| 2015-08-24 15:07 | hgbot | Fixed in SCM revision | => http://code.openbravo.com/erp/devel/pi/rev/00b72bf1ea141026307018796429830a8cf67a1c [^] |
| 2015-08-24 15:09 | vmromanos | Note Added: 0079639 | |
| 2015-08-24 15:09 | vmromanos | Review Assigned To | => vmromanos |
| 2015-08-24 15:09 | vmromanos | Note Added: 0079640 | |
| 2015-08-24 15:09 | vmromanos | Status | resolved => closed |
| 2015-08-25 15:48 | hudsonbot | Checkin | |
| 2015-08-25 15:48 | hudsonbot | Note Added: 0079724 | |
|
Issue History |
| Copyright © 2000 - 2009 MantisBT Group |
 |