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
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: ResourceManagerBean.findResourceCompositesByCriteria 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 ] ResourceGroupManagerBean.findResourceGroupCompositesByCriteria 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 ]
commit bfed4bf0a31ceff0d2dcebeaf5df632cc1ec1009 (release-3.0.0 branch) Author: Joseph Marques <joseph> 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
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).
commit 26e1f5c5118830d72b4c29201e0b553235e1b4c6 (master branch) Author: Joseph Marques <joseph> 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
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.