Optimization of descendent resource queries

Jay Shaughnessy jshaughn at redhat.com
Fri Feb 21 20:09:25 UTC 2014


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 at lists.fedorahosted.org
> https://lists.fedorahosted.org/mailman/listinfo/rhq-devel



More information about the rhq-devel mailing list