0040241: 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
(0109960) hgbot (developer) 2019-02-20 15:26	Repository: erp/backports/3.0PR18Q4.2 Changeset: bf5f0343dba5aa733d8e7c74d9ee9461621a6daf 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.0PR18Q4.2/rev/bf5f0343dba5aa733d8e7c74d9ee9461621a6daf [^] Fixes Issue 0040241: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 ---

(0109961) Sandrahuguet (developer) 2019-02-20 15:27	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.0PR18Q4.2
2019-02-20 15:26	hgbot	Checkin
2019-02-20 15:26	hgbot	Note Added: 0109960
2019-02-20 15:26	hgbot	Status	scheduled => resolved
2019-02-20 15:26	hgbot	Resolution	open => fixed
2019-02-20 15:26	hgbot	Fixed in SCM revision	=> http://code.openbravo.com/erp/backports/3.0PR18Q4.2/rev/bf5f0343dba5aa733d8e7c74d9ee9461621a6daf [^]
2019-02-20 15:27	Sandrahuguet	Review Assigned To	=> Sandrahuguet
2019-02-20 15:27	Sandrahuguet	Note Added: 0109961
2019-02-20 15:27	Sandrahuguet	Status	resolved => closed
2019-02-20 15:27	Sandrahuguet	Fixed in Version	=> 3.0PR18Q4.2