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

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0022086
TypeCategorySeverityReproducibilityDate SubmittedLast Update
design defect[Openbravo ERP] 07. Sales managementminoralways2012-10-24 10:442013-01-24 13:02
ReporteregoitzView Statuspublic 
Assigned ToioritzCia 
PriorityurgentResolutionopenFixed in Version
StatusnewFix in branchFixed in SCM revisionaff94b13c45d
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseOracleJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned To
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0022086: 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
TagsNo tags attached.
Attached Files

- Relationships [ Relation Graph ] [ Dependency Graph ]
depends on design defect 0022875 newmarvintm Translations from Oracle to PostgreSQL is not supported for some cursor declarations. 
Not all the children of this issue are yet resolved or closed.

-  Notes
(0053833)
hgbot (developer)
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 (developer)
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 (developer)
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 (developer)
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 (developer)
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 (developer)
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 (developer)
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 (reporter)
2012-12-10 12:50

 It should be included in MP19
(0055774)
ioritzCia (developer)
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.

- Issue History
Date Modified Username Field Change
2012-10-24 10:44 egoitz New Issue
2012-10-24 10:44 egoitz Assigned To => dmiguelez
2012-10-24 10:44 egoitz Modules => Core
2012-10-24 10:44 egoitz Resolution time => 1352934000
2012-10-24 10:46 dmiguelez Assigned To dmiguelez => jonalegriaesarte
2012-10-24 10:46 egoitz Assigned To jonalegriaesarte => jecharri
2012-10-24 10:46 egoitz Priority urgent => immediate
2012-10-24 12:49 jonalegriaesarte Type design defect => defect
2012-10-24 12:49 jonalegriaesarte Target Version => 3.0MP17
2012-10-30 15:43 hgbot Checkin
2012-10-30 15:43 hgbot Note Added: 0053833
2012-10-30 15:43 hgbot Status new => resolved
2012-10-30 15:43 hgbot Resolution open => fixed
2012-10-30 15:43 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/aff94b13c45dff7f4dc49040fdff5a329ab1f7cf [^]
2012-10-30 18:06 jecharri Note Added: 0053855
2012-10-31 13:31 hudsonbot Checkin
2012-10-31 13:31 hudsonbot Note Added: 0053940
2012-10-31 19:05 egoitz Note Added: 0053953
2012-10-31 19:05 egoitz Status resolved => new
2012-10-31 19:05 egoitz Resolution fixed => open
2012-10-31 19:08 egoitz Note Added: 0053954
2012-10-31 19:32 jecharri Target Version 3.0MP17 => 3.0MP18
2012-10-31 19:51 hgbot Checkin
2012-10-31 19:51 hgbot Note Added: 0053960
2012-11-06 10:47 hudsonbot Checkin
2012-11-06 10:47 hudsonbot Note Added: 0054027
2012-12-04 11:49 jonalegriaesarte Target Version 3.0MP18 => 3.0MP19
2012-12-10 12:49 jonalegriaesarte Assigned To jecharri => ioritzCia
2012-12-10 12:50 jonalegriaesarte Triggers an Emergency Pack => No
2012-12-10 12:50 jonalegriaesarte Note Added: 0054800
2012-12-10 12:50 jonalegriaesarte Priority immediate => urgent
2012-12-10 12:50 jonalegriaesarte Severity major => minor
2013-01-09 11:28 jonalegriaesarte Target Version 3.0MP19 => 3.0MP20
2013-01-23 16:55 ioritzCia Relationship added depends on 0022875
2013-01-23 16:57 ioritzCia Note Added: 0055774
2013-01-23 16:57 ioritzCia Type defect => design defect
2013-01-23 23:35 shuehner Issue Monitored: shuehner
2013-01-24 11:30 jonalegriaesarte Target Version 3.0MP20 =>
2013-01-24 13:01 jonalegriaesarte Assigned To ioritzCia => jecharri
2013-01-24 13:02 jonalegriaesarte Assigned To jecharri => ioritzCia

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker