Stats queries are much too slow

The admin stats uses a named query to get the total number of operations have done.

This named query is in ServiceRegistrationJpaImpl.java, line ServiceRegistration.hostload and is called by ServiceRegistryJpaImple.java on line 986.

This query expands through JPA to the following:

SELECT t0.ID, t0.JOB_PRODUCER, t0.PATH, t0.SERVICE_TYPE, t0.ONLINE, t0.host_reg, t1.STATUS, COUNT(t1.STATUS) FROM SERVICE_REGISTRATION t0 LEFT OUTER JOIN JOB t1 ON (t1.processor_svc = t0.ID), HOST_REGISTRATION t2 WHERE (((t0.ONLINE = 1) AND (t2.MAINTENANCE = 0)) AND (t2.ID = t0.host_reg)) GROUP BY t0.ID, t0.JOB_PRODUCER, t0.PATH, t0.SERVICE_TYPE, t0.ONLINE, t0.host_reg, t1.STATUS

In our production system this query takes 50 seconds to run. And it is called every ten seconds or so if an admin tab is open.

This query can be reduced to:

select * from SERVICE_REGISTRATION serv, (select processor_svc, status, count(status) from JOB group by processor_svc, status) as stats where serv.ID=stats.processor_svc

Which runs in around 12 seconds.

This second query isn't quite the same as the first, it does not return those items that have null in the status column. These items are ignored by the Java code anyway, as per the following:

List queryResults = query.getResultList();
for (Object result : queryResults) {
Object[] oa = (Object[]) result;
ServiceRegistrationJpaImpl serviceRegistration = ((ServiceRegistrationJpaImpl) oa[0]);
Status status = ((Status) oa[1]);
Number count = (Number) oa[2];
Number meanQueueTime = (Number) oa[3];
Number meanRunTime = (Number) oa[4];

// The statistics query returns a cartesian product, so we need to iterate over them to build up the objects
JaxbServiceStatistics stats = statsMap.get(serviceRegistration);
if (stats == null) {
stats = new JaxbServiceStatistics(serviceRegistration);
statsMap.put(serviceRegistration, stats);
}
// the status will be null if there are no jobs at all associated with this service registration
if (status != null) {
switch (status) {
case RUNNING:
stats.setRunningJobs(count.intValue());
break;
case QUEUED:
case DISPATCHING:
stats.setQueuedJobs(count.intValue());
break;
case FINISHED:
stats.setMeanRunTime(meanRunTime.longValue());
stats.setMeanQueueTime(meanQueueTime.longValue());
break;
default:
break;
}
}
}