Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0022086Openbravo ERP07. Sales managementpublic2012-10-24 10:442013-01-24 13:02
Reporteregoitz 
Assigned ToioritzCia 
PriorityurgentSeverityminorReproducibilityalways
StatusnewResolutionopen 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version 
Merge Request Status
Review Assigned To
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0022086: M_INOUT_POST are using an OR in a CURSOR so the query is doing a Full Table Scan which has a very big impact in performance
Descriptionn the M_INOUT_POST we have this code https://code.openbravo.com/erp/devel/pi/file/3ae1a7bf1d80/src-db/database/model/functions/M_INOUT_POST.xml#l277 [^]

FOR Cur_InOut IN
(SELECT *
FROM M_INOUT
WHERE(M_InOut_ID=v_Record_ID
OR(v_Record_ID IS NULL
AND DocAction='CO'))
AND IsActive='Y' FOR UPDATE
)

Even when the v_Record_ID is filled, the query with an OR condition on the input variable is causing a full table scan in M_INOUT.

Proposal:

Doing the same logic in this way:

IF v_Record_ID IS NOT NULL
THEN
OPEN InOutRecCsr FOR
SELECT * FROM M_INOUT
WHERE M_InOut_ID = v_Record_ID and IsActive = 'Y'
FOR UPDATE;
ELSE
OPEN InOutRecCsr FOR
SELECT * FROM M_INOUT
WHERE DocAction = 'CO' and IsActive = 'Y'
FOR UPDATE;
END IF;

LOOP
FETCH InOutRecCsr INTO Cur_InOut;
EXIT WHEN InOutRecCsr%NOTFOUND;

Reduce the cost to less than 1%.
Steps To Reproduce-Execute a m_inout_post on a database with a big number of lines on m_inout table.
*See that takes long to execute the porcess
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
depends on design defect 0022875 new marvintm Translations from Oracle to PostgreSQL is not supported for some cursor declarations. 
Not all the children of this issue are yet resolved or closed.
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2012-10-24 10:44egoitzNew Issue
2012-10-24 10:44egoitzAssigned To => dmiguelez
2012-10-24 10:44egoitzModules => Core
2012-10-24 10:44egoitzResolution time => 1352934000
2012-10-24 10:46dmiguelezAssigned Todmiguelez => jonalegriaesarte
2012-10-24 10:46egoitzAssigned Tojonalegriaesarte => jecharri
2012-10-24 10:46egoitzPriorityurgent => immediate
2012-10-24 12:49jonalegriaesarteTypedesign defect => defect
2012-10-24 12:49jonalegriaesarteTarget Version => 3.0MP17
2012-10-30 15:43hgbotCheckin
2012-10-30 15:43hgbotNote Added: 0053833
2012-10-30 15:43hgbotStatusnew => resolved
2012-10-30 15:43hgbotResolutionopen => fixed
2012-10-30 15:43hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/aff94b13c45dff7f4dc49040fdff5a329ab1f7cf [^]
2012-10-30 18:06jecharriNote Added: 0053855
2012-10-31 13:31hudsonbotCheckin
2012-10-31 13:31hudsonbotNote Added: 0053940
2012-10-31 19:05egoitzNote Added: 0053953
2012-10-31 19:05egoitzStatusresolved => new
2012-10-31 19:05egoitzResolutionfixed => open
2012-10-31 19:08egoitzNote Added: 0053954
2012-10-31 19:32jecharriTarget Version3.0MP17 => 3.0MP18
2012-10-31 19:51hgbotCheckin
2012-10-31 19:51hgbotNote Added: 0053960
2012-11-06 10:47hudsonbotCheckin
2012-11-06 10:47hudsonbotNote Added: 0054027
2012-12-04 11:49jonalegriaesarteTarget Version3.0MP18 => 3.0MP19
2012-12-10 12:49jonalegriaesarteAssigned Tojecharri => ioritzCia
2012-12-10 12:50jonalegriaesarteTriggers an Emergency Pack => No
2012-12-10 12:50jonalegriaesarteNote Added: 0054800
2012-12-10 12:50jonalegriaesartePriorityimmediate => urgent
2012-12-10 12:50jonalegriaesarteSeveritymajor => minor
2013-01-09 11:28jonalegriaesarteTarget Version3.0MP19 => 3.0MP20
2013-01-23 16:55ioritzCiaRelationship addeddepends on 0022875
2013-01-23 16:57ioritzCiaNote Added: 0055774
2013-01-23 16:57ioritzCiaTypedefect => design defect
2013-01-23 23:35shuehnerIssue Monitored: shuehner
2013-01-24 11:30jonalegriaesarteTarget Version3.0MP20 =>
2013-01-24 13:01jonalegriaesarteAssigned ToioritzCia => jecharri
2013-01-24 13:02jonalegriaesarteAssigned Tojecharri => ioritzCia

Notes
(0053833)
hgbot   
2012-10-30 15:43   
Repository: erp/devel/pi
Changeset: aff94b13c45dff7f4dc49040fdff5a329ab1f7cf
Author: Javier Etxarri <javier.echarri <at> openbravo.com>
Date: Mon Oct 29 18:15:18 2012 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/aff94b13c45dff7f4dc49040fdff5a329ab1f7cf [^]

Fixes issue 22086: M_INOUT_POST are using an OR in a CURSOR

---
M src-db/database/model/functions/M_INOUT_POST.xml
---
(0053855)
jecharri   
2012-10-30 18:06   
Test plan:
-verify the m_inout_post file continue working properly.
-create a "goods shipment" and "goods receipt" and complete it
-create a "goods shipment" and "goods receipt" from order and complete it.
-Go to window "Create shipments from orders" and verify that it is working.
-Go to sales order and create a "Warehouse order", complete it and verify that it is working.

Do all the test in postgre and Oracle
(0053940)
hudsonbot   
2012-10-31 13:31   
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/9e773cbcb723 [^]

Maturity status: Test
(0053953)
egoitz   
2012-10-31 19:05   
On postgresql, the shipment and the receipt can not be closed. the error shown is:

el cursor «cur_inoutone» ya está en uso
(0053954)
egoitz   
2012-10-31 19:08   
on oracle the test plan has worked well including also closing the shipments and receipts, but on postgresql fails when closing
(0053960)
hgbot   
2012-10-31 19:51   
Repository: erp/devel/pi
Changeset: 2da392a94c87499f4529f126d595a72971354759
Author: Javier Etxarri <javier.echarri <at> openbravo.com>
Date: Wed Oct 31 19:50:18 2012 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/2da392a94c87499f4529f126d595a72971354759 [^]

Backout issue 22086: M_INOUT_POST are using an OR in a CURSOR so the query
is doing a Full Table Scan which has a very big impact in performance

---
M src-db/database/model/functions/M_INOUT_POST.xml
---
(0054027)
hudsonbot   
2012-11-06 10:47   
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/905762a34c04 [^]

Maturity status: Test
(0054800)
jonalegriaesarte   
2012-12-10 12:50   
It should be included in MP19
(0055774)
ioritzCia   
2013-01-23 16:57   
As told in the related issue 22875 this issue cannot be properly fixed in both PostgreSQL and Oracle until DBSourceManager supports the fix.