Details

    • Type: Improvement Improvement
    • Status: Closed Closed
    • Priority: Critical Critical
    • Resolution: Fixed
    • Affects Version/s: 2.0.0-rc1, 2.0
    • Fix Version/s: 2.0.0-rc1, 2.0
    • Component/s: Database
    • Security Level: Public (Public: Anyone can view)
    • Labels:
      None
    • Similar issues:
      KULRICE-7613New DB index for KIM group membership
      KULRICE-7185Optimization of KIM Permission Checks
      KULRICE-6299New DB index to improve action list performance
      KULRICE-1133Update KIM DB diagram
      KULRICE-3735KIM Permission templates are queried from the DB too often
      KULRICE-2354KIM Permission Service Test
      KULRICE-3578Investigate and possibly create rice db indexes recommended by Colorado State
      KULRICE-2531Modify htmlControlAttribute to check KIM permissions
      KULRICE-3946Check KIM relationships for lazy loading
      KULRICE-2264Refactor Rice to use the KIM permission model for authorization checks
    • Rice Module:
      KIM
    • KAI Review Status:
      Not Required
    • KTI Review Status:
      Not Required

      Description

      The following indexes can improve the performance of several kinds of permission checks in KIM:

      • CREATE INDEX KRIM_ROLE_PERM_TI2 ON KRIM_ROLE_PERM_T (PERM_ID, ACTV_IND);
      • CREATE INDEX KRIM_PERM_TI1 ON KRIM_PERM_T (PERM_TMPL_ID);
      • CREATE INDEX KRIM_PERM_TI2 ON KRIM_PERM_T (PERM_TMPL_ID, ACTV_IND);
      • CREATE INDEX KRIM_PERM_TMPL_TI1 ON KRIM_PERM_TMPL_T (NMSPC_CD, NM);

      When applied to the IU database these indexes reduced the cost of some of the permission queries by half which improved the speed that those queries ran.

        Activity

        Hide
        James Bennett added a comment -

        Here are a couple more which help with role membership checks:

        CREATE INDEX KRIM_ROLE_MBR_TI2 ON KRIM_ROLE_MBR_T (role_id, mbr_id, mbr_typ_cd);
        CREATE INDEX KRIM_ROLE_MBR_TI3 ON KRIM_ROLE_MBR_T (mbr_id, mbr_typ_cd);

        These help improve the speed of permission checks as well.

        Show
        James Bennett added a comment - Here are a couple more which help with role membership checks: CREATE INDEX KRIM_ROLE_MBR_TI2 ON KRIM_ROLE_MBR_T (role_id, mbr_id, mbr_typ_cd); CREATE INDEX KRIM_ROLE_MBR_TI3 ON KRIM_ROLE_MBR_T (mbr_id, mbr_typ_cd); These help improve the speed of permission checks as well.
        Hide
        Eric Westfall added a comment -

        Reassigning to Peter since he's pulling together the final db upgrade scripts.

        Show
        Eric Westfall added a comment - Reassigning to Peter since he's pulling together the final db upgrade scripts.
        Hide
        Peter Giles (Inactive) added a comment -

        Added SQL to 2012-01-19b.sql & mysql-2012-01-19b.sql and applied to master db. Added to beta to beta impacting changes.

        Show
        Peter Giles (Inactive) added a comment - Added SQL to 2012-01-19b.sql & mysql-2012-01-19b.sql and applied to master db. Added to beta to beta impacting changes.
        Hide
        Jessica Coltrin (Inactive) added a comment -

        Closing since these items are now in the release notes.

        Show
        Jessica Coltrin (Inactive) added a comment - Closing since these items are now in the release notes.

          People

          • Assignee:
            Peter Giles (Inactive)
            Reporter:
            James Bennett
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Structure Helper Panel