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