Openbravo Issue Tracking System - Retail Modules
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0034982Retail ModulesStoreServerpublic2017-01-17 16:222017-01-27 09:30
ReporterAugustoMauch 
Assigned ToAugustoMauch 
PrioritynormalSeverityminorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in VersionRR17Q2 
Merge Request Status
Review Assigned Tomtaal
OBNetwork customerNo
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0034982: Improve the performance of the validate.store.data task
DescriptionThe 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-
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2017-01-17 16:22AugustoMauchNew Issue
2017-01-17 16:22AugustoMauchAssigned To => AugustoMauch
2017-01-17 16:22AugustoMauchOBNetwork customer => No
2017-01-17 16:22AugustoMauchTriggers an Emergency Pack => No
2017-01-18 13:28hgbotCheckin
2017-01-18 13:28hgbotNote Added: 0093693
2017-01-18 13:28hgbotStatusnew => resolved
2017-01-18 13:28hgbotResolutionopen => fixed
2017-01-18 13:28hgbotFixed in SCM revision => http://code.openbravo.com/erp/pmods/org.openbravo.replication.symmetricds/rev/963ec1086228a05164556a757b3ff39cf466c8d9 [^]
2017-01-18 17:03mtaalReview Assigned To => mtaal
2017-01-18 18:10AugustoMauchNote Added: 0093712
2017-01-25 15:48hgbotCheckin
2017-01-25 15:48hgbotNote Added: 0093822
2017-01-25 16:30hgbotCheckin
2017-01-25 16:30hgbotNote Added: 0093825
2017-01-27 09:29hgbotCheckin
2017-01-27 09:29hgbotNote Added: 0093865
2017-01-27 09:30mtaalNote Added: 0093866
2017-01-27 09:30mtaalStatusresolved => closed
2017-01-27 09:30mtaalFixed in Version => RR17Q2

Notes
(0093693)
hgbot   
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   
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   
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   
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   
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   
2017-01-27 09:30   
Reviewed and tested, last code change reviewed by Augusto