Optimization of descendent resource queries

Jay Shaughnessy jshaughn at redhat.com
Mon Feb 24 14:14:33 UTC 2014


Good stuff, I am also doing this, I spent some time on Friday and have 
the first query working (QUERY_FIND_DESCENDANTS).  I like the simpler 
Oracle syntax, although it's nice that you also found that Oracle 
supports almost a common syntax.   The Postgres query I came up with is 
similar to yours.  Here are the two I'm using currently.

     public static final String QUERY_NATIVE_FIND_DESCENDANTS_ORACLE = "" //
         + "           SELECT r.id " //
         + "             FROM rhq_resource r " //
         + "       START WITH r.id = ? " //
         + " CONNECT BY PRIOR r.id = r.parent_resource_id ";

     public static final String QUERY_NATIVE_FIND_DESCENDANTS_POSTGRES = 
"" //
         + " WITH RECURSIVE childResource AS " //
         + " ( " //
         + "   SELECT id FROM rhq_resource WHERE id = ? " // 
non-recursive term
         + "   UNION ALL " //
         + "   SELECT r.id " // recursive term
         + "     FROM rhq_resource AS r " //
         + "     JOIN childResource AS cr " //
         + "       ON (r.parent_resource_id = cr.id) " //
         + " ) " //
         + " SELECT id " //
         + "   FROM childResource ";

If you post the patch I'll combine what we have...

Jay


On 2/21/2014 4:53 PM, Elias Ross wrote:
> On Fri, Feb 21, 2014 at 12:09 PM, Jay Shaughnessy <jshaughn at redhat.com> wrote:
>> I'm going to look into making these changes...
> I updated the bug, but I already finished my changes, tested on Oracle
> (more than 10x speed improvement), and I have a patch to give, it just
> needs to be approved by Apple.
>
> Spoiler:
>
> Not to give too much away, but I actually figured out how to do this
> on both systems and have the query almost the same.
>
> For Oracle:
> +            + "WITH children(resource_id) AS (" // note no RECURSIVE keyword
> +            + "SELECT id FROM rhq_resource WHERE parent_resource_id =
> :resourceId "
> +            + "UNION ALL "
> +            + "SELECT r.id FROM children c, rhq_resource r WHERE
> r.parent_resource_id = c.resource_id) "
> +            + "SELECT resource_id FROM children"
>
> For PostGres:
> +            + "WITH RECURSIVE children(resource_id) AS ("
> +            + "SELECT id FROM rhq_resource WHERE parent_resource_id =
> :resourceId "
> +            + "UNION ALL "
> +            + "SELECT r.id FROM children c, rhq_resource r WHERE
> r.parent_resource_id = c.resource_id) "
> +            + "SELECT resource_id FROM children"
>
> The one unfixed query I'm missing is for matching resource name and
> type, used when an alert is triggered, causing an operation to fire.
> You can add additional columns (like name and type) for these queries,
> but they aren't used in all contexts. I suppose ideally you would just
> query the entire resource itself, but then each native query has to
> include every single column name for rhq_resource, which I wasn't
> interested in doing.
> _______________________________________________
> 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