According to your query you use IDs instead of references, which is not recommended when using JPA. It seems that a Meta instance holds the IDS of an Image and a Page instances instead of direct references.
A more JPA conventional variant of your query would be:
SELECT count(e) FROM Meta e, ImageNet i, Page p
WHERE e.image=i AND e.page=p
Anyway, this still doesn't justify execution time of hours.
Since you didn't provide a test case (as requested in the posting instructions) it is impossible to see what is exactly the problem. Maybe the id fields in Page and Image are not annotated with @Id or @Index. In that case, executing the JOIN may require iteration over 160,000 x 160,000 x 160,000 tuples.
The following test was written following your post to demonstrate queries which are similar to your query:
import javax.persistence.*;
public class T474 {
@SuppressWarnings("boxing")
public static void main(String[] args) {
EntityManagerFactory emf =
Persistence.createEntityManagerFactory(
"objectdb:$objectdb/db/test.tmp;drop");
EntityManager em = emf.createEntityManager();
int entityCount = 160000;
System.out.println("Database population started.");
em.getTransaction().begin();
for (int i = 0; i < entityCount; i++) {
B b = new B();
em.persist(b);
C c = new C();
em.persist(c);
em.persist(new A(b, c));
}
em.getTransaction().commit();
System.out.println("Database population completed.");
long ms, result, elapsedTime;
System.out.println("Query 1 started.");
ms = System.currentTimeMillis();
TypedQuery<Long> q1 = em.createQuery(
"SELECT COUNT(a) FROM A a, B b, C c " +
"WHERE a.bId = b.id AND a.cId = c.id", Long.class);
result = q1.getSingleResult();
elapsedTime = System.currentTimeMillis() - ms;
System.out.println("Query 1 completed in " + elapsedTime +
" milliseconds (" + result + ")");
System.out.println("Query 2 started.");
ms = System.currentTimeMillis();
TypedQuery<Long> q2 = em.createQuery(
"SELECT COUNT(a) FROM A a, B b, C c " +
"WHERE a.b = b AND a.c = c", Long.class);
result = q2.getSingleResult();
elapsedTime = System.currentTimeMillis() - ms;
System.out.println("Query 2 completed in " + elapsedTime +
" milliseconds (" + result + ")");
em.close();
emf.close();
}
@Entity
public static class A {
@Id @GeneratedValue long id;
B b;
long bId;
C c;
long cId;
public A() {
}
public A(B b, C c) {
this.b = b;
this.bId = b.id;
this.c = c;
this.cId = c.id;
}
}
@Entity
@SequenceGenerator(name="seqB", allocationSize=1000)
public static class B {
@Id @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="seqB")
long id;
}
@Entity
@SequenceGenerator(name="seqC", allocationSize=1000)
public static class C {
@Id @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="seqC")
long id;
}
}
The result output is:
Database population started.
Database population completed.
Query 1 started.
Query 1 completed in 1594 milliseconds (160000)
Query 2 started.
Query 2 completed in 109 milliseconds (160000)
The first query uses a JOIN on ID fields, similarly to your original query. The second query uses JOIN on direct references. The second form is simpler, faster and more appropriate when using JPA.
ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)