Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0035296Openbravo ERPY. DBSourceManagerpublic2017-02-17 12:072017-03-15 20:19
Reporteralostale 
Assigned Toalostale 
PrioritynormalSeverityminorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version3.0PR17Q2 
Merge Request Status
Review Assigned Tocaristu
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0035296: pl functions with out and defaults params can be slow to standardize
DescriptionIf there are PL functions with many parameters (>25) some of having default values and some other begin output parameters and there is another PL function that invokes it not passing all of the parameters to make use of the defaults, export.database take ages to complete.

The problem is caused because dbsm translates function Oracle function invocation for functions with out params in "select * into outp from function(inp)", this transformation is done with regexps, with many parameters applying the regexp in case it does not match is really slow.

See examples: https://gist.github.com/alostale/ddfd1ca890e3dbae3c3558fc2fa1bfc6 [^]
Steps To Reproduce1. patch pi (rev aff21cc5e768) with attached diff
2. update.database
3. export.database -> it can take hours to complete function standardization part
Proposed SolutionBecause the cases where it is slow is actually the ones that will not match, it is possible to know in advance these case by just counting the number of parameters the lines has, so in these cases the expression does not require to be applied.
Additional Information
TagsPerformance
Relationships
related to defect 0035284 closed alostale update.database unnecessarily standardizes DB PL code 
blocks defect 0035297 closed alostale PL functions with optional constant params are not correctly standardized 
Attached Filesdiff m_get_stock_param1.diff (977) 2017-02-17 12:08
https://issues.openbravo.com/file_download.php?file_id=10460&type=bug
Issue History
Date ModifiedUsernameFieldChange
2017-02-17 12:07alostaleNew Issue
2017-02-17 12:07alostaleAssigned To => platform
2017-02-17 12:07alostaleModules => Core
2017-02-17 12:07alostaleTriggers an Emergency Pack => No
2017-02-17 12:08alostaleFile Added: m_get_stock_param1.diff
2017-02-17 12:08alostaleAssigned Toplatform => alostale
2017-02-17 12:08alostaleStatusnew => acknowledged
2017-02-17 12:09alostaleRelationship addedrelated to 0035284
2017-02-17 12:09alostaleTag Attached: Performance
2017-02-17 12:14alostaleRelationship addedblocks 0035297
2017-02-20 14:49hgbotCheckin
2017-02-20 14:49hgbotNote Added: 0094469
2017-02-20 14:49hgbotCheckin
2017-02-20 14:49hgbotNote Added: 0094470
2017-02-20 14:49hgbotStatusacknowledged => resolved
2017-02-20 14:49hgbotResolutionopen => fixed
2017-02-20 14:49hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/dbsm-main/rev/05dcb0005fc0934fc4bfaefedf914bf8a960fb33 [^]
2017-02-20 14:49hgbotCheckin
2017-02-20 14:49hgbotNote Added: 0094473
2017-02-20 14:49hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/devel/dbsm-main/rev/05dcb0005fc0934fc4bfaefedf914bf8a960fb33 [^] => http://code.openbravo.com/erp/devel/pi/rev/490d93599594dac190b9711b1a43054ad51cf025 [^]
2017-02-20 14:50alostaleReview Assigned To => caristu
2017-02-28 09:28caristuNote Added: 0094654
2017-02-28 09:28caristuStatusresolved => closed
2017-02-28 09:28caristuFixed in Version => 3.0PR17Q2
2017-03-15 20:19hudsonbotCheckin
2017-03-15 20:19hudsonbotNote Added: 0095197

Notes
(0094469)
hgbot   
2017-02-20 14:49   
Repository: erp/devel/dbsm-main
Changeset: dcd0a1c5dc184fb1d43658f0dd1acdbb4fb26773
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Fri Feb 17 12:23:04 2017 +0100
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/dcd0a1c5dc184fb1d43658f0dd1acdbb4fb26773 [^]

related to issue 35296: added missing license and reformat

---
M src/org/apache/ddlutils/platform/postgresql/PostgrePLSQLStandarization.java
---
(0094470)
hgbot   
2017-02-20 14:49   
Repository: erp/devel/dbsm-main
Changeset: 05dcb0005fc0934fc4bfaefedf914bf8a960fb33
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Fri Feb 17 12:26:20 2017 +0100
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/05dcb0005fc0934fc4bfaefedf914bf8a960fb33 [^]

fixed bug 35296: pl with out and defaults params is slow to standardize

  Applying the regexp to do these transformations is very slow in case of many
  paramters and non matching pattern.

  Fixed by preventing it to be applied in case the number of parameters in the
  evaluated string doesn't match with the number defined in the pattern.

---
M src/org/apache/ddlutils/platform/postgresql/PostgrePLSQLStandarization.java
A src/org/apache/ddlutils/translation/ReplaceOutFunctionParams.java
---
(0094473)
hgbot   
2017-02-20 14:49   
Repository: erp/devel/pi
Changeset: 490d93599594dac190b9711b1a43054ad51cf025
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Fri Feb 17 12:34:43 2017 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/490d93599594dac190b9711b1a43054ad51cf025 [^]

fixed bug 35284, fixed bug 35297, fixed bug 35296

  dbsourcemanager.jar including fixes for these 3 issues.

---
M src-db/database/lib/dbsourcemanager.jar
---
(0094654)
caristu   
2017-02-28 09:28   
Code review + tested in pi@1e7495658970
(0095197)
hudsonbot   
2017-03-15 20:19   
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/54e102bef53e [^]
Maturity status: Test