Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0027140Openbravo ERP07. Sales managementpublic2014-07-21 16:532014-08-08 19:41
Reportervmromanos 
Assigned Tovmromanos 
PrioritynormalSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS20OS VersionDebian 5.0
Product Version 
Target Version3.0PR14Q4Fixed in Version 
Merge Request Status
Review Assigned Toeduardo_Argal
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0027140: Useless usage of hex_to_int() in C_ORDER_POST affects performance
DescriptionThe hex_to_int() database function is used in several places throughout the C_Order_Post process. This function transforms from hexadecimal to integer, which is a time demanding process and in the particular case of C_Order_Post is totally useless.

There are two similar queries used for getting the quantity pending to invoice and the quantity pending to deliver where this function is used. Example:

SELECT SUM(QtyOrdered*hex_to_int(C_OrderLine_ID)) SUM(QtyDelivered*hex_to_int(C_OrderLine_ID))
INTO ToDeliver

The transformation there has no sense and creates performance problems.
Steps To ReproduceUsing PG:
-Enable function statistics:
  -In postgresql.conf set track_functions = all
  -/etc/init.d/postgresql reload

1. Create a sales order with a great number of lines (for example 400).
2. Reset pg statistics: select pg_stat_reset();
3. Book the order
4. Check function calls: select funcname, calls, total_time, self_time from pg_stat_user_functions order by self_time desc
5. Verify 'hex_to_int' is invoked more than 2000 times with a total_time greater than 600
Proposed Solution
Additional Information
TagsPerformance
Relationships
blocks defect 0027160 closed eduardo_Argal C_Order_Post poor performance 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2014-07-21 16:53vmromanosNew Issue
2014-07-21 16:53vmromanosAssigned To => vmromanos
2014-07-21 16:53vmromanosModules => Core
2014-07-21 16:53vmromanosTriggers an Emergency Pack => No
2014-07-21 16:54vmromanosTag Attached: Performance
2014-07-21 16:54vmromanosStatusnew => scheduled
2014-07-21 16:54vmromanosfix_in_branch => pi
2014-07-21 17:14shuehnerNote Added: 0068769
2014-07-21 17:15shuehnerIssue Monitored: shuehner
2014-07-22 15:12hgbotCheckin
2014-07-22 15:12hgbotNote Added: 0068806
2014-07-22 15:12hgbotStatusscheduled => resolved
2014-07-22 15:12hgbotResolutionopen => fixed
2014-07-22 15:12hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/a2ce02e621d9152f77c21133daa28e53246f7e45 [^]
2014-07-23 08:26alostaleRelationship addedblocks 0027160
2014-07-23 10:58mtaalIssue Monitored: mtaal
2014-07-29 12:03shuehnerNote Added: 0068956
2014-08-04 09:51hgbotCheckin
2014-08-04 09:51hgbotNote Added: 0069059
2014-08-04 09:52vmromanosNote Added: 0069060
2014-08-04 09:52vmromanosStatusresolved => new
2014-08-04 09:52vmromanosResolutionfixed => open
2014-08-04 11:02vmromanosStatusnew => scheduled
2014-08-04 14:49hgbotCheckin
2014-08-04 14:49hgbotNote Added: 0069069
2014-08-04 14:49hgbotStatusscheduled => resolved
2014-08-04 14:49hgbotResolutionopen => fixed
2014-08-04 14:49hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/devel/pi/rev/a2ce02e621d9152f77c21133daa28e53246f7e45 [^] => http://code.openbravo.com/erp/devel/pi/rev/4882d76b8d6a243d3c13c02c1664de289e52220d [^]
2014-08-07 18:37hgbotCheckin
2014-08-07 18:37hgbotNote Added: 0069117
2014-08-08 18:53hudsonbotCheckin
2014-08-08 18:53hudsonbotNote Added: 0069209
2014-08-08 18:54hudsonbotCheckin
2014-08-08 18:54hudsonbotNote Added: 0069257
2014-08-08 18:54hudsonbotCheckin
2014-08-08 18:54hudsonbotNote Added: 0069260
2014-08-08 18:54hudsonbotCheckin
2014-08-08 18:54hudsonbotNote Added: 0069270
2014-08-08 19:41eduardo_ArgalReview Assigned To => eduardo_Argal
2014-08-08 19:41eduardo_ArgalNote Added: 0069273
2014-08-08 19:41eduardo_ArgalStatusresolved => closed

Notes
(0068769)
shuehner   
2014-07-21 17:14   
That function should just be deleted, as it was created when migrating to uuid's as a kind of workaround. But i think logic should be able to easily refactorable to not need it at all.
(0068806)
hgbot   
2014-07-22 15:12   
Repository: erp/devel/pi
Changeset: a2ce02e621d9152f77c21133daa28e53246f7e45
Author: Víctor Martínez Romanos <victor.martinez <at> openbravo.com>
Date: Tue Jul 22 10:21:40 2014 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/a2ce02e621d9152f77c21133daa28e53246f7e45 [^]

Fixed bug 27140: Useless usage of hex_to_int()

---
M src-db/database/model/functions/C_ORDER_POST1.xml
---
(0068956)
shuehner   
2014-07-29 12:03   
Note: for reviewer please verify that the changed code does really do the same functional check.
On a quick look it does not. Before it did verify if the two quantities are same per line.
Now just in global
So getting diff of +1 in one line and -1 in another line before was captured. Now it is not.
Needs to be verified if that can happen in practice.

Also if that is correct that commit should have explained that it does intentionally change technical behavior but that it does matter functionally.
(0069059)
hgbot   
2014-08-04 09:51   
Repository: erp/devel/pi
Changeset: 7507a81f4e636dc2a3a0520312049ac300d08950
Author: Víctor Martínez Romanos <victor.martinez <at> openbravo.com>
Date: Mon Aug 04 09:51:01 2014 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/7507a81f4e636dc2a3a0520312049ac300d08950 [^]

Issue 27140: Backout previous fix to study its consequences

---
M src-db/database/model/functions/C_ORDER_POST1.xml
---
(0069060)
vmromanos   
2014-08-04 09:52   
Stefan, I think you are absolutely right.

I'm reverting the fix and I will study it deeply.
(0069069)
hgbot   
2014-08-04 14:49   
Repository: erp/devel/pi
Changeset: 4882d76b8d6a243d3c13c02c1664de289e52220d
Author: Víctor Martínez Romanos <victor.martinez <at> openbravo.com>
Date: Mon Aug 04 14:47:58 2014 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/4882d76b8d6a243d3c13c02c1664de289e52220d [^]

Fixed bug 27140: Useless usage of hex_to_int()

---
M src-db/database/model/functions/C_ORDER_POST1.xml
---
(0069117)
hgbot   
2014-08-07 18:37   
Repository: erp/devel/pi
Changeset: 19e19b829b48c51c1d9a26f66feeb4a7f2a684d1
Author: Víctor Martínez Romanos <victor.martinez <at> openbravo.com>
Date: Thu Aug 07 18:35:47 2014 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/19e19b829b48c51c1d9a26f66feeb4a7f2a684d1 [^]

Issue 27140: fixed execution in Oracle

The previous SQL queries raised a ORA-01403: no data found exception when no records were found.
They have been rewritten to use count(*) instead, which either returns 0 if no records are found or 1 when it finds any record.

---
M src-db/database/model/functions/C_ORDER_POST1.xml
---
(0069209)
hudsonbot   
2014-08-08 18:53   
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/4450016dee64 [^]
Maturity status: Test
(0069257)
hudsonbot   
2014-08-08 18:54   
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/4450016dee64 [^]
Maturity status: Test
(0069260)
hudsonbot   
2014-08-08 18:54   
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/4450016dee64 [^]
Maturity status: Test
(0069270)
hudsonbot   
2014-08-08 18:54   
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/4450016dee64 [^]
Maturity status: Test
(0069273)
eduardo_Argal   
2014-08-08 19:41   
Code Reviewed and tested