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

View Issue DetailsJump to Notes ] Issue History ] Print ]
ID
0034982
TypeCategorySeverityReproducibilityDate SubmittedLast Update
feature request[Retail Modules] StoreServerminorhave not tried2017-01-17 16:222017-01-27 09:30
ReporterAugustoMauchView Statuspublic 
Assigned ToAugustoMauch 
PrioritynormalResolutionfixedFixed in VersionRR17Q2
StatusclosedFix in branchFixed in SCM revision963ec1086228
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned Tomtaal
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0034982: 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-
TagsNo 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
Powered by Mantis Bugtracker