I've personally never been a fan of this query style, but I realize
there are pros and cons with every solution. The pro here is obviously
that it's quick to implement and get new, useful functionality into the
product without having to invest the effort in writing a more formal
solution for pre-computing hierarchical resource information. The con,
as your performance testing shows, is that all of the computational
burden is deferred to the much more frequent reads (instead of the less
frequent writes). As with any pre-compute solution, one needs to make
sure that every possible write-scenario is accounted for so as to keep
the pre-compute structure in perfect sync with its origin data; but the
solution in use today needn't be held to the same standards because the
hierarchical information is computed on each query so it's naturally
guaranteed to be correct.
As I eluded to above, this query style could actually be useful if the
results are stored. Cached results become, in effect, a type of
pre-compute structure themselves, thus allowing for more performant
reads at the cost of a much more complex query during the write-phase.
And this is actually how recursive groups work - users add/remove
resources to the "explicit" membership of the group, but
under-the-covers RHQ uses the "implicit" (pre-computed) membership for
doing most other work. However, recursive groups have become much more
pervasive over the last 15 months or so, and I'm starting to question
whether the complex query will continue to suffice:
* recursive groups used to be structures that users had to create one at
a time, but today you can use group definitions to generate recursive
groups dynamically and en mass
* group definitions used to be expressions that users had to recalculate
manually, but today you can set them up to automatically recalculate
after X minutes
* recursive groups used to be structures that were only applicable to
compatible groups, but that restriction was lifted (increasing the
number of recursive groups that can be automatically calculated by group
definitions)
-----
So I can certainly see the need for pre-computing all resource
hierarchies across the inventory becoming more and more important. A
good book on this topic is Joe Celko's Tree and Hierarchies[2], which
explains several methods for "flattening" hierarchical information. The
specific pre-compute structure chosen depends largely on what your
requirements are, in particular your anticipated access patterns for
going either up or down the hierarchy, and how many levels.
Here are the parts of the system that I know would benefit from
flattening the resource hierarchy:
* group definitions / dynamic groups -- expressions support filtering
resources 1, 2, 3, or 4 levels up; 1 level down
* recursive groups -- need to calculate "ALL" levels down, representing
entire resource sub-lineages
* search -- will support N levels up as well as N levels down, where N
is a user-chosen number specific to the search being performed
* resource disambiguation (RDA) -- worst case needs to calculate "ALL"
levels up, to ensure resources get unique qualifiers
Thus, whatever structure we come up with needs to support:
A) obtaining the entire ancestry or descendant-tree for a set of resources
B) navigating up and down the hierarchy by precisely N levels
--joseph
[1] - Recursive group - if you add a resource to a recursive group, all
of that resource's descendants will also become members of the group
[2] -
http://www.amazon.com/Hierarchies-Smarties-Kaufmann-Management-Systems/dp...
On 07/06/2010 04:39 AM, Heiko W.Rupp wrote:
Hi,
With in Resource.java we define quite some queries like
Resource.QUERY_FIND_DESCENDENTS
that go
+ "SELECT r.id " //
+ " FROM Resource r " //
+ " WHERE r.id = :resourceId " //
+ " OR r.id IN (SELECT rr.id FROM Resource rr WHERE rr.parentResource.id
= :resourceId) "
+ " OR r.id IN (SELECT rr.id FROM Resource rr WHERE
rr.parentResource.parentResource.id = :resourceId) "
+ " OR r.id IN (SELECT rr.id FROM Resource rr WHERE
rr.parentResource.parentResource.parentResource.id = :resourceId) "
+ " OR r.id IN (SELECT rr.id FROM Resource rr WHERE
rr.parentResource.parentResource.parentResource.parentResource.id = :resourceId) "
+ " OR r.id IN (SELECT rr.id FROM Resource rr WHERE
rr.parentResource.parentResource.parentResource.parentResource.parentResource.id =
:resourceId) "
+ " OR r.id IN (SELECT rr.id FROM Resource rr WHERE
rr.parentResource.parentResource.parentResource.parentResource.parentResource.parentResource.id
= :resourceId) "
Oracle EM reports very high cpu usage when processing them.
I wonder if we should add a 'helper table' (or materialized view), which gets
updated on resource creation/deletion
a little like this:
RHQ_RESOURCE_PARENT_CHILD (= X in the following examples)
(
int resourceId;
int childId;
int resourceTypeId;
int childTypeId;
int resourceCategoryId;
)
with a pk of (resourceId, childId)
Entries would be for each resource id we have in inventory its id, the ids of all of its
children, type of resource, category of resource.
Some example queries:
- To find the parent platform of a resource
select resourceId from X where childId = :child and resourceCategory = platform;
- To find all resources below a given root (e.g. for marking as uninventoried)
select childId from X where resourceId = :root;
- To find all children of a certain type (= autogroup)
select childIf from X where resourceId = :root and childTypeId = :childType
- mark all children of a root as deleted (2 steps now, 2nd one to get rid of resources to
delete from X )
update RHQ_RESOURCE set INVENTORY_STATUS= :status ,...
where id in (select childId FROM x where resourceId = :root)
delete FROM X where resourceId = :root
I am sure that there are a ton more of information that could be included here
like the 'owner' of a resource or its name. In any cases some good indices
would be needed.
Also to replace existing queries, in some cases would perhaps need two
roundtrips to the database, so in this case one would need to investigate
the benefits here.