I have two JPA entities
public class Job {
@ManyToOne
@JoinColumn(name = "service")
public Service service;
@Column(name = "queue_time")
public Long queueTime;
@Column(name = "run_time")
public Long runTime;
}
public class Service {
@Id
@Column(name = "id")
public Long id;
@Column(name = "name")
public String name;
@Column(name = "host")
public String host;
}
Now I want to do some aggregation queries with JPQL:
SELECT job.service.id, AVG(job.queueTime), AVG(job.runTime) FROM Job job GROUP BY job.service.id
The resulting SQL query (I'm using a MySQL database) looks like this:
SELECT t0.id, AVG(t1.queueTime), AVG(t1.runTime) FROM Service t0, Job t1 WHERE (t0.service = t1.id) GROUP BY t0.id
As you can see, JPA translates my JPQL query to a SQL query with a join. This however slows down the query dramatically. The following SQL query executes ~6 time faster and returns the exact same result set:
SELECT t1.service, AVG(t1.queueTime), AVG(t1.runTime) FROM Job t1 GROUP BY t1.service
If I change the JPQL query to
SELECT job.service, AVG(job.queueTime), AVG(job.runTime) FROM Job job GROUP BY job.service
the resulting SQL query looks like this:
SELECT t0.id, t0.name, t0.host AVG(t1.queueTime), AVG(t1.runTime) FROM Service t0, Job t1 WHERE (t0.service = t1.id) GROUP BY t0.id, t0.name, t0.host
Is there a way to write the JPQL which only queries the job table without making a join to the service table?
