Bug 1025918

See also Bug 918207.

It seems the problem is this query takes a long time to execute:

Resource.QUERY_FIND_DESCENDANTS

and ones like it, especially if there are a lot of resources (say, 500,000).

Could this be optimized in some better way?

In many cases, a resource does not have children, or if it does, just one level deep. This can be determined by looking at the resource type.

One suggestion is to do a native query, which may be faster:

select name
from rhq_resource r
start with r.id = 375489
connect by prior r.id = r.parent_resource_id

I'm not sure how much faster this is, but might be better than a ton of joins. Hibernate does not support this in HQL.

For Postgres, the solution is something like this:
http://wiki.postgresql.org/wiki/CTEReadme

Looking into making these changes...

I have a patch ready to go. Unless you really can't wait, can you please wait?

Postgres has recursive queries since 8.4, which is currently the minimum version that we require: http://www.postgresql.org/docs/current/static/queries-with.html

Elias,

Sorry, I was too excited to wait, and I wanted to see this in 4.10  But feel free to submit the patch and I can review to see if you found something I missed.  Likewise, take a look at the following commit if you get time.

master commit caa08cef46c679064040e6256fdaf24f2d402250
Author: Jay Shaughnessy <jshaughn>
Date:   Tue Feb 25 13:02:29 2014 -0500

    As suggested by Elias Ross, convert our depth-restricted "recursive" join
    queries with recursive queries leveraging native support in
    Oracle and Postgres.
    
    Also:
    - remove unused coregui (GWT) service that depended on an old query.

related:

master commit 31301a1f167e289814f31e2af343834544edb641
Author: Jay Shaughnessy <jshaughn>
Date:   Tue Feb 25 09:45:47 2014 -0500

    Added getLong() to the interface since different vendors do it differently.

Created attachment 870183 [details]
Native query patch - my version

My version I use NamedNativeQueries and create an entity class to hold the ID. Probably unnecessary, but I like that the query is part of the class.

One note.

+    public Long getLong(Object number) {
+        return (Long) number;
+    }

+    public Long getLong(Object number) {
+        BigDecimal longField = (BigDecimal) number;
+        return longField.longValue();
+    }

You can simply do:

+    public Long getLong(Object number) {
+        Number longField = (Number) number;
+        return longField.longValue();
+    }

Although you end up creating an extra Long object in the Oracle case.

I like the NamedNativeQuery idea, it's consistent with our use of NamedQuery.  I'll keep that in mind moving forward.  Thanks for the review and patch and most of all for helping us improve perf and get rid of those queries.

Bulk closing of 4.10 issues.

If an issue is not solved for you, please open a new BZ (or clone the existing one) with a version designator of 4.10.