How do I do this query using JPA so that it is not very slow?

Question

How do you join across multiple tables in an efficient way using JPQL

select a.text,b.text,c.text,
from Class1 a, Class2 b, Class3 c
where a.id=b.b_id and b.id=c.b_id and a.text like ... and b.text like ...

I am doing something like this, the tables only have a few thousand rows, yet the query takes 5-6 seconds to run. I assume it is joining all of the tables before doing the filter

I know the speed may be JPA vendor implementation specific, but I suspect this is not the proper way to write this query!

Answer 1

See what SQL query has been generated. Then EXPLAIN that query and try to optimize it. For example, make sure you have proper indices.

Answer 2

If you don't like the SQL which JPA is generating (but I doubt its generating "bad" SQL) you can always use a Native Query.