Bug 661203 - improve inventory browser load efficiency (resources and groups)
Summary: improve inventory browser load efficiency (resources and groups)
Alias: None
Product: RHQ Project
Classification: Other
Component: Performance
Version: 4.0.0
Hardware: Unspecified
OS: Unspecified
Target Milestone: ---
: ---
Assignee: RHQ Project Maintainer
QA Contact: Sunil Kondkar
Depends On:
Blocks: jon241-bugs jon30-bugs
TreeView+ depends on / blocked
Reported: 2010-12-08 06:16 UTC by Joseph Marques
Modified: 2018-11-14 16:37 UTC (History)
2 users (show)

Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Clone Of:
Last Closed: 2013-08-31 13:08:31 UTC

Attachments (Terms of Use)

Description Joseph Marques 2010-12-08 06:16:16 UTC
The inventory browser uses ResourceManagerBean.findResourceCompositesByCriteria and ResourceGroupManagerBean.findResourceGroupCompositesByCriteria, respectively.  Both of these methods have a total number of DB roundtrips relative to the number of results pulled across the wire.  For each row in the result set, the methods do another query to the DB to lookup the data required to fill in the ResourcePermission object attach to the composites.  This means that these methods suffer from the class N+1 query problem, and need to be rewritten.  Plan is to leverage the CriteriaQueryGenerator.alterProjection method to pull this associated information across the wire along with each row result in a single query.

Note: commit will go into master as well as release-3.0.0 branch

Comment 1 Joseph Marques 2010-12-08 06:38:08 UTC
I've implemented the fix locally and have some before and after performance figures...for users with ("admin") and without ("user") the MANAGE_INVENTORY permission...loading the maximum UI page size of 45 rows of data at a time.  

Note: since inventory managers implicitly have full permission to all resources, they can skip the additional authz check; it doesn't change the number of DB roundtrips required to pull all the data, but does increase the complexity of the query and pulls slightly more data back across the wire:

  before:admin[ queries=48, xactions=0, loads=106, connects=121, time=247 ]
  after:admin[ queries=3, xactions=0, loads=120, connects=3, time=107 ]

  before:user[ queries=93, xactions=0, loads=120, connects=194, time=453 ]
  after:user[ queries=3, xactions=0, loads=63, connects=3, time=137 ]

  before:admin[ queries=48, xactions=0, loads=94, connects=48, time=351 ]
  after:admin[ queries=3, xactions=0, loads=94, connects=3, time=201 ]

  before:user[ queries=93, xactions=0, loads=93, connects=93, time=529 ]
  after:user[ queries=3, xactions=0, loads=94, connects=3, time=203 ]

Comment 2 Joseph Marques 2010-12-08 18:33:49 UTC
commit bfed4bf0a31ceff0d2dcebeaf5df632cc1ec1009 (release-3.0.0 branch)
Author: Joseph Marques <joseph@redhat.com>
Date:   Wed Dec 8 02:45:38 2010 -0500

    BZ-661203: improve inventory browser load efficiency
    * update find*CompositesByCriteria methods for resources/groups to eliminate
      the N+1 query issue by using constructor queries (made possible by the
      CriteriaQueryGenerator's alterProjection method) that pull all required
      data back in a single query

Comment 3 Joseph Marques 2010-12-08 22:05:36 UTC
To test:

make sure all sub-tabs for the inventory browser work for resources and groups including pagination and sorting.  also make sure that the inventory sub-tabs work for resources and groups (pagination and sorting), as well as the group definition details view (pagination and sorting).

Comment 4 Joseph Marques 2010-12-08 23:22:47 UTC
commit 26e1f5c5118830d72b4c29201e0b553235e1b4c6 (master branch)
Author: Joseph Marques <joseph@redhat.com>
Date:   Wed Dec 8 01:48:50 2010 -0500

    make get**CompositesByCriteria for resources/groups as efficient as possible
    * eliminate N+1 query issue by fetching the data required for the
      ResourcePermission object of each composite in the projection

Comment 7 Sunil Kondkar 2011-01-06 12:20:44 UTC
Verified on jon241 build#61 (build number: 10975:6823ab8)

Inventoried resources and created mixed/compatible groups and group definitions.
Navigated through all sub-tabs and tested pagination and sorting for the inventory browser for resources and groups.
Also navigated and tested pagination and sorting on the inventory sub-tabs for resources and groups, and pagination and sorting on the group definition details view.

Note You need to log in before you can comment on or make changes to this bug.