Attachment 870183 Details for Bug 1025918 – Native query patch - my version

[patch] Native query patch - my version

0001-BZ-1025918-Use-native-queries-for-finding-the-childr.patch (text/plain), 12.22 KB, created by Elias Ross on 2014-03-04 00:17:14 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Elias Ross

Created: 2014-03-04 00:17:14 UTC

Size: 12.22 KB

patch

obsolete

>From 72a75cb5e30b664f7b6ccf2b48e9a56752cd7758 Mon Sep 17 00:00:00 2001
>From: Elias Ross <elias_ross@apple.com>
>Date: Wed, 19 Feb 2014 17:16:31 -0800
>Subject: [PATCH] BZ 1025918 - Use native queries for finding the children or
> parent of resources
>
>Improves uninventory time from about 6 seconds a resource to 600ms for a server with
>70,000 resources.
>
>Works for Postgres or Oracle.
>---
> .../org/rhq/core/domain/resource/Resource.java     | 75 +++++++++++++++++++++-
> .../server/resource/ResourceManagerBean.java       | 55 ++++++++++++----
> 2 files changed, 117 insertions(+), 13 deletions(-)
>
>diff --git a/modules/core/domain/src/main/java/org/rhq/core/domain/resource/Resource.java b/modules/core/domain/src/main/java/org/rhq/core/domain/resource/Resource.java
>index 8fc65a8..be07c97 100644
>--- a/modules/core/domain/src/main/java/org/rhq/core/domain/resource/Resource.java
>+++ b/modules/core/domain/src/main/java/org/rhq/core/domain/resource/Resource.java
>@@ -37,6 +37,8 @@
> import javax.persistence.JoinColumn;
> import javax.persistence.ManyToMany;
> import javax.persistence.ManyToOne;
>+import javax.persistence.NamedNativeQueries;
>+import javax.persistence.NamedNativeQuery;
> import javax.persistence.NamedQueries;
> import javax.persistence.NamedQuery;
> import javax.persistence.OneToMany;
>@@ -69,6 +71,7 @@
> import org.rhq.core.domain.measurement.ResourceAvailability;
> import org.rhq.core.domain.operation.ResourceOperationHistory;
> import org.rhq.core.domain.resource.group.ResourceGroup;
>+import org.rhq.core.domain.resource.Resource.ResourceID;
> import org.rhq.core.domain.tagging.Tag;
> import org.rhq.core.domain.util.Summary;
> 
>@@ -76,6 +79,38 @@
>  * Represents an RHQ managed resource (i.e. a platform, server, or service).
>  */
> @Entity
>+@NamedNativeQueries({
>+    @NamedNativeQuery(name = Resource.QUERY_FIND_DESCENDANTS_ORACLE, resultClass=ResourceID.class, query = ""
>+            // See: http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#SQLRF55576
>+            + "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"
>+            ),
>+    @NamedNativeQuery(name = Resource.QUERY_FIND_DESCENDANTS_POSTGRES, resultClass=ResourceID.class, query = ""
>+            // See: http://www.postgresql.org/docs/current/static/queries-with.html
>+            + "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"
>+            ),
>+    @NamedNativeQuery(name = Resource.QUERY_FIND_ROOT_POSTGRES, resultClass=ResourceID.class, query = ""
>+            + "WITH RECURSIVE parent(resource_id, pid) AS ("
>+            + "SELECT resource_id, parent_resource_id FROM rhq_resource WHERE resource_id = :resourceId"
>+            + "UNION ALL "
>+            + "SELECT r.id, r.parent_resource_id FROM parent p, rhq_resource r WHERE r.id = p.pid )"
>+            + "SELECT resource_id FROM parent WHERE pid is NULL"
>+            ),
>+    @NamedNativeQuery(name = Resource.QUERY_FIND_ROOT_ORACLE, resultClass=ResourceID.class, query = ""
>+            + "WITH parent(resource_id, pid) AS ("
>+            + "SELECT resource_id, parent_resource_id FROM rhq_resource WHERE resource_id = :resourceId"
>+            + "UNION ALL "
>+            + "SELECT r.id, r.parent_resource_id FROM parent p, rhq_resource r WHERE r.id = p.pid )"
>+            + "SELECT resource_id FROM parent WHERE pid is NULL"
>+            ),
>+})
> @NamedQueries({
>     @NamedQuery(name = Resource.QUERY_FIND_PROBLEM_RESOURCES_ALERT_ADMIN, query = "" //
>         + "  SELECT DISTINCT new org.rhq.core.domain.resource.composite.ProblemResourceComposite"
>@@ -609,7 +644,7 @@
>         + " (SELECT count(p) FROM res.implicitGroups g JOIN g.roles r JOIN r.subjects s JOIN r.permissions p WHERE s = :subject AND p = 9), " // we want MANAGE_CONTENT, 9
>         + " (SELECT count(p) FROM res.implicitGroups g JOIN g.roles r JOIN r.subjects s JOIN r.permissions p WHERE s = :subject AND p = 6), " // we want CREATE_CHILD_RESOURCES, 6
>         + " (SELECT count(p) FROM res.implicitGroups g JOIN g.roles r JOIN r.subjects s JOIN r.permissions p WHERE s = :subject AND p = 5), " // we want DELETE_RESOURCES, 5
>-        + " (SELECT count(p) FROM res.implicitGroups g JOIN g.roles r JOIN r.subjects s JOIN r.permissions p WHERE s = :subject AND p = 16)) " // we want MANAGE_DRIFT, 16        
>+        + " (SELECT count(p) FROM res.implicitGroups g JOIN g.roles r JOIN r.subjects s JOIN r.permissions p WHERE s = :subject AND p = 16)) " // we want MANAGE_DRIFT, 16
>         + "FROM Resource res " //
>         + "     LEFT JOIN res.parentResource parent " //
>         + "     LEFT JOIN res.currentAvailability a " //
>@@ -888,9 +923,13 @@
>     public static final String QUERY_FIND_PARENT_ID = "Resource.findParentId";
> 
>     public static final String QUERY_FIND_ROOT_PLATFORM_OF_RESOURCE = "Resource.findRootPlatformOfResource";
>+    public static final String QUERY_FIND_ROOT_ORACLE = "Resource.findRootOracle";
>+    public static final String QUERY_FIND_ROOT_POSTGRES = "Resource.findRootPostgres";
> 
>     public static final String QUERY_FIND_DESCENDANTS_BY_TYPE_AND_NAME = "Resource.findDescendantsByTypeAndName";
>     public static final String QUERY_FIND_DESCENDANTS = "Resource.findDescendants";
>+    public static final String QUERY_FIND_DESCENDANTS_ORACLE = "Resource.findDescendantsOracle";
>+    public static final String QUERY_FIND_DESCENDANTS_POSTGRES = "Resource.findDescendantsPostgres";
>     public static final String QUERY_MARK_RESOURCES_FOR_ASYNC_DELETION_QUICK = "Resource.markResourcesForAsyncDeletionQuick";
>     public static final String QUERY_FIND_RESOURCES_MARKED_FOR_ASYNC_DELETION = "Resource.findResourcesMarkedForAsyncDeletion";
> 
>@@ -1876,10 +1915,42 @@ public String toString() {
>     // this should only ever be called once, during initial persistence
>     public void initCurrentAvailability() {
>         if (this.currentAvailability == null) {
>-            // initialize avail to be one big unknown period, starting at epoch. 
>+            // initialize avail to be one big unknown period, starting at epoch.
>             this.currentAvailability = new ResourceAvailability(this, AvailabilityType.UNKNOWN);
>             this.availability = new ArrayList<Availability>(1);
>             this.availability.add(new Availability(this, 0L, AvailabilityType.UNKNOWN));
>         }
>     }
>+
>+    /**
>+     * Holds a primary key for a {@link Resource}.
>+     * Used by {@link NamedNativeQuery} to return from queries of resources by ID.
>+     * Note: This class cannot be used outside of JPA.
>+     *
>+     * @see http://stackoverflow.com/questions/13278544/returning-a-numeric-value-with-hibernate-namednativequery
>+     * @see NamedNativeQuery
>+     */
>+    @Entity
>+    public static class ResourceID {
>+
>+        @Id
>+        @Column(name = "RESOURCE_ID")
>+        private int id;
>+
>+        ResourceID() { }
>+
>+        /**
>+         * Returns the primary key.
>+         */
>+        public int getId() {
>+            return id;
>+        }
>+
>+        @Override
>+        public String toString() {
>+            return String.valueOf(id);
>+        }
>+
>+    }
>+
> }
>diff --git a/modules/enterprise/server/jar/src/main/java/org/rhq/enterprise/server/resource/ResourceManagerBean.java b/modules/enterprise/server/jar/src/main/java/org/rhq/enterprise/server/resource/ResourceManagerBean.java
>index 2fa0773..d1f9890 100644
>--- a/modules/enterprise/server/jar/src/main/java/org/rhq/enterprise/server/resource/ResourceManagerBean.java
>+++ b/modules/enterprise/server/jar/src/main/java/org/rhq/enterprise/server/resource/ResourceManagerBean.java
>@@ -49,9 +49,10 @@
> import org.apache.commons.logging.LogFactory;
> import org.jetbrains.annotations.NotNull;
> import org.jetbrains.annotations.Nullable;
>-
> import org.rhq.core.db.DatabaseType;
> import org.rhq.core.db.DatabaseTypeFactory;
>+import org.rhq.core.db.OracleDatabaseType;
>+import org.rhq.core.db.PostgresqlDatabaseType;
> import org.rhq.core.domain.alert.Alert;
> import org.rhq.core.domain.alert.AlertCondition;
> import org.rhq.core.domain.alert.AlertConditionLog;
>@@ -94,6 +95,7 @@
> import org.rhq.core.domain.resource.DeleteResourceHistory;
> import org.rhq.core.domain.resource.InventoryStatus;
> import org.rhq.core.domain.resource.Resource;
>+import org.rhq.core.domain.resource.Resource.ResourceID;
> import org.rhq.core.domain.resource.ResourceAncestryFormat;
> import org.rhq.core.domain.resource.ResourceCategory;
> import org.rhq.core.domain.resource.ResourceError;
>@@ -374,9 +376,7 @@ public void uninventoryAllResourcesByAgent(Subject user, Agent doomedAgent) {
>             // set agent references null
>             // foobar the resourceKeys
>             // update the inventory status to UNINVENTORY
>-            Query toBeDeletedQuery = entityManager.createNamedQuery(Resource.QUERY_FIND_DESCENDANTS);
>-            toBeDeletedQuery.setParameter("resourceId", resourceId);
>-            List<Integer> toBeDeletedResourceIds = toBeDeletedQuery.getResultList();
>+            List<Integer> toBeDeletedResourceIds = getFamily(resource);
> 
>             int i = 0;
>             if (isDebugEnabled) {
>@@ -1097,12 +1097,24 @@ private String getResourceLongName(String resourceName, ResourceType type) {
>         return sb.toString();
>     }
> 
>+    private Resource getResourceFromNative(String queryStr, int resourceId) {
>+        Query query = entityManager.createNamedQuery(queryStr);
>+        ResourceID id = (ResourceID) query.getSingleResult();
>+        query.setParameter("resourceId", resourceId);
>+        return entityManager.getReference(Resource.class, id.getId());
>+    }
>+
>     @NotNull
>     public Resource getRootResourceForResource(int resourceId) {
>-        Query q = entityManager.createNamedQuery(Resource.QUERY_FIND_ROOT_PLATFORM_OF_RESOURCE);
>-        q.setParameter("resourceId", resourceId);
>-
>-        return (Resource) q.getSingleResult();
>+        DatabaseType type = DatabaseTypeFactory.getDefaultDatabaseType();
>+        if (type instanceof OracleDatabaseType) {
>+            return getResourceFromNative(Resource.QUERY_FIND_ROOT_ORACLE, resourceId);
>+        } else if (type instanceof PostgresqlDatabaseType) {
>+            return getResourceFromNative(Resource.QUERY_FIND_ROOT_POSTGRES, resourceId);
>+        }
>+        Query query = entityManager.createNamedQuery(Resource.QUERY_FIND_ROOT_PLATFORM_OF_RESOURCE);
>+        query.setParameter("resourceId", resourceId);
>+        return (Resource) query.getSingleResult();
>     }
> 
>     @SuppressWarnings("unchecked")
>@@ -2844,14 +2856,35 @@ public Integer getResourceCount(List<Integer> resourceTypeIds) {
>         return reports;
>     }
> 
>+    /**
>+     * Queries for descendants using an optimized native query.
>+     */
>+    private List<Integer> getFamilyNativeQuery(String queryStr, Resource resource) {
>+        Query query = entityManager.createNamedQuery(queryStr);
>+        query.setParameter("resourceId", resource.getId());
>+        List<ResourceID> ids = query.getResultList();
>+        List<Integer> ids2 = new ArrayList<Integer>(ids.size());
>+        for (ResourceID id : ids) {
>+            ids2.add(id.getId());
>+        }
>+        return ids2;
>+    }
>+
>+    /**
>+     * Returns all descendants of this resource.
>+     */
>     private List<Integer> getFamily(Resource resource) {
>+        DatabaseType type = DatabaseTypeFactory.getDefaultDatabaseType();
>+        if (type instanceof OracleDatabaseType) {
>+            return getFamilyNativeQuery(Resource.QUERY_FIND_DESCENDANTS_ORACLE, resource);
>+        } else if (type instanceof PostgresqlDatabaseType) {
>+            return getFamilyNativeQuery(Resource.QUERY_FIND_DESCENDANTS_POSTGRES, resource);
>+        }
> 
>-        // note - this query is good only to 6 levels deep
>+        // this query is good only to 6 levels deep
>         Query query = entityManager.createNamedQuery(Resource.QUERY_FIND_DESCENDANTS);
>         query.setParameter("resourceId", resource.getId());
>-
>         List<Integer> resourceIds = query.getResultList();
>-
>         return resourceIds;
>     }
> 
>-- 
>1.8.1.2
>

Actions: View | Diff

Attachments on bug 1025918: 870183