0040242: Unpost process can be very slow

Description

In a customer instance with a lot of data in FACT_ACCT, the queries are too slow when filtering by record_id.
Attached query from ResetAccounting (getDocbasetypes method) which makes the process slow.

Also attached explain analyze before and after our proposed solution.

Steps To Reproduce

In customer environment:

1. Go to goods shipment.
2. Unpost a record.
3. Process time can vary, but sometimes can easily take 30-60 mins

Almost all the time consumed by the attached query.

Proposed Solution

CREATE INDEX FACT_ACCT_RECORDID ON FACT_ACCT(recordid);

Tags

No tags attached.

Attached Files

Relationships [ Relation Graph ] [ Dependency Graph ]

blocks

defect

0040208

3.0PR18Q3.3

closed

AtulOpenbravo

Unpost process can be very slow

Notes
(0109957) hgbot (developer) 2019-02-20 15:10	Repository: erp/backports/3.0PR18Q3.5 Changeset: 2c2f81e031ba143d00e29465b706d8be62585379 Author: Atul Gaware <atul.gaware <at> openbravo.com> Date: Wed Feb 20 09:46:02 2019 +0530 URL: http://code.openbravo.com/erp/backports/3.0PR18Q3.5/rev/2c2f81e031ba143d00e29465b706d8be62585379 [^] Fixes Issue 40242:Unpost process can be very slow To use existing index fact_acct_table_record_id for ad_table_id and record_id columns of fact_acct, ad_table_id is added in AND clause of exists subquery from fact_acct table only if record id is sent to getDocbasetypes method. --- M src/org/openbravo/financial/ResetAccounting.java ---

(0109958) Sandrahuguet (viewer) 2019-02-20 15:11	Code review + testing OK

Issue History
Date Modified	Username	Field	Change
2019-02-20 10:11	AtulOpenbravo	Type	defect => backport
2019-02-20 10:11	AtulOpenbravo	Target Version	3.0PR18Q3.3 => 3.0PR18Q3.5
2019-02-20 15:10	hgbot	Checkin
2019-02-20 15:10	hgbot	Note Added: 0109957
2019-02-20 15:10	hgbot	Status	scheduled => resolved
2019-02-20 15:10	hgbot	Resolution	open => fixed
2019-02-20 15:10	hgbot	Fixed in SCM revision	=> http://code.openbravo.com/erp/backports/3.0PR18Q3.5/rev/2c2f81e031ba143d00e29465b706d8be62585379 [^]
2019-02-20 15:11	Sandrahuguet	Review Assigned To	=> Sandrahuguet
2019-02-20 15:11	Sandrahuguet	Note Added: 0109958
2019-02-20 15:11	Sandrahuguet	Status	resolved => closed
2019-02-20 15:11	Sandrahuguet	Fixed in Version	=> 3.0PR18Q3.5