details: https://code.openbravo.com/erp/devel/pi/rev/4f9386d39647 changeset: 15323:4f9386d39647 user: Antonio Moreno <antonio.moreno <at> openbravo.com> date: Thu Feb 02 12:02:43 2012 +0100 summary: Fixed issue 14980. Fixed issue 19625.
details: https://code.openbravo.com/erp/devel/pi/rev/5aa4d0ee877c changeset: 15324:5aa4d0ee877c user: Antonio Moreno <antonio.moreno <at> openbravo.com> date: Thu Feb 02 12:43:36 2012 +0100 summary: Fixed issue 19628. Improved the performance of the import reference data process in two ways: - Added an index to the AD_Ref_loaded table, so that when the number of clients is big, performance doesn't degrade - Refactored the code to use SQL instead of HQL in a specific critical part of the process, to increase performance when the dataset is big. Also, optimized the way information is retrieved from Ad_Ref_Loaded, by caching a full query initially, and then iterating through this cache in successive steps, instead of doing multiple queries for each row to be inserted or updated. diffstat: src-db/database/lib/dbsourcemanager.jar | 0 src-db/database/model/tables/AD_REF_DATA_LOADED.xml | 5 + src/org/openbravo/dal/xml/EntityResolver.java | 198 +++++++++++++++---- 3 files changed, 158 insertions(+), 45 deletions(-) diffs (truncated from 304 to 300 lines): diff -r 4ab1f1545d84 -r 5aa4d0ee877c src-db/database/lib/dbsourcemanager.jar Binary file src-db/database/lib/dbsourcemanager.jar has changed diff -r 4ab1f1545d84 -r 5aa4d0ee877c src-db/database/model/tables/AD_REF_DATA_LOADED.xml --- a/src-db/database/model/tables/AD_REF_DATA_LOADED.xml Thu Feb 02 12:14:56 2012 +0100 +++ b/src-db/database/model/tables/AD_REF_DATA_LOADED.xml Thu Feb 02 12:43:36 2012 +0100 @@ -61,6 +61,11 @@ <foreign-key foreignTable="AD_TABLE" name="AD_REF_DATA_LOADED_AD_TABLE" onDelete="cascade"> <reference local="AD_TABLE_ID" foreign="AD_TABLE_ID"/> </foreign-key> + <index name="AD_REF_DATA_LOADED_INDEX" unique="false"> + <index-column name="AD_CLIENT_ID"/> + <index-column name="AD_TABLE_ID"/> + <index-column name="GENERIC_ID"/> + </index> <check name="AD_REFDATALOAD_ISACTIVE_CHECK"><![CDATA[ISACTIVE IN ('Y', 'N')]]></check> </table> </database> diff -r 4ab1f1545d84 -r 5aa4d0ee877c src/org/openbravo/dal/xml/EntityResolver.java --- a/src/org/openbravo/dal/xml/EntityResolver.java Thu Feb 02 12:14:56 2012 +0100 +++ b/src/org/openbravo/dal/xml/EntityResolver.java Thu Feb 02 12:43:36 2012 +0100 @@ -22,6 +22,8 @@ import static org.openbravo.model.ad.system.Client.PROPERTY_ORGANIZATION; import static org.openbravo.model.common.enterprise.Organization.PROPERTY_CLIENT; +import java.sql.PreparedStatement; +import java.sql.ResultSet; import java.util.ArrayList; import java.util.HashMap; import java.util.List; @@ -29,6 +31,7 @@ import java.util.Set; import org.hibernate.criterion.Restrictions; +import org.openbravo.base.exception.OBException; import org.openbravo.base.model.AccessLevel; import org.openbravo.base.model.Entity; import org.openbravo.base.model.ModelProvider; @@ -48,6 +51,7 @@ import org.openbravo.model.common.enterprise.Organization; import org.openbravo.model.common.plm.AttributeSet; import org.openbravo.model.common.plm.AttributeSetInstance; +import org.openbravo.service.db.DalConnectionProvider; /** * The entity resolver will resolve an entity name and id to a business object. The resolver will @@ -132,7 +136,6 @@ if (result != null) { return result; } - result = searchInstance(entity, id); } @@ -238,62 +241,64 @@ if (al == AccessLevel.SYSTEM) { result = searchSystem(id, entity); } else if (al == AccessLevel.SYSTEM_CLIENT) { + final List<RefDataLoaded> refLoadeds = getRefLoadedsUsingSql(id, entity, null, false); // search client and system - result = searchClient(id, entity); + result = searchClientRefLoaded(id, entity, refLoadeds); if (result == null) { result = searchSystem(id, entity); } } else if (al == AccessLevel.CLIENT) { - result = searchClient(id, entity); + final List<RefDataLoaded> refLoadeds = getRefLoadedsUsingSql(id, entity, null, false); + result = searchClientRefLoaded(id, entity, refLoadeds); } else if (al == AccessLevel.ORGANIZATION) { - result = searchClientOrganization(id, entity); - + final List<RefDataLoaded> refLoadeds = getRefLoadedsUsingSql(id, entity, null, false); + result = searchClientOrganizationRefLoaded(id, entity, refLoadeds); if (result == null) { - // still null, try all the orgs the user has access to - for (String orgId : OBContext.getOBContext().getReadableOrganizations()) { - result = search(id, entity, orgId); - if (result != null) { - break; - } - } + result = searchClientReadableOrgRefLoaded(id, entity, refLoadeds); } } else if (al == AccessLevel.CLIENT_ORGANIZATION) { // search 2 levels - result = searchClientOrganization(id, entity); + final List<RefDataLoaded> refLoadeds = getRefLoadedsUsingSql(id, entity, null, false); + result = searchClientOrganizationRefLoaded(id, entity, refLoadeds); if (result == null) { - result = searchClient(id, entity); + result = searchClientRefLoaded(id, entity, refLoadeds); } if (result == null && (entity.getName().compareTo(AttributeSetInstance.ENTITY_NAME) == 0 || entity.getName() .compareTo(AttributeSet.ENTITY_NAME) == 0)) { - result = searchSystem(id, entity); + result = OBDal.getInstance().get(entity.getName(), id); + } + if (result == null) { + result = searchClientReadableOrgRefLoaded(id, entity, refLoadeds); } + } else if (al == AccessLevel.ALL) { + final List<RefDataLoaded> refLoadeds = getRefLoadedsUsingSql(id, entity, null, false); + // First check if there is one with same client/org + + result = searchClientOrganizationRefLoaded(id, entity, refLoadeds); + // search all three levels from the bottom if (result == null) { - // still null, try all the orgs the user has access to - for (String orgId : OBContext.getOBContext().getReadableOrganizations()) { - result = search(id, entity, orgId); - if (result != null) { - break; - } - } - } - } else if (al == AccessLevel.ALL) { - // search all three levels from the bottom - result = searchClientOrganization(id, entity); - if (result == null) { - result = searchClient(id, entity); + result = searchClientRefLoaded(id, entity, refLoadeds); } if (result == null) { result = searchSystem(id, entity); } + if (result == null) { + result = searchClientReadableOrgRefLoaded(id, entity, refLoadeds); + } + } + return result; + } - if (result == null) { - // still null, try all the orgs the user has access to + private BaseOBObject searchClientReadableOrgRefLoaded(String id, Entity entity, + List<RefDataLoaded> refLoadeds) { + BaseOBObject result = null; + for (RefDataLoaded refLoaded : refLoadeds) { + if (refLoaded.getClientId().equals(client.getId())) { for (String orgId : OBContext.getOBContext().getReadableOrganizations()) { - result = search(id, entity, orgId); - if (result != null) { - break; + if (refLoaded.getOrgId().equals(orgId)) { + result = doSearch(refLoaded.getGenericId(), entity, client.getId(), orgId); } } } @@ -301,6 +306,29 @@ return result; } + private BaseOBObject searchClientOrganizationRefLoaded(String id, Entity entity, + List<RefDataLoaded> refLoadeds) { + BaseOBObject result = null; + for (RefDataLoaded refLoaded : refLoadeds) { + if (refLoaded.getClientId().equals(client.getId()) + && refLoaded.getOrgId().equals(organization.getId())) { + result = doSearch(refLoaded.getGenericId(), entity, client.getId(), organization.getId()); + } + } + return result; + } + + private BaseOBObject searchClientRefLoaded(String id, Entity entity, + List<RefDataLoaded> refLoadeds) { + BaseOBObject result = null; + for (RefDataLoaded refLoaded : refLoadeds) { + if (refLoaded.getClientId().equals(client.getId()) && refLoaded.getOrgId().equals("0")) { + result = doSearch(refLoaded.getGenericId(), entity, client.getId(), "0"); + } + } + return result; + } + public String getOriginalId(BaseOBObject bob) { return objectOriginalIdMapping.get(bob); } @@ -362,11 +390,85 @@ return null; } + private List<RefDataLoaded> getRefLoadedsUsingSql(String id, Entity entity, String orgId, + boolean filterByClient) { + try { + String st = "Select specific_id, generic_id, ad_client_id, ad_org_id from ad_ref_data_loaded where ad_client_id in ('" + + client.getId() + + "', '0') and generic_id='" + + id + + "' and ad_table_id='" + + entity.getTableId() + "'"; + PreparedStatement ps = new DalConnectionProvider(false).getPreparedStatement(st); + ps.execute(); + ResultSet rs = ps.getResultSet(); + List<RefDataLoaded> refDataLoadeds = new ArrayList<EntityResolver.RefDataLoaded>(); + while (rs.next()) { + RefDataLoaded rfl = new RefDataLoaded(); + rfl.setSpecificId(rs.getString(1)); + rfl.setGenericId(rs.getString(1)); + rfl.setClientId(rs.getString(2)); + rfl.setOrgId(rs.getString(3)); + } + return refDataLoadeds; + } catch (Exception e) { + throw new OBException("Error while accessing the ad_ref_data_loaded table", e); + } + } + + private class RefDataLoaded { + private String specificId; + private String genericId; + private String clientId; + private String orgId; + + public String getGenericId() { + return genericId; + } + + public void setGenericId(String genericId) { + this.genericId = genericId; + } + + public String getSpecificId() { + return specificId; + } + + public void setSpecificId(String specificId) { + this.specificId = specificId; + } + + public String getClientId() { + return clientId; + } + + public void setClientId(String clientId) { + this.clientId = clientId; + } + + public String getOrgId() { + return orgId; + } + + public void setOrgId(String orgId) { + this.orgId = orgId; + } + } + + private List<String> getId(String id, Entity entity, String orgId) { + List<ReferenceDataStore> rdls = getRefLoadeds(id, entity, orgId, true); + final List<String> result = new ArrayList<String>(); + for (final ReferenceDataStore rdl : rdls) { + result.add(rdl.getSpecific()); + } + return result; + } + // get the new id which was created in previous imports // note that there is a rare case that when an instance is removed // and then re-imported that it occurs multiple times. - private List<String> getId(String id, Entity entity, String orgId) { - final String[] searchOrgIds = getOrgIds(orgId); + private List<ReferenceDataStore> getRefLoadeds(String id, Entity entity, String orgId, + boolean filterByClient) { OBContext.setAdminMode(); try { final OBCriteria<ReferenceDataStore> rdlCriteria = OBDal.getInstance().createCriteria( @@ -375,19 +477,25 @@ rdlCriteria.setFilterOnReadableOrganization(false); rdlCriteria.setFilterOnReadableClients(false); rdlCriteria.add(Restrictions.eq(ReferenceDataStore.PROPERTY_GENERIC, id)); - rdlCriteria.add(Restrictions.eq( - ReferenceDataStore.PROPERTY_CLIENT + "." + Client.PROPERTY_ID, client.getId())); - rdlCriteria.add(Restrictions.in(ReferenceDataStore.PROPERTY_ORGANIZATION + "." - + Organization.PROPERTY_ID, searchOrgIds)); + if (filterByClient) { + rdlCriteria.add(Restrictions.eq(ReferenceDataStore.PROPERTY_CLIENT + "." + + Client.PROPERTY_ID, client.getId())); + } else { + String[] clients = new String[2]; + clients[0] = client.getId(); + clients[1] = "0"; + rdlCriteria.add(Restrictions.in(ReferenceDataStore.PROPERTY_CLIENT + "." + + Client.PROPERTY_ID, clients)); + } + if (orgId != null) { + final String[] searchOrgIds = getOrgIds(orgId); + rdlCriteria.add(Restrictions.in(ReferenceDataStore.PROPERTY_ORGANIZATION + "." + + Organization.PROPERTY_ID, searchOrgIds)); + } rdlCriteria.add(Restrictions.eq(ReferenceDataStore.PROPERTY_TABLE + "." + Table.PROPERTY_ID, entity.getTableId())); final List<ReferenceDataStore> rdls = rdlCriteria.list(); - - final List<String> result = new ArrayList<String>(); - for (final ReferenceDataStore rdl : rdls) { - result.add(rdl.getSpecific()); - } - return result; ------------------------------------------------------------------------------ Keep Your Developer Skills Current with LearnDevNow! The most comprehensive online learning library for Microsoft developers is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3, Metro Style Apps, more. Free future releases when you subscribe now! http://p.sf.net/sfu/learndevnow-d2d _______________________________________________ Openbravo-commits mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/openbravo-commits
