Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0027141Openbravo ERP07. Sales managementpublic2014-07-21 17:012014-08-18 13:34
Reportervmromanos 
Assigned Tovmromanos 
PrioritynormalSeverityminorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS20OS VersionDebian 5.0
Product Version 
Target Version3.0PR14Q4Fixed in Version 
Merge Request Status
Review Assigned Torafaroda
OBNetwork customerNo
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0027141: Minor performance fix in C_ORDERLINETAX_TRG
DescriptionThe C_ORDERLINETAX_TRG trigger contains the following SQL query:

SELECT COUNT(C_TAX_ID) INTO v_Count
FROM C_ORDERTAX
WHERE C_ORDER_ID = :NEW.C_Order_ID
AND C_TAX_ID = :NEW.C_TAX_ID;

Later on there is an IF clause that checks v_Count > 0.

In this scenario where we are not interested in the exact number of records, it's better from a performance perspective to use COUNT(1) instead.
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 the total_time for C_ORDERLINETAX_TRG
6. Apply the proposed solution
7. Verify the total_time for C_ORDERLINETAX_TRG has decreased.
Proposed SolutionUse Count(1)
Additional Information
TagsPerformance
Relationships
blocks defect 0027160 closed eduardo_Argal C_Order_Post poor performance 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2014-07-21 17:01vmromanosNew Issue
2014-07-21 17:01vmromanosAssigned To => vmromanos
2014-07-21 17:01vmromanosOBNetwork customer => No
2014-07-21 17:01vmromanosModules => Core
2014-07-21 17:01vmromanosTriggers an Emergency Pack => No
2014-07-21 17:01vmromanosTag Attached: Performance
2014-07-21 17:01vmromanosStatusnew => scheduled
2014-07-21 17:01vmromanosfix_in_branch => pi
2014-07-22 15:12hgbotCheckin
2014-07-22 15:12hgbotNote Added: 0068807
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/796652fcb518b93f0ffb193a69ffafe6a0d997be [^]
2014-07-23 08:26alostaleRelationship addedblocks 0027160
2014-07-23 10:58mtaalIssue Monitored: mtaal
2014-08-04 16:36dmitry_mezentsevNote Added: 0069075
2014-08-08 18:53hudsonbotCheckin
2014-08-08 18:53hudsonbotNote Added: 0069210
2014-08-08 19:50eduardo_ArgalNote Added: 0069274
2014-08-18 13:34rafarodaReview Assigned To => rafaroda
2014-08-18 13:34rafarodaNote Added: 0069458
2014-08-18 13:34rafarodaStatusresolved => closed

Notes
(0068807)
hgbot   
2014-07-22 15:12   
Repository: erp/devel/pi
Changeset: 796652fcb518b93f0ffb193a69ffafe6a0d997be
Author: Víctor Martínez Romanos <victor.martinez <at> openbravo.com>
Date: Tue Jul 22 10:22:19 2014 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/796652fcb518b93f0ffb193a69ffafe6a0d997be [^]

Fixed bug 27141: Minor performance fix in C_ORDERLINETAX_TRG

---
M src-db/database/model/triggers/C_ORDERLINETAX_TRG.xml
---
(0069075)
dmitry_mezentsev   
2014-08-04 16:36   
The issues is not closed for 13 days now!!!
(0069210)
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
(0069274)
eduardo_Argal   
2014-08-08 19:50   
I would try this query:

select 1 from dual where exists (select 1 FROM C_ORDERTAX
WHERE C_ORDER_ID = :NEW.C_Order_ID
AND C_TAX_ID = :NEW.C_TAX_ID)

this one could be faster, but for an order and a certain tax order tax should hold one single entry. There is just one exception to this rule, which is to enter and adjustment manually.
(0069458)
rafaroda   
2014-08-18 13:34   
Code reviewed + functionally tested with Cash VAT and regular Sales Orders.