|
Notes |
|
|
(0107557)
|
|
markmm82
|
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
|
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
|
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
|
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
|
|
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
|
|
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
|
|
2018-10-25 18:51
|
|
|
|
|
|
|
|
|
|
|