Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0039447
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] I. PerformancemajorN/A2018-10-15 12:022018-12-11 20:22
ReporteravicenteView Statuspublic 
Assigned Tomarkmm82 
PriorityhighResolutionfixedFixed in Version3.0PR19Q1
StatusclosedFix in branchFixed in SCM revisionee13830a8384
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned Todmiguelez
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0039447: slow delete action in some backend windows

DescriptionIn a client, it has been detected that the following actions takes more than 10 seconds:
-delete remittance.
-delete POS Terminal.
-delete Sales Invoice.
-detele User.
Steps To ReproduceTest in client's production clone environment https://but-perf-prod-central.services.openbravo.com [^]

User/password in https://docs.google.com/spreadsheets/d/1gK_jnL6Ny7SfYeKACIoBOVpx1dgRf1BMEkokX2WG9J0/edit#gid=138018989 [^]
TagsPerformance
Attached Files

- Relationships [ Relation Graph ] [ Dependency Graph ]
related to defect 0039329 closeddmiguelez Modules Deleting an order on an environment with analytics installed is slower 

-  Notes
(0107557)
markmm82 (developer)
2018-10-24 20:56
edited on: 2018-10-25 10:24

 Tested on client's environment:

1.- Delete an invoice header (c_invoice table).
explain analyze
delete from C_Invoice where C_Invoice_ID='040EA34C2C27425EA7F4CDA698D62D3F';

Before fix:
 Trigger for constraint m_inout_c_invoice: time=67138.480 calls=1
 Execution time: 67165.245 ms

After fix (This index is already on PI but not in client's environment):
CREATE INDEX m_inout_invoice_idx
  ON m_inout
  USING btree
  (c_invoice_id)
  WHERE c_invoice_id IS NOT NULL;
 Trigger for constraint m_inout_c_invoice: time=6.942 calls=1
 Execution time: 17.317 ms

Delete an invoice was improved from 67165.245 ms to 17.317 ms
(0107558)
markmm82 (developer)
2018-10-24 21:00
edited on: 2018-10-25 10:23

 Tested on client's environment:

2.- Delete a Remittance (rem_remittance):
explain analyze
delete from REM_Remittance where REM_Remittance_ID='E407F89665E74DC2B320EFEF43972272';

Before fix:
 Trigger for constraint em_obbutf_remittance: time=109624.657 calls=1
 Execution time: 109628.187 ms

After fix:
CREATE INDEX fin_payment_remittance_id ON fin_payment USING btree (em_obbutf_remittance_id);

 Trigger for constraint em_obbutf_remittance: time=2.525 calls=1
 Execution time: 11.284 ms

select distinct m.name, m.version, m.version_label, m.url, m.javapackage from ad_column c inner join ad_module m on c.ad_module_id=m.ad_module_id where columnname ilike 'em_obbutf_remittance_id';
/*
    name | version | version_label | url | javapackage
------------+---------+---------------+-----+--------------------------
 But Fields | 1.0.4 | | | com.openbravo.but.fields
(1 row)
*/
To be fixed in But Fields module.

Delete a remittance was improved its execution time from 109628.187 ms to 11.284 ms
(0107559)
markmm82 (developer)
2018-10-24 21:02
edited on: 2018-10-25 10:23

 Tested on client's environment:

3.- Delete a POS Terminal:
explain analyze
delete from OBPOS_APPLICATIONS where Obpos_Applications_ID='73E691F4D00E4BB792F11C044D331292';

Before fix:
 Trigger for constraint em_obpos_ad_session_terminal: time=409.495 calls=1
 Trigger for constraint em_oborpre_app_id: time=253.606 calls=1
 Trigger for constraint em_obrlp_posterminal_fk: time=367.256 calls=1
 Execution time: 1415.354 ms

After fix:
CREATE INDEX ad_session_posterminal ON ad_session USING btree(em_obpos_applications_id);
CREATE INDEX obwpl_pickinglist_app_id ON obwpl_pickinglist USING btree (em_oborpre_appcreated_id);
CREATE INDEX oblp_subscription_posterminal ON oblp_subscription USING btree (em_obrlp_posterminal_id);

 Trigger for constraint em_obpos_ad_session_terminal: time=0.162 calls=1
 Trigger for constraint em_oborpre_app_id: time=0.115 calls=1
 Trigger for constraint em_obrlp_posterminal_fk: time=0.391 calls=1
 Execution time: 19.093 ms

select distinct m.name, m.version, m.version_label, m.url, m.javapackage from ad_column c inner join ad_module m on c.ad_module_id=m.ad_module_id where columnname ilike 'em_oborpre_appcreated_id';
           name | version | version_label | url | javapackage
--------------------------+---------+---------------+-----+---------------------------------------
 Retail Order Preparation | 1.0.0 | | | org.openbravo.retail.orderpreparation
(1 row)
To be fixed in Retail Order Preparation module (org.openbravo.retail.orderpreparation)

select distinct m.name, m.version, m.version_label, m.url, m.javapackage from ad_column c inner join ad_module m on c.ad_module_id=m.ad_module_id where columnname ilike 'em_obrlp_posterminal_id';
             name | version | version_label | url | javapackage
------------------------------+---------+---------------+---------------------------+---------------------------------------
 Loyalty Programs for Web POS | 1.0.0 | | http://www.openbravo.com/ [^] | org.openbravo.retail.loyalty.programs
(1 row)
To be fixed in Loyalty Programs for Web POS module (org.openbravo.retail.orderpreparation)

Delete a POS Terminal improved its execution time from 1415.354 ms to 19.093 ms
(0107560)
markmm82 (developer)
2018-10-24 21:05
edited on: 2018-10-25 10:24

 Tested on client's environment:

4.- Delete an user (ad_user):
explain analyze
delete from AD_User where AD_User_ID='DF1979E9D5D44A158ABD85438333CFD2';

Before fix:
    Trigger for constraint ad_user_supervisor: time=17811.419 calls=1
    Trigger for constraint m_product_ad_user: time=10961.616 calls=1
    Trigger for constraint m_inout_ad_user: time=88144.194 calls=1
    Trigger for constraint m_inout_salesrep: time=89451.732 calls=1
    Trigger for constraint c_order_ad_user: time=142089.976 calls=1
    Trigger for constraint c_order_dropship_user: time=138939.513 calls=1
    Trigger for constraint c_order_salesrep: time=152626.130 calls=1
    Trigger for constraint phiedt1_facts_doc_dtl_salesrep: time=291102.261 calls=1
    Trigger for constraint c_invoice_ad_user: time=6633.773 calls=1
    Trigger for constraint c_invoice_salesrep: time=562.628 calls=1
    Trigger for constraint philoyi_pivot_employeecreation: time=2845.420 calls=1
    Trigger for constraint philoyi_pivot_employeesubmis: time=170.986 calls=1
    Trigger for constraint em_obbutf_delivereduser: time=5663.980 calls=1
    Trigger for constraint obwpl_pickinglist_employee: time=3788.049 calls=1
    Trigger for constraint obpos_orderappr_user: time=739.243 calls=1
    Execution time: 986561.020 ms

After fix:
CREATE INDEX ad_user_supervisor ON ad_user USING btree (supervisor_id) WHERE supervisor_id IS NOT NULL;
CREATE INDEX m_product_salesrep ON m_product USING btree (salesrep_id) WHERE salesrep_id IS NOT NULL;
CREATE INDEX m_inout_ad_user ON m_inout USING btree (ad_user_id) WHERE ad_user_id IS NOT NULL;
CREATE INDEX m_inout_salesrep ON m_inout USING btree (salesrep_id) WHERE salesrep_id IS NOT NULL;
CREATE INDEX c_order_ad_user ON c_order USING btree (ad_user_id) WHERE ad_user_id IS NOT NULL;
CREATE INDEX c_order_dropship_user ON c_order USING btree (dropship_user_id) WHERE dropship_user_id IS NOT NULL;
CREATE INDEX c_order_salesrep ON c_order USING btree (salesrep_id) WHERE salesrep_id IS NOT NULL;
CREATE INDEX phiedt1_facts_doc_detail_idx7 ON phiedt1_facts_doc_detail USING btree (salesrep_id, phiedt1_facts_doc_detail_id) WHERE salesrep_id IS NOT NULL;
CREATE INDEX c_invoice_ad_user ON c_invoice USING btree (ad_user_id) WHERE ad_user_id IS NOT NULL;
CREATE INDEX c_invoice_salesrep ON c_invoice USING btree (salesrep_id) WHERE salesrep_id IS NOT NULL;
CREATE INDEX philoyi_pivot_employeesubm ON philoyi_pivot USING btree (philoyi_employeesubmission_id) WHERE philoyi_employeesubmission_id IS NOT NULL;
CREATE INDEX philoyi_pivot_employeecreat ON philoyi_pivot USING btree (philoyi_employeecreation_id) WHERE philoyi_employeecreation_id IS NOT NULL;
CREATE INDEX obwpl_pickinglist_deliveruser ON obwpl_pickinglist USING btree (em_obbutf_delivereduser_id) WHERE em_obbutf_delivereduser_id IS NOT NULL;
CREATE INDEX obwpl_pickinglist_employee ON obwpl_pickinglist USING btree (ad_user_id) WHERE ad_user_id IS NOT NULL;
CREATE INDEX obpos_orderappr_user ON obpos_order_approval USING btree (ad_user_id) WHERE ad_user_id IS NOT NULL;

    Trigger for constraint ad_user_supervisor: time=0.113 calls=1
    Trigger for constraint m_product_ad_user: time=0.174 calls=1
    Trigger for constraint m_inout_ad_user: time=1.963 calls=1
    Trigger for constraint m_inout_salesrep: time=0.484 calls=1
    Trigger for constraint c_order_ad_user: time=0.664 calls=1
    Trigger for constraint c_order_dropship_user: time=0.292 calls=1
    Trigger for constraint c_order_salesrep: time=0.598 calls=1
    Trigger for constraint phiedt1_facts_doc_dtl_salesrep: time=0.824 calls=1
    Trigger for constraint c_invoice_ad_user: time=1.009 calls=1
    Trigger for constraint c_invoice_salesrep: time=0.740 calls=1
    Trigger for constraint philoyi_pivot_employeecreation: time=0.546 calls=1
    Trigger for constraint philoyi_pivot_employeesubmis: time=2.149 calls=1
    Trigger for constraint em_obbutf_delivereduser: time=1.269 calls=1
    Trigger for constraint obwpl_pickinglist_employee: time=1.099 calls=1
    Trigger for constraint obpos_orderappr_user: time=1.032 calls=1
    Execution time: 72.404 ms

select distinct m.name, m.version, m.version_label, m.url, m.javapackage from ad_column c inner join ad_module m on c.ad_module_id=m.ad_module_id where columnname ilike 'phiedt1_facts_doc_detail_id';
     name | version | version_label | url | javapackage
--------------+---------+---------------+-----+-----------------------------------
BUT Editions | 1.0.13 | | | fr.phidias.openbravo.but.editions
(1 row)
INDEX phiedt1_facts_doc_detail_idx7 to be fixed in But Editions module.

select distinct m.name, m.version, m.version_label, m.url, m.javapackage from ad_column c inner join ad_module m on c.ad_module_id=m.ad_module_id where columnname ilike 'philoyi_employeesubmission_id';
         name | version | version_label | url | javapackage
----------------------+---------+---------------+-------------------------+----------------------------------------------
Integration Fidélité | 1.0.0 | | https://www.phidias.fr/ [^] | fr.phidias.openbravo.but.integration.loyalty
(1 row)
INDEX philoyi_pivot_employeesubm to be fixed in Integration Fidélité module.

select distinct m.name, m.version, m.version_label, m.url, m.javapackage from ad_column c inner join ad_module m on c.ad_module_id=m.ad_module_id where columnname ilike 'philoyi_employeecreation_id';
         name | version | version_label | url | javapackage
----------------------+---------+---------------+-------------------------+----------------------------------------------
Integration Fidélité | 1.0.0 | | https://www.phidias.fr/ [^] | fr.phidias.openbravo.but.integration.loyalty
(1 row)
INDEX philoyi_pivot_employeecreat to be fixed in Integration Fidélité module.

Delete a User improved its execution time from 986561.020 ms to 72.404 ms
(0107597)
hgbot (developer)
2018-10-25 15:45

 Repository: erp/devel/pi
Changeset: fd033d0b5bc14e2834669d4cc43856abf7a1e1a4
Author: Mark <markmm82 <at> gmail.com>
Date: Wed Oct 24 20:00:04 2018 -0400
URL: http://code.openbravo.com/erp/devel/pi/rev/fd033d0b5bc14e2834669d4cc43856abf7a1e1a4 [^]

Fixes issue 39447: Added indexes to Improve delete action in some windows.

In environment with high volume of data, when deleting records in some windows,
some constraints related with foreign keys were causing delays when they were checked.
To improve the execution times of these checks were created indexes on the columns
of the affected tables.

---
M src-db/database/model/tables/AD_USER.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/M_INOUT.xml
M src-db/database/model/tables/M_PRODUCT.xml
---
(0107602)
hgbot (developer)
2018-10-25 18:51

 Repository: erp/pmods/org.openbravo.warehouse.pickinglist
Changeset: ee13830a8384835b11184c1f596355a776852e73
Author: Mark <markmm82 <at> gmail.com>
Date: Thu Oct 25 09:40:13 2018 -0400
URL: http://code.openbravo.com/erp/pmods/org.openbravo.warehouse.pickinglist/rev/ee13830a8384835b11184c1f596355a776852e73 [^]

Fixes issue 39447: Added indexes to Improve delete action in some windows.

In environment with high volume of data, when deleting records in some windows,
some constraints related with foreign keys were causing delays when they were checked.

It applies on the particular case of deleting an user. To improve the execution times
of these checks was created an index on the AD_USER_ID column of the OBWPL_PICKINGLIST
table.

---
M src-db/database/model/tables/OBWPL_PICKINGLIST.xml
---
(0107603)
hgbot (developer)
2018-10-25 18:51

 Repository: erp/pmods/org.openbravo.warehouse.pickinglist
Changeset: f3ab66fff525dbe0c7b9eba169a6a09f6d8bd638
Author: Mark <markmm82 <at> gmail.com>
Date: Thu Oct 25 09:41:05 2018 -0400
URL: http://code.openbravo.com/erp/pmods/org.openbravo.warehouse.pickinglist/rev/f3ab66fff525dbe0c7b9eba169a6a09f6d8bd638 [^]

related to issue 39447: Update module version

---
M src-db/database/sourcedata/AD_MODULE.xml
---
(0107604)
dmiguelez (developer)
2018-10-25 18:52

 Code Review + Testing Ok
(0108444)
hudsonbot (developer)
2018-12-11 20:22

 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/470e3cd384c5 [^]
Maturity status: Test

- Issue History
Date Modified Username Field Change
2018-10-15 12:02 avicente New Issue
2018-10-15 12:02 avicente Assigned To => platform
2018-10-15 12:02 avicente Modules => Core
2018-10-15 12:02 avicente Resolution time => 1541372400
2018-10-15 12:02 avicente Triggers an Emergency Pack => No
2018-10-15 14:13 alostale Relationship added related to 0039329
2018-10-15 14:13 alostale Assigned To platform => Triage Finance
2018-10-15 15:46 Sandrahuguet Tag Attached: Performance
2018-10-16 13:36 Sandrahuguet Assigned To Triage Finance => markmm82
2018-10-17 16:16 markmm82 Status new => scheduled
2018-10-24 20:56 markmm82 Note Added: 0107557
2018-10-24 21:00 markmm82 Note Added: 0107558
2018-10-24 21:02 markmm82 Note Added: 0107559
2018-10-24 21:05 markmm82 Note Added: 0107560
2018-10-25 10:23 dmiguelez Description Updated View Revisions
2018-10-25 10:23 dmiguelez Steps to Reproduce Updated View Revisions
2018-10-25 10:23 dmiguelez Note Edited: 0107557 View Revisions
2018-10-25 10:23 dmiguelez Note Edited: 0107558 View Revisions
2018-10-25 10:23 dmiguelez Note Edited: 0107559 View Revisions
2018-10-25 10:24 dmiguelez Note Edited: 0107560 View Revisions
2018-10-25 10:24 dmiguelez Note Edited: 0107557 View Revisions
2018-10-25 15:45 hgbot Checkin
2018-10-25 15:45 hgbot Note Added: 0107597
2018-10-25 15:45 hgbot Status scheduled => resolved
2018-10-25 15:45 hgbot Resolution open => fixed
2018-10-25 15:45 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/fd033d0b5bc14e2834669d4cc43856abf7a1e1a4 [^]
2018-10-25 18:51 hgbot Checkin
2018-10-25 18:51 hgbot Note Added: 0107602
2018-10-25 18:51 hgbot Fixed in SCM revision http://code.openbravo.com/erp/devel/pi/rev/fd033d0b5bc14e2834669d4cc43856abf7a1e1a4 [^] => http://code.openbravo.com/erp/pmods/org.openbravo.warehouse.pickinglist/rev/ee13830a8384835b11184c1f596355a776852e73 [^]
2018-10-25 18:51 hgbot Checkin
2018-10-25 18:51 hgbot Note Added: 0107603
2018-10-25 18:52 dmiguelez Review Assigned To => dmiguelez
2018-10-25 18:52 dmiguelez Note Added: 0107604
2018-10-25 18:52 dmiguelez Status resolved => closed
2018-10-25 18:52 dmiguelez Fixed in Version => 3.0PR19Q1
2018-12-11 20:22 hudsonbot Checkin
2018-12-11 20:22 hudsonbot Note Added: 0108444

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker