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

View Issue DetailsJump to Notes ] Issue History ] Print ]
ID
0047331
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Modules] French Fiscalminorhave not tried2021-07-05 13:542021-09-15 17:12
ReportervmromanosView Statuspublic 
Assigned Tovmromanos 
PrioritynormalResolutionfixedFixed in Version
StatusclosedFix in branchFixed in SCM revision
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Regression date
Regression introduced by commit
Regression level
Review Assigned To
Regression introduced in release
Summary

0047331: Improve performance FrenchFiscalSecurityManager

DescriptionIn a HV environment the following queries have been detected as slow:

select distinct obcfr_tick0_.Obpos_Applications_ID as col_0_0_ from OBCFR_Ticket obcfr_tick0_ where obcfr_tick0_.AD_Client_ID=$1
 select distinct obcfr_tick0_.Obpos_Applications_ID as col_0_0_ from OBCFR_TicketTax obcfr_tick0_ where obcfr_tick0_.AD_Client_ID=$1
 select distinct obcfr_tick0_.Obpos_Applications_ID as col_0_0_ from OBCFR_TicketReprint obcfr_tick0_ where obcfr_tick0_.AD_Client_ID=$1
 select distinct obcfr_term0_.Obpos_Applications_ID as col_0_0_ from OBCFR_TerminalMonitor obcfr_term0_ where obcfr_term0_.AD_Client_ID=$1
 select distinct obcfr_tick0_.Obpos_Applications_ID as col_0_0_ from OBCFR_Ticket_AggDay obcfr_tick0_ where obcfr_tick0_.AD_Client_ID=$1
 select distinct obcfr_tick0_.Obpos_Applications_ID as col_0_0_ from OBCFR_Ticket_AggMonth obcfr_tick0_ where obcfr_tick0_.AD_Client_ID=$1
 select distinct obcfr_tick0_.Obpos_Applications_ID as col_0_0_ from OBCFR_Ticket_AggYear obcfr_tick0_ where obcfr_tick0_.AD_Client_ID=$1

These queries are related to the FrenchFiscalSecurityManager class
Steps To ReproduceNA
Proposed Solution1. Create an index on [AD_CLIENT_ID, Obpos_Applications_ID] for each of the affected tables and see if there are improvements. Other alternative is to create an index over [Obpos_Applications_ID] and remove the filter over the AD_Client_ID in the queries.

2. Optionally, consider filtering by terminal's isBlockchained flag where applicable
TagsNOR
Attached Files

- Relationships Relation Graph ] Dependency Graph ]

-  Notes
(0131457)
hgbot (developer)
2021-08-31 12:42

Merge Request created: https://gitlab.com/openbravo/ci/modules/org.openbravo.certification.france.dev/-/merge_requests/23 [^]
(0131459)
hgbot (developer)
2021-08-31 13:20

Merge request merged: https://gitlab.com/openbravo/ci/modules/org.openbravo.certification.france.dev/-/merge_requests/23 [^]
(0131460)
hgbot (developer)
2021-08-31 13:20

Directly closing issue as related merge request is already approved.

Repository: https://gitlab.com/openbravo/ci/modules/org.openbravo.certification.france.dev [^]
Changeset: cc55b8f1f4bb8212985b330518684ba19508f114
Author: Víctor Martínez Romanos <victor.martinez@openbravo.com>
Date: 2021-08-31T12:40:17+02:00
URL: https://gitlab.com/openbravo/ci/modules/org.openbravo.certification.france.dev/-/commit/cc55b8f1f4bb8212985b330518684ba19508f114 [^]

Fixed ISSUE-47331: New index to improve FrenchFiscalSecurityManager performance

The FrenchFiscalSecurityManager is run on a daily basis and executes some
queries in OBCFR_* tables, filtering by AD_Client_ID, and creating seq. scans.

The fix adds a new index [AD_CLIENT_ID, Obpos_Applications_ID] over these tables
so the DB can use an "Index Only Scan" instead. In a testing environment with
50k records, the time to execute the same query has improved from 16ms to 0.04ms.

Adding the index is an easy solution that avoids to change the
FrenchFiscalSecurityManager code, and therefore a new certification process.

---
M src-db/database/model/tables/OBCFR_TERMINALMONITOR.xml
M src-db/database/model/tables/OBCFR_TICKET.xml
M src-db/database/model/tables/OBCFR_TICKETREPRINT.xml
M src-db/database/model/tables/OBCFR_TICKETTAX.xml
M src-db/database/model/tables/OBCFR_TICKET_AGGDAY.xml
M src-db/database/model/tables/OBCFR_TICKET_AGGMONTH.xml
M src-db/database/model/tables/OBCFR_TICKET_AGGYEAR.xml
---
(0131830)
hgbot (developer)
2021-09-15 17:12

Directly closing issue as related merge request is already approved.

Repository: https://gitlab.com/openbravo/ci/modules/org.openbravo.certification.france.dev [^]
Changeset: cc55b8f1f4bb8212985b330518684ba19508f114
Author: Víctor Martínez Romanos <victor.martinez@openbravo.com>
Date: 2021-08-31T12:40:17+02:00
URL: https://gitlab.com/openbravo/ci/modules/org.openbravo.certification.france.dev/-/commit/cc55b8f1f4bb8212985b330518684ba19508f114 [^]

Fixed ISSUE-47331: New index to improve FrenchFiscalSecurityManager performance

The FrenchFiscalSecurityManager is run on a daily basis and executes some
queries in OBCFR_* tables, filtering by AD_Client_ID, and creating seq. scans.

The fix adds a new index [AD_CLIENT_ID, Obpos_Applications_ID] over these tables
so the DB can use an "Index Only Scan" instead. In a testing environment with
50k records, the time to execute the same query has improved from 16ms to 0.04ms.

Adding the index is an easy solution that avoids to change the
FrenchFiscalSecurityManager code, and therefore a new certification process.

---
M src-db/database/model/tables/OBCFR_TERMINALMONITOR.xml
M src-db/database/model/tables/OBCFR_TICKET.xml
M src-db/database/model/tables/OBCFR_TICKETREPRINT.xml
M src-db/database/model/tables/OBCFR_TICKETTAX.xml
M src-db/database/model/tables/OBCFR_TICKET_AGGDAY.xml
M src-db/database/model/tables/OBCFR_TICKET_AGGMONTH.xml
M src-db/database/model/tables/OBCFR_TICKET_AGGYEAR.xml
---

- Issue History
Date Modified Username Field Change
2021-07-05 13:54 vmromanos New Issue
2021-07-05 13:54 vmromanos Assigned To => Triage Finance
2021-07-08 12:10 aferraz Description Updated View Revisions
2021-07-14 10:54 rafaroda Tag Attached: NOR
2021-07-14 11:20 shuehner Issue Monitored: shuehner
2021-08-31 11:14 vmromanos Status new => scheduled
2021-08-31 11:14 vmromanos Assigned To Triage Finance => vmromanos
2021-08-31 12:42 hgbot Note Added: 0131457
2021-08-31 13:20 hgbot Note Added: 0131459
2021-08-31 13:20 hgbot Resolution open => fixed
2021-08-31 13:20 hgbot Status scheduled => closed
2021-08-31 13:20 hgbot Note Added: 0131460
2021-09-15 17:12 hgbot Note Added: 0131830


Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker