Elias, this is wicked stuff. I think we've been so tied in with
Hibernate/JPA that we were either unaware of this proprietary syntax or
had just dismissed it many moons ago. But with scalability now a
primary goal, this Oracle tree/recursive query support could be great.
It's too bad it'snot standard and that Postgres does things
differently. But it seems it has support as well. Although we don't
officially support anything else, we'd likely want to hold onto the HQL
approach as well, for other DBs.
I would guess that with differences in the starting resource (like a
platform) and the depth of resources underneath, performace would
degrade from what you're seeing, but I'm sure it would likely not reach
what we see today. The other nice thing here is that it gives us a way
to eliminate our artificial limit on hierarchy depth. Today, due to
these queries we can suport up to 6 levels under a platform. Anything
deeper and we're in trouble, these queries won't see them.
I'm going to look into making these changes...
On 2/21/2014 1:13 PM, Elias Ross wrote:
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?
_______________________________________________
rhq-devel mailing list
rhq-devel(a)lists.fedorahosted.org
https://lists.fedorahosted.org/mailman/listinfo/rhq-devel