Optimization of descendent resource queries
by Elias Ross
https://bugzilla.redhat.com/show_bug.cgi?id=1025918
With a large enough system (500,000+ resources), the queries for
finding the descendent resources of a resource can be very slow. This
is because of the various joins that take place. Of course, things
work well with a small number of resources, but not with tens of
thousands.
I'm optimizing the Oracle and Postgres cases using a recursive
sub-query. (See bug for details.)
>From my preliminary findings, the speed goes from about 5 seconds,
down to a few hundred milliseconds to uninventory a resource with a
few children. When you have to uninventory a few thousand resources,
this makes a big difference in usability.
There are a couple of other queries I was looking at:
public Resource getPlaformOfResource(Subject subject, int resourceId)
Is this really the same as this method? (with a authorization checks.)
The assumption is that the root resource is always a platform
resource.
getRootResourceForResource(resourceId);
The other one that could be fixed, but probably not needing optimization is:
getResourceDescendantsByTypeAndName
There are a couple of ways to fix this. One is simply doing a graph
traverse, you don't have to run a bunch of queries. It is more
memory/network intensive, but easy on the database.
Any thoughts on this?
10 years, 2 months