Project:
| View Issue Details[ Jump to Notes ] | [ Issue History ] [ Print ] | |||||||
| ID | ||||||||
| 0047331 | ||||||||
| Type | Category | Severity | Reproducibility | Date Submitted | Last Update | |||
| defect | [Modules] French Fiscal | minor | have not tried | 2021-07-05 13:54 | 2023-11-15 10:03 | |||
| Reporter | vmromanos | View Status | public | |||||
| Assigned To | vmromanos | |||||||
| Priority | normal | Resolution | fixed | Fixed in Version | ||||
| Status | closed | Fix in branch | Fixed in SCM revision | |||||
| Projection | none | ETA | none | Target Version | ||||
| OS | Any | Database | Any | Java version | ||||
| OS Version | Database version | Ant version | ||||||
| Product Version | SCM revision | |||||||
| Regression date | ||||||||
| Regression introduced by commit | ||||||||
| Regression level | ||||||||
| Review Assigned To | ||||||||
| Regression introduced in release | ||||||||
| Summary | 0047331: Improve performance FrenchFiscalSecurityManager | |||||||
| Description | In 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 Reproduce | NA | |||||||
| Proposed Solution | 1. 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 | |||||||
| Tags | NOR | |||||||
| Attached Files | ||||||||
 Relationships [ Relation Graph ]
[ Dependency Graph ]
|
|||||||||||||||||
|
|||||||||||||||||
 Relationships |
|
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 --- |
|
Notes |
|
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 | |
| 2023-03-20 13:34 | aferraz | Relationship added | has duplicate 0049691 |
| 2023-11-15 10:03 | egoitz | Issue cloned | 0053940 |
| 2023-11-15 10:03 | egoitz | Relationship added | related to 0053940 |
|
Issue History |
| Copyright © 2000 - 2009 MantisBT Group |
 |