Project:
View Issue Details[ Jump to Notes ] | [ Issue History ] [ Print ] | |||||||
ID | ||||||||
0034982 | ||||||||
Type | Category | Severity | Reproducibility | Date Submitted | Last Update | |||
feature request | [Retail Modules] StoreServer | minor | have not tried | 2017-01-17 16:22 | 2017-01-27 09:30 | |||
Reporter | AugustoMauch | View Status | public | |||||
Assigned To | AugustoMauch | |||||||
Priority | normal | Resolution | fixed | Fixed in Version | RR17Q2 | |||
Status | closed | Fix in branch | Fixed in SCM revision | 963ec1086228 | ||||
Projection | none | ETA | none | Target Version | ||||
OS | Any | Database | Any | Java version | ||||
OS Version | Database version | Ant version | ||||||
Product Version | SCM revision | |||||||
Review Assigned To | mtaal | |||||||
Regression level | ||||||||
Regression date | ||||||||
Regression introduced in release | ||||||||
Regression introduced by commit | ||||||||
Triggers an Emergency Pack | No | |||||||
Summary | 0034982: Improve the performance of the validate.store.data task | |||||||
Description | The validate.store.data task does not have a good performance when validating stores in high volume environments. The current approach keeps caches of the data that is going to validate, which takes a lot of RAM to maintain and is very time consuming to process. A better approach is to detect the inconsistent data using SQL queries. For instance, if these two queries are used to export two tables: select * from table_A where where_clause_A select * from table_B where where_clause_B Then, if the table_A has a column name table_B_id that references table_B, the following query will return that inconsistent rows of table_A: select table_A_id from table_A as A left join table_B as B on A.table_B_id = B.table_B_id where where_clause_A and where_clause_B and A.table_B_id is not null and B.table_B_id is null This query is an adaptation of the query defined in this image [1], first column of second row. [1] https://i.stack.imgur.com/VQ5XP.png [^] | |||||||
Steps To Reproduce | - | |||||||
Tags | No tags attached. | |||||||
Attached Files | ||||||||
Relationships [ Relation Graph ] [ Dependency Graph ] | |
Notes | |
(0093693) hgbot (developer) 2017-01-18 13:28 |
Repository: erp/pmods/org.openbravo.replication.symmetricds Changeset: 963ec1086228a05164556a757b3ff39cf466c8d9 Author: Augusto Mauch <augusto.mauch <at> openbravo.com> Date: Wed Jan 18 13:23:50 2017 +0100 URL: http://code.openbravo.com/erp/pmods/org.openbravo.replication.symmetricds/rev/963ec1086228a05164556a757b3ff39cf466c8d9 [^] Fixes issue 34982: Improves store data validation by using queries Now, instead of using caching the exported data and checking manually if all the records referenced by the exported rows are also exported, queries are used to find the invalid records. For each foreign key of the table being validated, a query is built joining it with each referenced table, and applying both where clauses. This requires using the @alias@ placeholder when defining the HQL where clauses in the dataset tables. Building a query to join tables that used Full SQL Clauses instead of SQL Where Clauses had bad performance. This was addressed by building materialized views based on the tables that used Full SQL Clauses, and using the materialized views to build the join statements. The ExportedTableCache class is no longer needed, it has been removed. The part of StoreDataSetTableQueryGenerator that contained the validation logic for a given table has been extracted to the new ExportedTableValidator class. --- M src/org/openbravo/replication/symmetricds/export/StoreDataSetTableQueryGenerator.java M src/org/openbravo/replication/symmetricds/validation/DataValidationError.java M src/org/openbravo/replication/symmetricds/validation/SynchronizationStoreDataValidator.java A src/org/openbravo/replication/symmetricds/validation/ExportedTableValidator.java R src/org/openbravo/replication/symmetricds/validation/ExportedTableCache.java --- |
(0093712) AugustoMauch (manager) 2017-01-18 18:10 |
This documents details the performance improvementes done on the store data validation task: https://docs.google.com/document/d/1l1S9GCmQm6Kxunj64GwU2DtdDxP2Oy1CvOTkqfWnih0/edit#heading=h.kat1pvwedklz [^] |
(0093822) hgbot (developer) 2017-01-25 15:48 |
Repository: erp/pmods/org.openbravo.retail.storeserver.synchronization Changeset: bb79f674605b3a1620d66c43fa563e751683a701 Author: Augusto Mauch <augusto.mauch <at> openbravo.com> Date: Wed Jan 18 13:52:53 2017 +0100 URL: http://code.openbravo.com/erp/pmods/org.openbravo.retail.storeserver.synchronization/rev/bb79f674605b3a1620d66c43fa563e751683a701 [^] Related with issue 34982: Updates where clauses to include @alias@ placeholder The new store data validation approach requires defining the @alias@ placeholder in the where clauses defined for the dataset tables. For instance ad_org_id in (@orgsInNaturalTree@) should be replaced with @alias@.ad_org_id in (@orgsInNaturalTree@) --- M src-db/database/sourcedata/AD_DATASET_TABLE.xml --- |
(0093825) hgbot (developer) 2017-01-25 16:30 |
Repository: erp/pmods/org.openbravo.retail.storeserver.synchronization Changeset: c3696ff1abb8754b0455b82f70d55d9c0c9d09e2 Author: Augusto Mauch <augusto.mauch <at> openbravo.com> Date: Wed Jan 25 16:30:23 2017 +0100 URL: http://code.openbravo.com/erp/pmods/org.openbravo.retail.storeserver.synchronization/rev/c3696ff1abb8754b0455b82f70d55d9c0c9d09e2 [^] Related with issue 34982: Removes unneded expression in where clause --- M src-db/database/sourcedata/AD_DATASET_TABLE.xml --- |
(0093865) hgbot (developer) 2017-01-27 09:29 |
Repository: erp/pmods/org.openbravo.replication.symmetricds Changeset: da43d1e63a034a724b635f7f125087c84eb373a0 Author: Martin Taal <martin.taal <at> openbravo.com> Date: Fri Jan 27 09:29:23 2017 +0100 URL: http://code.openbravo.com/erp/pmods/org.openbravo.replication.symmetricds/rev/da43d1e63a034a724b635f7f125087c84eb373a0 [^] Related to issue 34982: Improve the performance of the validate.store.data task Move the whereclause of the right part in the join part --- M src/org/openbravo/replication/symmetricds/validation/ExportedTableValidator.java --- |
(0093866) mtaal (manager) 2017-01-27 09:30 |
Reviewed and tested, last code change reviewed by Augusto |
Issue History | |||
Date Modified | Username | Field | Change |
2017-01-17 16:22 | AugustoMauch | New Issue | |
2017-01-17 16:22 | AugustoMauch | Assigned To | => AugustoMauch |
2017-01-17 16:22 | AugustoMauch | Triggers an Emergency Pack | => No |
2017-01-18 13:28 | hgbot | Checkin | |
2017-01-18 13:28 | hgbot | Note Added: 0093693 | |
2017-01-18 13:28 | hgbot | Status | new => resolved |
2017-01-18 13:28 | hgbot | Resolution | open => fixed |
2017-01-18 13:28 | hgbot | Fixed in SCM revision | => http://code.openbravo.com/erp/pmods/org.openbravo.replication.symmetricds/rev/963ec1086228a05164556a757b3ff39cf466c8d9 [^] |
2017-01-18 17:03 | mtaal | Review Assigned To | => mtaal |
2017-01-18 18:10 | AugustoMauch | Note Added: 0093712 | |
2017-01-25 15:48 | hgbot | Checkin | |
2017-01-25 15:48 | hgbot | Note Added: 0093822 | |
2017-01-25 16:30 | hgbot | Checkin | |
2017-01-25 16:30 | hgbot | Note Added: 0093825 | |
2017-01-27 09:29 | hgbot | Checkin | |
2017-01-27 09:29 | hgbot | Note Added: 0093865 | |
2017-01-27 09:30 | mtaal | Note Added: 0093866 | |
2017-01-27 09:30 | mtaal | Status | resolved => closed |
2017-01-27 09:30 | mtaal | Fixed in Version | => RR17Q2 |
Copyright © 2000 - 2009 MantisBT Group |