[prev in list] [next in list] [prev in thread] [next in thread] 

List:       rhq-devel
Subject:    Re: Optimization of descendent resource queries
From:       Jay Shaughnessy <jshaughn () redhat ! com>
Date:       2014-02-25 3:19:43
Message-ID: 530C0BCF.6000005 () redhat ! com
[Download RAW message or body]



Looks like I have the stuff working.  I need to do a little more testing 
and should commit to master tomorrow.  Thanks!


On 2/24/2014 9:14 AM, Jay Shaughnessy wrote:
>
> 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@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@lists.fedorahosted.org
>> https://lists.fedorahosted.org/mailman/listinfo/rhq-devel
>
> _______________________________________________
> rhq-devel mailing list
> rhq-devel@lists.fedorahosted.org
> https://lists.fedorahosted.org/mailman/listinfo/rhq-devel

_______________________________________________
rhq-devel mailing list
rhq-devel@lists.fedorahosted.org
https://lists.fedorahosted.org/mailman/listinfo/rhq-devel

[prev in list] [next in list] [prev in thread] [next in thread]