Uploaded image for project: 'Syncope'
  1. Syncope
  2. SYNCOPE-1519

SchemaDataBinderImpl#update optimization

    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Resolved
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 2.0.12
    • Fix Version/s: 2.1.5, 3.0.0
    • Component/s: core
    • Labels:
      None

      Description

      When org.apache.syncope.core.provisioning.java.data.SchemaDataBinderImpl#update(org.apache.syncope.common.lib.to.PlainSchemaTO, org.apache.syncope.core.persistence.api.entity.PlainSchema) is invoked it checks whether plain schema has any attrs, by invoking the method org.apache.syncope.core.persistence.api.dao.PlainSchemaDAO#findAttrs which generates huge SQL:

      syncope 2.0.12-related
      
      SELECT t0.id, t2.id, t1.id, t3.id, t1.cipherAlgorithm, t1.conversionPattern, t1.enumerationKeys, t1.enumerationValues, t1.mandatoryCondition, t1.mimeType, t1.multivalue, t1.readonly, t1.secretKey, t1.type, t1.uniqueConstraint, t1.validatorClass, t4.id, t5.id, t5.creationDate, t5.creator, t5.lastChangeDate, t5.lastModifier, t6.id, t7.id, t7.description, t7.maxAuthenticationAttempts, t7.propagateSuspension, t6.name, t8.id, t8.ACCOUNTPOLICY_ID, t8.name, t8.PASSWORDPOLICY_ID, t9.id, t9.description, t9.allowNullPassword, t9.historyLength, t5.status, t5.workflowId, t5.changePwdDate, t5.cipherAlgorithm, t5.failedLogins, t5.lastLoginDate, t5.lastRecertification, t5.lastRecertificator, t5.mustChangePassword, t5.password, t5.securityAnswer, t10.id, t10.content, t5.suspended, t5.token, t5.tokenExpireTime, t5.username, t11.id, t11.creationDate, t11.creator, t11.lastChangeDate, t11.lastModifier, t11.REALM_ID, t11.status, t11.workflowId, t12.id, t12.creationDate, t12.creator, t12.lastChangeDate, t12.lastModifier, t12.REALM_ID, t12.status, t12.workflowId, t12.name, t12.USEROWNER_ID, t11.name, t13.id, t13.fiql, t14.id, t14.creationDate, t14.creator, t14.lastChangeDate, t14.lastModifier, t14.REALM_ID, t14.status, t14.workflowId, t14.changePwdDate, t14.cipherAlgorithm, t14.failedLogins, t14.lastLoginDate, t14.lastRecertification, t14.lastRecertificator, t14.mustChangePassword, t14.password, t14.securityAnswer, t14.SECURITYQUESTION_ID, t14.suspended, t14.token, t14.tokenExpireTime, t14.username, t15.id, t15.creationDate, t15.creator, t15.lastChangeDate, t15.lastModifier, t15.REALM_ID, t15.status, t15.workflowId, t15.changePwdDate, t15.cipherAlgorithm, t15.failedLogins, t15.lastLoginDate, t15.lastRecertification, t15.lastRecertificator, t15.mustChangePassword, t15.password, t15.securityAnswer, t15.SECURITYQUESTION_ID, t15.suspended, t15.token, t15.tokenExpireTime, t15.username, t16.id, t16.binaryValue, t16.booleanValue, t16.dateValue, t16.doubleValue, t16.longValue, t16.stringValue, t18.id, t17.id, t17.ANYTYPECLASS_ID, t17.cipherAlgorithm, t17.conversionPattern, t17.enumerationKeys, t17.enumerationValues, t17.mandatoryCondition, t17.mimeType, t17.multivalue, t17.readonly, t17.secretKey, t17.type, t17.uniqueConstraint, t17.validatorClass FROM UPlainAttr t0 LEFT OUTER JOIN PlainSchema t1 ON t0.schema_id = t1.id LEFT OUTER JOIN UMembership t4 ON t0.MEMBERSHIP_ID = t4.id LEFT OUTER JOIN SyncopeUser t15 ON t0.OWNER_ID = t15.id LEFT OUTER JOIN UPlainAttrUniqueValue t16 ON t0.id = t16.ATTRIBUTE_ID LEFT OUTER JOIN AnyTypeClass t3 ON t1.ANYTYPECLASS_ID = t3.id LEFT OUTER JOIN SyncopeSchema t2 ON t1.id = t2.id LEFT OUTER JOIN SyncopeUser t5 ON t4.user_id = t5.id LEFT OUTER JOIN SyncopeGroup t11 ON t4.group_id = t11.id LEFT OUTER JOIN PlainSchema t17 ON t16.schema_id = t17.id LEFT OUTER JOIN Realm t6 ON t5.REALM_ID = t6.id LEFT OUTER JOIN SecurityQuestion t10 ON t5.SECURITYQUESTION_ID = t10.id LEFT OUTER JOIN SyncopeGroup t12 ON t11.GROUPOWNER_ID = t12.id LEFT OUTER JOIN UDynGroupMembership t13 ON t11.id = t13.GROUP_ID LEFT OUTER JOIN SyncopeUser t14 ON t11.USEROWNER_ID = t14.id LEFT OUTER JOIN SyncopeSchema t18 ON t17.id = t18.id LEFT OUTER JOIN AccountPolicy t7 ON t6.ACCOUNTPOLICY_ID = t7.id LEFT OUTER JOIN Realm t8 ON t6.PARENT_ID = t8.id LEFT OUTER JOIN PasswordPolicy t9 ON t6.PASSWORDPOLICY_ID = t9.id WHERE (t0.schema_id = ?)
      

      The query can be optimized to:

      select count(UPlainAttr.id) FROM plainschema join UPlainAttr on plainschema.id = UPlainAttr.schema_id WHERE plainschema.id = ?
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                ilgrosso Francesco Chicchiriccò
                Reporter:
                DmitriyB. Dmitriy B.
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 20m
                  20m