Uploaded image for project: 'OpenIDM'
  1. OpenIDM
  2. OPENIDM-13873

Database performance on MS SQL 2012

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 6.5.0.1
    • Fix Version/s: 7.0.0
    • Labels:
    • Environment:
      IDM 6.5.0.1 . MS SQL 2012 with generic mapping
    • Target Version/s:
    • Verified Version/s:
    • Support Ticket IDs:
    • Zendesk ID:
      43478

      Description

      Customer notices high SQL server utilization on MS SQL 2012 repo.  The queries in question:

       

      First Query

       

      (@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 nvarchar(4000),@P4 nvarchar(4000),@P5 nvarchar(4000))

       

      SELECT originobj.fullobject AS o_fullobject, 

         originobj.objectid AS o_objectid, 

         originobj.rev AS o_rev, 

         first.firstpropertyname AS f_propname, 

         first.fullobject AS f_fullobject, 

         first.secondresourceid AS f_vertexid, 

         first.secondresourcecollection AS f_vertexcollection, 

         second.secondpropertyname AS s_propname, 

         second.fullobject AS s_fullobject, 

         second.firstresourceid AS s_vertexid, 

         second.firstresourcecollection AS s_vertexcollection 

      FROM 

         ( 

         SELECT TOP 1 obj.objectid , 

            obj.rev, 

            obj.fullobject 

         FROM openidm.objecttypes objtype, 

            openidm.managedobjects obj 

         WHERE obj.objecttypes_id = objtype.id 

        AND objtype.objecttype = @P0 

        AND obj.objectid = @P1 

         ) 

         originobj 

      LEFT OUTER JOIN openidm.relationships first 

      ON (first.firstresourceid=originobj.objectid 

      AND first.firstresourcecollection= @P2 

      AND first.firstpropertyname= @P3) 

      LEFT OUTER JOIN openidm.relationships second 

      ON (second.secondresourceid=originobj.objectid 

      AND second.secondresourcecollection= @P4 

      AND second.secondpropertyname= @P5)

       

      --****************************************

       

      Second Query

       

      (@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 nvarchar(4000),@P4 nvarchar(4000),@P5 nvarchar(4000))

       

      SELECT * 

      FROM openidm.relationships 

      WHERE ((firstresourcecollection = @P0 

      AND firstresourceid = @P1 

      AND firstpropertyname = @P2) 

      OR (secondresourcecollection = @P3 

      AND secondresourceid = @P4 AND secondpropertyname = @P5))

       

      Customer provided results from performance tests.  They show large usage when many AD groups with memberships greater than 100000, so in the case of these queries selecting all columns or fullobject is quite ineffent considering all the data in fullobject is also in the individual columns.  The customer main concern is that reconciliation is slow when they update attributes and do account provisioning. 

        Attachments

          Activity

            People

            • Assignee:
              brmiller Brendan Miller
              Reporter:
              jesse.ontiveros Jesse Ontiveros
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: