Optimization of descendent resource queries

Larry O'Leary loleary at redhat.com
Fri Feb 21 20:37:53 UTC 2014


On Fri, 2014-02-21 at 15:09 -0500, Jay Shaughnessy wrote:
> 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.

For the most part, I think there is a standard way that you can optimize
these queries. I have not seen the data for this issue but in many cases
Hibernate performs its joins in memory. Meaning that the queries don't
get pushed out to the database for optimization. It also may not be
using prepared statements which also slows things down when executing
the same types of queries over and over.

Perhaps using something like Teiid would be ideal. It allows you to use
the same call on all supported databases without worrying about what
happens under the covers. Teiid's query optimizer and planner can figure
out where the data lives and how best to get at it with as little time
as possible to retrieve the data. That said, its capabilities may be too
heavy weight for what we need, but then again, so is Hibernate. 



> 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
> 
> _______________________________________________
> 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