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

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0035605
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Retail Modules] Multi UPC/EAN for Productmajoralways2017-03-22 18:132017-10-09 11:17
ReporterJONHMView Statuspublic 
Assigned Togorka_gil 
PriorityhighResolutionfixedFixed in VersionRR18Q1
StatusclosedFix in branchFixed in SCM revision5f8ec1c34bdb
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned Tomarvintm
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0035605: Performance issue found caused by trigger obmupc_prod_check_upc_trg

DescriptionIt causes a performance issue when updating a column from M_Product
Steps To Reproduce<ASK FOR THE ENVIRONMENT TO JONHM>

Case 1: with trigger obmupc_prod_check_upc_trg enabled:
update m_product set updated=now() where m_product_id='some_id'
Elapsed time: 4.1 sec

Case 2: with trigger obmupc_prod_check_upc_trg disabled:
update m_product set updated=now() where m_product_id='some_id'
Elapsed time: 12 msec
Proposed SolutionThe solution could be to check if 'upc' field has been changed and execute the query only in that case
TagsNo tags attached.
Attached Files

- Relationships [ Relation Graph ] [ Dependency Graph ]

-  Notes
(0095818)
hgbot (developer)
2017-04-04 09:23

 Repository: erp/pmods/org.openbravo.multiupc
Changeset: 1050a329105bfa4d5979cdf204651ec02ad07a64
Author: Adrián Romero <adrianromero <at> openbravo.com>
Date: Mon Apr 03 19:19:37 2017 +0200
URL: http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/1050a329105bfa4d5979cdf204651ec02ad07a64 [^]

Fixes issue 0035605: Performance issue found caused by trigger obmupc_prod_check_upc_trg

---
M src-db/database/model/triggers/OBMUPC_PROD_CHECK_UPC_TRG.xml
---
(0097105)
marvintm (manager)
2017-06-02 12:46

 Fix seems not to be correct in Oracle, as its causing a mutating trigger error.
(0097106)
hgbot (developer)
2017-06-02 12:51

 Repository: erp/pmods/org.openbravo.multiupc
Changeset: 1292df0e34a8e20e2edb4121d4e141540616b1f7
Author: Miguel de Juana <miguel.dejuana <at> openbravo.com>
Date: Fri Jun 02 12:49:10 2017 +0200
URL: http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/1292df0e34a8e20e2edb4121d4e141540616b1f7 [^]

Related issue 0035605: Performance issue found caused by trigger obmupc_prod_check_upc_trg

- Fix seems not to be correct in Oracle, as its causing a mutating trigger error.
Backed out changeset 1050a329105b

---
M src-db/database/model/triggers/OBMUPC_PROD_CHECK_UPC_TRG.xml
---
(0098124)
hgbot (developer)
2017-07-19 11:16

 Repository: erp/pmods/org.openbravo.multiupc
Changeset: af89dbff980191344982c147b2ee6d377cdbe905
Author: Jorge Garcia <jorge.garcia <at> openbravo.com>
Date: Wed Jul 19 11:15:49 2017 +0200
URL: http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/af89dbff980191344982c147b2ee6d377cdbe905 [^]

Related issue 0035605: Performance issue found caused by trigger obmupc_prod_check_upc_trg

Re-apply code to check the fix in our current CI.

---
M src-db/database/model/triggers/OBMUPC_PROD_CHECK_UPC_TRG.xml
---
(0098137)
hgbot (developer)
2017-07-19 15:33

 Repository: erp/pmods/org.openbravo.multiupc
Changeset: 3524321e78bfbaa0212f803dd38344726f8685e3
Author: Jorge Garcia <jorge.garcia <at> openbravo.com>
Date: Wed Jul 19 15:32:17 2017 +0200
URL: http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/3524321e78bfbaa0212f803dd38344726f8685e3 [^]

Related issue 0035605: Performance issue found caused by trigger obmupc_prod_check_upc_trg

Backed out changeset af89dbff9801

---
M src-db/database/model/triggers/OBMUPC_PROD_CHECK_UPC_TRG.xml
---
(0099779)
hgbot (developer)
2017-10-05 18:20

 Repository: erp/pmods/org.openbravo.multiupc
Changeset: 5f8ec1c34bdbcc5d957110f113548d41c44e74da
Author: Gorka Gil <gorka.gil <at> openbravo.com>
Date: Thu Oct 05 18:03:54 2017 +0200
URL: http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/5f8ec1c34bdbcc5d957110f113548d41c44e74da [^]

Fixed issue 35605: Performance issue found caused by trigger obmupc_prod_check_upc_trg

Remove the trigger and created an event handler

Details:
- The old trigger was slow because do a join of m_product and obmupc_prod_multiupc
to check that there is no duplicates in all the table
- First aproach of the fix was to instead do a statement trigger do a for each row,
and only check if the upc that we are adding exists already
- It has the problem that in Oracle is not possible to do a select in the same table
that we are updating with a trigger for each row, gives error mutating trigger

Other discarded options:
- First idea was to deprecate the upc column in m_product and use only multiupc table,
optionally handle with triggers to have syncronized the upc in m_product with the upc
in multiupc table. This was discarded becasue is a big functional change, and means
change how is working for the past 4 years.
- Second idea was to create a index without duplicates in m_product. In multiupc
already has that index. So the trigger will be very simple to check in the other table
of the one that we are inserting. This was not possible because was needed to create a
unique index that accepts nulls. But this works in oracle only for one column, not for
two or more. Found a workaround:
create unique index EM_OBMUPC_PROD_UPC_IDX on m_product (
   case when upc is null then
     null
   else
     ad_client_id
   end,
   upc
) ;
But it creates db inconsistencies when export from pg.
- Other option is to use expecial triggers of oracle, but are not supported by
dbsourcemaner, also needed to check if exists in postgres.

---
A src/org/openbravo/multiupc/event/NoDuplicateUpcHandler.java
R src-db/database/model/triggers/OBMUPC_CHECK_UPC_TRG.xml
R src-db/database/model/triggers/OBMUPC_PROD_CHECK_UPC_TRG.xml
---

- Issue History
Date Modified Username Field Change
2017-03-22 18:13 JONHM New Issue
2017-03-22 18:13 JONHM Assigned To => Retail
2017-03-22 18:13 JONHM Triggers an Emergency Pack => No
2017-03-22 18:14 JONHM Resolution time => 1491948000
2017-03-23 09:03 Practics Issue Monitored: Practics
2017-03-29 17:01 adrianromero Status new => scheduled
2017-03-29 17:01 adrianromero Assigned To Retail => reinaldoguerra
2017-04-04 09:23 hgbot Checkin
2017-04-04 09:23 hgbot Note Added: 0095818
2017-04-04 09:23 hgbot Status scheduled => resolved
2017-04-04 09:23 hgbot Resolution open => fixed
2017-04-04 09:23 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/1050a329105bfa4d5979cdf204651ec02ad07a64 [^]
2017-04-04 09:23 adrianromero Assigned To reinaldoguerra => adrianromero
2017-04-04 09:24 adrianromero Review Assigned To => marvintm
2017-04-07 11:07 marvintm Status resolved => closed
2017-04-07 11:07 marvintm Fixed in Version => RR17Q2
2017-04-07 11:07 marvintm Fixed in Version RR17Q2 => RR17Q3
2017-06-02 12:46 marvintm Resolution time 1491948000 => 1498946400
2017-06-02 12:46 marvintm Assigned To adrianromero => Retail
2017-06-02 12:46 marvintm Status closed => new
2017-06-02 12:46 marvintm Resolution fixed => open
2017-06-02 12:46 marvintm Fixed in Version RR17Q3 =>
2017-06-02 12:46 marvintm Note Added: 0097105
2017-06-02 12:51 hgbot Checkin
2017-06-02 12:51 hgbot Note Added: 0097106
2017-06-15 11:59 ranjith_qualiantech_com Assigned To Retail => ranjith_qualiantech_com
2017-06-15 11:59 ranjith_qualiantech_com Status new => scheduled
2017-06-19 16:07 ranjith_qualiantech_com Assigned To ranjith_qualiantech_com => Retail
2017-06-19 16:07 ranjith_qualiantech_com Status scheduled => acknowledged
2017-06-28 08:28 marvintm Resolution time 1498946400 => 1499637600
2017-07-06 15:56 marvintm Resolution time 1499637600 => 1500242400
2017-07-13 16:00 migueldejuana Assigned To Retail => migueldejuana
2017-07-13 16:03 migueldejuana Assigned To migueldejuana => Retail
2017-07-13 17:02 migueldejuana Resolution time 1500242400 => 1500847200
2017-07-18 11:11 migueldejuana Resolution time 1500847200 => 1501711200
2017-07-18 16:59 jorge-garcia Assigned To Retail => jorge-garcia
2017-07-19 11:16 hgbot Checkin
2017-07-19 11:16 hgbot Note Added: 0098124
2017-07-19 15:33 hgbot Checkin
2017-07-19 15:33 hgbot Note Added: 0098137
2017-08-04 14:16 marvintm Resolution time 1501711200 => 1502748000
2017-08-21 08:45 marvintm Resolution time 1502748000 => 1503525600
2017-08-24 09:13 marvintm Resolution time 1503525600 => 1504130400
2017-09-01 13:28 marvintm Resolution time 1504130400 => 1505426400
2017-09-01 13:30 Practics Issue End Monitor: Practics
2017-09-15 12:58 marvintm Resolution time 1505426400 => 1506031200
2017-09-22 11:59 marvintm Resolution time 1506031200 => 1506636000
2017-09-26 12:57 marvintm Assigned To jorge-garcia => gorka_gil
2017-09-29 11:24 gorka_gil Status acknowledged => scheduled
2017-10-05 18:20 hgbot Checkin
2017-10-05 18:20 hgbot Note Added: 0099779
2017-10-05 18:20 hgbot Status scheduled => resolved
2017-10-05 18:20 hgbot Resolution open => fixed
2017-10-05 18:20 hgbot Fixed in SCM revision http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/1050a329105bfa4d5979cdf204651ec02ad07a64 [^] => http://code.openbravo.com/erp/pmods/org.openbravo.multiupc/rev/5f8ec1c34bdbcc5d957110f113548d41c44e74da [^]
2017-10-09 11:17 marvintm Status resolved => closed
2017-10-09 11:17 marvintm Fixed in Version => RR18Q1

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker