+++ This bug was initially created as a clone of Bug #1207816 +++

Description of problem:

The System Overview page in the Satellite web UI can take a long time (~2 minutes) to load, when the systems have a large number of unscheduled errata. For example, on a Satellite with 84 systems, where 37 of them have over 1,000 available but unscheduled errata, the page takes 154 seconds to load (with session timeouts increased to avoid Internal Server Errors). 103 of those 154 seconds are spent creating instances of the com.redhat.rhn.domain.errata.impl.PublishedErrata class that are never actually used. The problem is in the query used:

****
./java/code/src/com/redhat/rhn/manager/system/SystemManager.java:

    public static boolean hasUnscheduledErrata(User user, Long sid) {
        SelectMode m = ModeFactory.getMode("Errata_queries",
                "unscheduled_relevant_to_system");
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("user_id", user.getId());
        params.put("sid", sid);
        DataResult dr = m.execute(params);
        return !dr.isEmpty();   <--- returns True or False, depending on whether there are any unscheduled errata for the system
    }

./java/code/src/com/redhat/rhn/common/db/datasource/xml/Errata_queries.xml:

<mode name="unscheduled_relevant_to_system" class="com.redhat.rhn.domain.errata.impl.PublishedErrata">
  <query params="user_id, sid">
SELECT DISTINCT E.id, E.update_date
  FROM rhnErrata E,
       rhnServerNeededPackageCache SNPC
 WHERE EXISTS (SELECT server_id FROM rhnUserServerPerms USP WHERE USP.user_id = :user_id AND USP.server_id = :sid)
   AND SNPC.server_id = :sid
   AND SNPC.errata_id = E.id
   AND NOT EXISTS (SELECT SA.server_id
                     FROM rhnActionErrataUpdate AEU,
                          rhnServerAction SA,
                          rhnActionStatus AST
                    WHERE SA.server_id = :sid
                      AND SA.status = AST.id
                      AND AST.name IN('Queued', 'Picked Up')
                      AND AEU.action_id = SA.action_id
                      AND AEU.errata_id = E.id )
ORDER BY E.update_date, E.id
  </query>
  <elaborator name="published_overview" params="">
    SELECT  E.id, E.advisory, E.advisory_name, E.advisory_type, E.update_date, E.synopsis
    FROM  rhnErrata E
    WHERE  E.id IN (%s)
    ORDER BY  E.update_date DESC
  </elaborator>
</mode>
****

unscheduled_relevant_to_system creates a class instance for each errata record returned by the query, but SystemManager.hasUnscheduledErrata() simply checks whether the result is empty, and does not use these instances. 

Version-Release number of selected component (if applicable):

spacewalk-java-2.3.8-100.el6sat.noarch

How reproducible:

100%

Steps to Reproduce:
1.) Register a RHEL 6.0 system ~50 times, subscribing it to the rhel-x86_64-server-6 base channel.
2.) Navigate to the System Overview page.
3.) Notice the slow page load.

Actual results:

Slow System Overview page load when systems have many available errata.

Expected results:

Not-so-slow page load.

Additional info:


unscheduled_relevant_to_system is used in other code that needs the errata objects, so creating a second query that returns only the total errata count may be required to fix this. In my testing, the following patch resolved the issue:

# diff -pruN java/code/src/com/redhat/rhn/manager/system/SystemManager.java.bak java/code/src/com/redhat/rhn/manager/system/SystemManager.java
--- java/code/src/com/redhat/rhn/manager/system/SystemManager.java.bak	2015-03-31 14:51:17.984634294 -0400
+++ java/code/src/com/redhat/rhn/manager/system/SystemManager.java	2015-03-31 14:51:58.354443219 -0400
@@ -943,12 +943,13 @@ public class SystemManager extends BaseM
      */
     public static boolean hasUnscheduledErrata(User user, Long sid) {
         SelectMode m = ModeFactory.getMode("Errata_queries",
-                "unscheduled_relevant_to_system");
+                "count_unscheduled_relevant_to_system");
         Map<String, Object> params = new HashMap<String, Object>();
         params.put("user_id", user.getId());
         params.put("sid", sid);
-        DataResult dr = m.execute(params);
-        return !dr.isEmpty();
+        DataResult<Map<String, Object>> dr = makeDataResult(params, null, null, m);
+        return ((Long) dr.get(0).get("count")).intValue() > 0;
+
     }
 
     /**

# diff -pruN java/code/src/com/redhat/rhn/common/db/datasource/xml/Errata_queries.xml.bak java/code/src/com/redhat/rhn/common/db/datasource/xml/Errata_queries.xml
--- java/code/src/com/redhat/rhn/common/db/datasource/xml/Errata_queries.xml.bak	2015-03-31 14:53:28.367017151 -0400
+++ java/code/src/com/redhat/rhn/common/db/datasource/xml/Errata_queries.xml	2015-03-31 14:53:43.854943835 -0400
@@ -301,6 +301,26 @@ ORDER BY E.update_date, E.id
   </elaborator>
 </mode>
 
+<mode name="count_unscheduled_relevant_to_system">
+  <query params="user_id, sid">
+ SELECT COUNT(DISTINCT E.id) AS COUNT
+   FROM rhnErrata E,
+        rhnServerNeededPackageCache SNPC
+  WHERE EXISTS (SELECT server_id FROM rhnUserServerPerms USP WHERE USP.user_id = :user_id AND USP.server_id = :sid)
+    AND SNPC.server_id = :sid
+    AND SNPC.errata_id = E.id
+    AND NOT EXISTS (SELECT SA.server_id
+                      FROM rhnActionErrataUpdate AEU,
+                           rhnServerAction SA,
+                           rhnActionStatus AST
+                     WHERE SA.server_id = :sid
+                       AND SA.status = AST.id
+                       AND AST.name IN('Queued', 'Picked Up')
+                       AND AEU.action_id = SA.action_id
+                       AND AEU.errata_id = E.id )
+  </query>
+</mode>
+
 <mode name="issued_between">
   <query params="start_date_str, end_date_str">
 SELECT DISTINCT

--- Additional comment from Jan Dobes on 2015-04-02 07:33:24 EDT ---

see also bz1205328 where is now used select from rhnServerNeededErrataCache instead of rhnServerNeededPackageCache

--- Additional comment from Stephen Herr on 2015-04-22 13:36:18 EDT ---

Thanks as always for the excellent investigation and patch Tasos.

I approve the patch (after making the rhnServerNeededPackageCache -> rhnServerNeededErrataCache change that Jan pointed out) and verify that it improves the loading speed of that page (50 rhel 6.0 server subscribed to rhel channel, 27s page load before the patch, ~4s after) (local SSD hard drives are fast).