Named Query Job.countPerHostService is slow

Steps to reproduce

Our database manager pointed out that this SQL query is using up a lot of time/resources:

SELECT t0.host, t1.service_type, t2.status, COUNT(t2.id)
FROM host_registration t0, job t2, service_registration t1
WHERE (((NOT ((t2.processor_service IS NULL)) AND (t2.processor_service = t1.id))
OR (NOT ((t2.creator_service IS NULL)) AND (t2.creator_service = t1.id))) AND (t1.host_registration = t0.id))
GROUP BY t0.host, t1.service_type, t2.status

and suggested that:

SELECT t0.host, t1.service_type, t2.status, COUNT(t2.id)
FROM host_registration t0
INNER JOIN service_registration t1
ON t0.id = t1.host_registration
INNER JOIN job t2
ON (t2.processor_service = t1.id) OR (t2.creator_service = t1.id)
GROUP BY t0.host, t1.service_type, t2.status

uses one third of the reads and takes one quarter of the duration.

This turns out to be the NamedQuery Job.countPerHostService in JobJpaImpl.java. Which is used to determine the job loads on the hosts. Can the JPQL query be rewritten to something similar more efficient that uses joins? JPQL currently doesn't support JOIN ON though so it might need some lateral thinking.

Status

Assignee

James Perrin

Reporter

James Perrin

Severity

Performance

Tags (folksonomy)

Components

Fix versions

Affects versions

1.4.0

Priority

Major
Configure