Dear Forum,
in my application I have two medium-sized tables (~100.000 records) A and B with a simple [i]many-to-one[/i] relationship:
[i]@Entity[/i]
[b]class[/b] A {
[i]@Id[/i]
[b]int[/b] id;
[i]@Column[/i]
Date date;
[i]@ManyToOne
@JoinColumn(name="b_id")[/i]
B b;
}
[i]@Entity[/i]
[b]class[/b] B {
[i]@Id[/i]
[b]int[/b] id;
}
Now I'm doing the following query:
[b]SELECT a FROM A a
JOIN a.b
WHERE b.type=[i]:type[/i]
ORDER BY a.date DESC[/b]
with setMaxResults(50).
The query is very slow, it takes about a full second on my quite new and shiny server with raid-10. If I execute it in postgreSQL directly, using [b]LIMIT 50[/b] it is much much faster, and even faster if I create an index on the date field.
The [b]LIMIT[/b] clause aparently allows postgres to sort and limit table A before doing the join with B. With toplink it seems to be the other way round: It joins the two large tables together, sorts them and throws away 99% of the results afterwards.
Is there a way to change this behaviour?
Using:
- glassfish v2 ur1
- PostgreSQL 8.2.4
Kind regards
Bastian
[Message sent by forum member 'batzee' (batzee)]
http://forums.java.net/jive/thread.jspa?messageID=263601