0040240: 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
(0109962) hgbot (developer) 2019-02-20 16:38	Repository: erp/backports/3.0PR19Q1 Changeset: 42d7938062af4385456d59792ed69b6460cd5ed3 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.0PR19Q1/rev/42d7938062af4385456d59792ed69b6460cd5ed3 [^] Fixes Issue 0040240: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 ---

(0109963) Sandrahuguet (developer) 2019-02-20 16:38	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.0PR19Q1
2019-02-20 16:38	hgbot	Checkin
2019-02-20 16:38	hgbot	Note Added: 0109962
2019-02-20 16:38	hgbot	Status	scheduled => resolved
2019-02-20 16:38	hgbot	Resolution	open => fixed
2019-02-20 16:38	hgbot	Fixed in SCM revision	=> http://code.openbravo.com/erp/backports/3.0PR19Q1/rev/42d7938062af4385456d59792ed69b6460cd5ed3 [^]
2019-02-20 16:38	Sandrahuguet	Review Assigned To	=> Sandrahuguet
2019-02-20 16:38	Sandrahuguet	Note Added: 0109963
2019-02-20 16:38	Sandrahuguet	Status	resolved => closed
2019-02-20 16:38	Sandrahuguet	Fixed in Version	=> 3.0PR19Q1