Grouper
  1. Grouper
  2. GRP-723

missing parens in SQL statement in large deployment cause severe performance problem

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 2.0.2
    • Fix Version/s: 2.0.3
    • Component/s: API
    • Labels:
      None

      Description

      There is also probably a logic problem.
      1. patch.tar.gz
        38 kB
        Chris Hyzer
      2. patch.txt
        1 kB
        Chris Hyzer

        Activity

        Chris Hyzer created issue -
        Hide
        Chris Hyzer added a comment - - edited
        The lack of parens kills the explain plan in oracle and the performance of large deployments (and is probably wrong logic). Here is the patch:

        Index: Hib3GroupDAO.java
        ===================================================================
        --- Hib3GroupDAO.java (revision 7744)
        +++ Hib3GroupDAO.java (working copy)
        @@ -1074,7 +1074,7 @@
               hql.append(" where ");
             }
             
        - hql.append(" theGroup.nameDb = :value or theGroup.alternateNameDb = :value ");
        + hql.append(" ( theGroup.nameDb = :value or theGroup.alternateNameDb = :value ) ");
             
             byHqlStatic.createQuery(hql.toString())
               .setCacheable(true).setCacheRegion(KLASS + ".FindByNameSecure").options(queryOptions);
        @@ -2360,13 +2360,13 @@
               } else {
                 sql.append(" where ");
               }
        - sql.append(" theGroup.nameDb in ( ");
        + sql.append(" ( theGroup.nameDb in ( ");
               
               sql.append(HibUtils.convertToInClause(namesBatch, byHqlStatic)).append(" ) ");
         
               sql.append(" or theGroup.alternateNameDb in ( ");
               
        - sql.append(HibUtils.convertToInClause(namesBatch, byHqlStatic)).append(" ) ");
        + sql.append(HibUtils.convertToInClause(namesBatch, byHqlStatic)).append(" ) )");
         
               byHqlStatic
                 .createQuery(sql.toString())
        Show
        Chris Hyzer added a comment - - edited The lack of parens kills the explain plan in oracle and the performance of large deployments (and is probably wrong logic). Here is the patch: Index: Hib3GroupDAO.java =================================================================== --- Hib3GroupDAO.java (revision 7744) +++ Hib3GroupDAO.java (working copy) @@ -1074,7 +1074,7 @@        hql.append(" where ");      }       - hql.append(" theGroup.nameDb = :value or theGroup.alternateNameDb = :value "); + hql.append(" ( theGroup.nameDb = :value or theGroup.alternateNameDb = :value ) ");            byHqlStatic.createQuery(hql.toString())        .setCacheable(true).setCacheRegion(KLASS + ".FindByNameSecure").options(queryOptions); @@ -2360,13 +2360,13 @@        } else {          sql.append(" where ");        } - sql.append(" theGroup.nameDb in ( "); + sql.append(" ( theGroup.nameDb in ( ");                sql.append(HibUtils.convertToInClause(namesBatch, byHqlStatic)).append(" ) ");          sql.append(" or theGroup.alternateNameDb in ( ");         - sql.append(HibUtils.convertToInClause(namesBatch, byHqlStatic)).append(" ) "); + sql.append(HibUtils.convertToInClause(namesBatch, byHqlStatic)).append(" ) )");          byHqlStatic          .createQuery(sql.toString())
        Chris Hyzer made changes -
        Field Original Value New Value
        Status Open [ 1 ] Resolved [ 5 ]
        Fix Version/s 2.0.3 [ 10721 ]
        Resolution Fixed [ 1 ]
        Hide
        Chris Hyzer added a comment - - edited
        The problem here is that the problem query looks like this:

        SELECT DISTINCT group0_.id AS id21_,
                        group0_.hibernate_version_number AS hibernate2_21_,
                        group0_.last_membership_change AS last3_21_,
                        group0_.last_imm_membership_change AS last4_21_,
                        group0_.parent_stem AS parent5_21_,
                        group0_.creator_id AS creator6_21_,
                        group0_.create_time AS create7_21_,
                        group0_.modifier_id AS modifier8_21_,
                        group0_.modify_time AS modify9_21_,
                        group0_.name AS name21_,
                        group0_.display_name AS display11_21_,
                        group0_.extension AS extension21_,
                        group0_.display_extension AS display13_21_,
                        group0_.description AS descrip14_21_,
                        group0_.context_id AS context15_21_,
                        group0_.alternate_name AS alternate16_21_,
                        group0_.type_of_group AS type17_21_
          FROM grouper_groups group0_, grouper_memberships_all_v membership1_
         WHERE ( membership1_.owner_group_id = group0_.id
               AND (membership1_.field_id IN
                       ('0b195b7c-e526-4d40-9098-510e021093c1',
                        'd68dad3b-be79-40ce-8128-86503ce21a37',
                        '8f59f9fb-0caf-47f9-8fdf-428531f03514',
                        '655aea9c-a8c1-429e-be4b-3b22605cf58f',
                        'd647453b-db49-474f-834a-52db840377ea',
                        '7b83eb56-eea1-4d73-b1b2-5134a799218f'))
               AND (membership1_.member_id IN
                       ('theuserid',
                        'GrouperAllId'))
               AND membership1_.immediate_mship_enabled = 'T'
               AND (group0_.name IN ('somestring2'))
               OR group0_.alternate_name IN ('somestring2') )

        Note the lack of parens on the OR statement.

        Here is what it should look like:

        SELECT DISTINCT group0_.id AS id21_,
                        group0_.hibernate_version_number AS hibernate2_21_,
                        group0_.last_membership_change AS last3_21_,
                        group0_.last_imm_membership_change AS last4_21_,
                        group0_.parent_stem AS parent5_21_,
                        group0_.creator_id AS creator6_21_,
                        group0_.create_time AS create7_21_,
                        group0_.modifier_id AS modifier8_21_,
                        group0_.modify_time AS modify9_21_,
                        group0_.name AS name21_,
                        group0_.display_name AS display11_21_,
                        group0_.extension AS extension21_,
                        group0_.display_extension AS display13_21_,
                        group0_.description AS descrip14_21_,
                        group0_.context_id AS context15_21_,
                        group0_.alternate_name AS alternate16_21_,
                        group0_.type_of_group AS type17_21_
          FROM grouper_groups group0_, grouper_memberships_all_v membership1_
         WHERE ( membership1_.owner_group_id = group0_.id
               AND (membership1_.field_id IN
                       ('0b195b7c-e526-4d40-9098-510e021093c1',
                        'd68dad3b-be79-40ce-8128-86503ce21a37',
                        '8f59f9fb-0caf-47f9-8fdf-428531f03514',
                        '655aea9c-a8c1-429e-be4b-3b22605cf58f',
                        'd647453b-db49-474f-834a-52db840377ea',
                        '7b83eb56-eea1-4d73-b1b2-5134a799218f'))
               AND (membership1_.member_id IN
                       ('theuserid',
                        'GrouperAllId'))
               AND membership1_.immediate_mship_enabled = 'T'
               AND ( (group0_.name IN ('somestring2'))
               OR group0_.alternate_name IN ('somestring2') ) )

        The bad query has an explain plan of: 1.2 trillion bytes, 7.5 million cost
        The good query has an explain plan of: 706 bytes, 161 cost

        The issue is this query is used when doing a subject search by identifier against the g:gsa source,
        and when the WS authenticates, it does a subject lookup for the authenticating user by identifier.
        In Penn's environment it was a 60 second penalty.
        Then (ever so slightly good news), once Oracle does the query, its significantly improved the next time (if soon after), down to 15 seconds

        We just put the new classfiles of the WEB-INF/classes/edu/internet2/middleware/grouper/internal/dao/hib3 dir and we are all set...
        Show
        Chris Hyzer added a comment - - edited The problem here is that the problem query looks like this: SELECT DISTINCT group0_.id AS id21_,                 group0_.hibernate_version_number AS hibernate2_21_,                 group0_.last_membership_change AS last3_21_,                 group0_.last_imm_membership_change AS last4_21_,                 group0_.parent_stem AS parent5_21_,                 group0_.creator_id AS creator6_21_,                 group0_.create_time AS create7_21_,                 group0_.modifier_id AS modifier8_21_,                 group0_.modify_time AS modify9_21_,                 group0_.name AS name21_,                 group0_.display_name AS display11_21_,                 group0_.extension AS extension21_,                 group0_.display_extension AS display13_21_,                 group0_.description AS descrip14_21_,                 group0_.context_id AS context15_21_,                 group0_.alternate_name AS alternate16_21_,                 group0_.type_of_group AS type17_21_   FROM grouper_groups group0_, grouper_memberships_all_v membership1_  WHERE ( membership1_.owner_group_id = group0_.id        AND (membership1_.field_id IN                ('0b195b7c-e526-4d40-9098-510e021093c1',                 'd68dad3b-be79-40ce-8128-86503ce21a37',                 '8f59f9fb-0caf-47f9-8fdf-428531f03514',                 '655aea9c-a8c1-429e-be4b-3b22605cf58f',                 'd647453b-db49-474f-834a-52db840377ea',                 '7b83eb56-eea1-4d73-b1b2-5134a799218f'))        AND (membership1_.member_id IN                ('theuserid',                 'GrouperAllId'))        AND membership1_.immediate_mship_enabled = 'T'        AND (group0_.name IN ('somestring2'))        OR group0_.alternate_name IN ('somestring2') ) Note the lack of parens on the OR statement. Here is what it should look like: SELECT DISTINCT group0_.id AS id21_,                 group0_.hibernate_version_number AS hibernate2_21_,                 group0_.last_membership_change AS last3_21_,                 group0_.last_imm_membership_change AS last4_21_,                 group0_.parent_stem AS parent5_21_,                 group0_.creator_id AS creator6_21_,                 group0_.create_time AS create7_21_,                 group0_.modifier_id AS modifier8_21_,                 group0_.modify_time AS modify9_21_,                 group0_.name AS name21_,                 group0_.display_name AS display11_21_,                 group0_.extension AS extension21_,                 group0_.display_extension AS display13_21_,                 group0_.description AS descrip14_21_,                 group0_.context_id AS context15_21_,                 group0_.alternate_name AS alternate16_21_,                 group0_.type_of_group AS type17_21_   FROM grouper_groups group0_, grouper_memberships_all_v membership1_  WHERE ( membership1_.owner_group_id = group0_.id        AND (membership1_.field_id IN                ('0b195b7c-e526-4d40-9098-510e021093c1',                 'd68dad3b-be79-40ce-8128-86503ce21a37',                 '8f59f9fb-0caf-47f9-8fdf-428531f03514',                 '655aea9c-a8c1-429e-be4b-3b22605cf58f',                 'd647453b-db49-474f-834a-52db840377ea',                 '7b83eb56-eea1-4d73-b1b2-5134a799218f'))        AND (membership1_.member_id IN                ('theuserid',                 'GrouperAllId'))        AND membership1_.immediate_mship_enabled = 'T'        AND ( (group0_.name IN ('somestring2'))        OR group0_.alternate_name IN ('somestring2') ) ) The bad query has an explain plan of: 1.2 trillion bytes, 7.5 million cost The good query has an explain plan of: 706 bytes, 161 cost The issue is this query is used when doing a subject search by identifier against the g:gsa source, and when the WS authenticates, it does a subject lookup for the authenticating user by identifier. In Penn's environment it was a 60 second penalty. Then (ever so slightly good news), once Oracle does the query, its significantly improved the next time (if soon after), down to 15 seconds We just put the new classfiles of the WEB-INF/classes/edu/internet2/middleware/grouper/internal/dao/hib3 dir and we are all set...
        Chris Hyzer made changes -
        Attachment patch.tar.gz [ 10963 ]
        Chris Hyzer made changes -
        Attachment patch.txt [ 10964 ]
        Chris Hyzer made changes -
        Summary missing parens in large deployment causes severe performance problem missing parens in SQL statement in large deployment causes severe performance problem
        Chris Hyzer made changes -
        Comment [ classfiles ]
        Chris Hyzer made changes -
        Comment [ source patch ]
        Chris Hyzer made changes -
        Summary missing parens in SQL statement in large deployment causes severe performance problem missing parens in SQL statement in large deployment cause severe performance problem

          People

          • Assignee:
            Chris Hyzer
            Reporter:
            Chris Hyzer
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: