Uploaded image for project: 'MidPoint'
  1. MidPoint
  2. MID-1774

OS performance



    • Type: Bug
    • Status: Closed
    • Priority: Blocker
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 3.0 (Newton)
    • Component/s: Repository
    • Labels:


      Really unusable selects are used for searching children through org. closure table:

      select closure1_.descendant_id as y0_, closure1_.descendant_oid as y1_, this_.name_orig as y2_, this_3_.fullObject as y3_ from m_org this_ inner join m_abstract_role this_1_ on this_.id=this_1_.id and this_.oid=this_1_.oid inner join m_focus this_2_ on this_.id=this_2_.id and this_.oid=this_2_.oid inner join m_object this_3_ on this_.id=this_3_.id and this_.oid=this_3_.oid inner join m_container this_4_ on this_.id=this_4_.id and this_.oid=this_4_.oid inner join m_org_closure closure1_ on this_.id=closure1_.descendant_id and this_.oid=closure1_.descendant_oid inner join m_object anc2_ on closure1_.ancestor_id=anc2_.id and closure1_.ancestor_oid=anc2_.oid left outer join m_container anc2_1_ on anc2_.id=anc2_1_.id and anc2_.oid=anc2_1_.oid left outer join m_generic_object anc2_2_ on anc2_.id=anc2_2_.id and anc2_.oid=anc2_2_.oid left outer join m_focus anc2_3_ on anc2_.id=anc2_3_.id and anc2_.oid=anc2_3_.oid left outer join m_user anc2_4_ on anc2_.id=anc2_4_.id and anc2_.oid=anc2_4_.oid left outer join m_report anc2_5_ on anc2_.id=anc2_5_.id and anc2_.oid=anc2_5_.oid left outer join m_resource anc2_6_ on anc2_.id=anc2_6_.id and anc2_.oid=anc2_6_.oid left outer join m_object_template anc2_7_ on anc2_.id=anc2_7_.id and anc2_.oid=anc2_7_.oid left outer join m_node anc2_8_ on anc2_.id=anc2_8_.id and anc2_.oid=anc2_8_.oid left outer join m_report_output anc2_9_ on anc2_.id=anc2_9_.id and anc2_.oid=anc2_9_.oid left outer join m_value_policy anc2_10_ on anc2_.id=anc2_10_.id and anc2_.oid=anc2_10_.oid left outer join m_connector_host anc2_11_ on anc2_.id=anc2_11_.id and anc2_.oid=anc2_11_.oid left outer join m_abstract_role anc2_12_ on anc2_.id=anc2_12_.id and anc2_.oid=anc2_12_.oid left outer join m_role anc2_13_ on anc2_.id=anc2_13_.id and anc2_.oid=anc2_13_.oid left outer join m_system_configuration anc2_14_ on anc2_.id=anc2_14_.id and anc2_.oid=anc2_14_.oid left outer join m_connector anc2_15_ on anc2_.id=anc2_15_.id and anc2_.oid=anc2_15_.oid left outer join m_shadow anc2_16_ on anc2_.id=anc2_16_.id and anc2_.oid=anc2_16_.oid left outer join m_org anc2_17_ on anc2_.id=anc2_17_.id and anc2_.oid=anc2_17_.oid left outer join m_task anc2_18_ on anc2_.id=anc2_18_.id and anc2_.oid=anc2_18_.oid where (anc2_.id=? and anc2_.oid=? and closure1_.depthValue<=? and closure1_.depthValue>?) group by closure1_.descendant_id, closure1_.descendant_oid, this_.name_orig order by this_.name_orig asc

      It takes 100-500ms and it could be rewritten to:

       SELECT closure1_.descendant_id  AS y0_,
             closure1_.descendant_oid AS y1_,
             this_.name_orig          AS y2_,
             this_.fullobject       AS y3_
      FROM   m_object this_
             inner join m_container this_4_
                     ON this_.id = this_4_.id
                        AND this_.oid = this_4_.oid
             inner join m_org_closure closure1_
                     ON this_.id = closure1_.descendant_id
                        AND this_.oid = closure1_.descendant_oid
             inner join m_object anc2_
                     ON closure1_.ancestor_id = anc2_.id
                        AND closure1_.ancestor_oid = anc2_.oid
      WHERE  ( anc2_.id =0
               AND anc2_.oid ='00000000-8888-6666-0000-100000000003'
               AND closure1_.depthvalue =1)
      GROUP  BY closure1_.descendant_id,
      order by this_.name_orig asc

      This select took 5ms. Tested on experimental faster repository implementation. Same unusable select is generated using midpoint from master.




            lazyman Viliam Repan
            lazyman Viliam Repan
            0 Vote for this issue
            1 Start watching this issue