reducing database roundtrips

jwenting · **Joined:** Tue Jan 03, 2006 9:20 am **Posts:** 74

this may be more a JPA question than purely Hibernate related.
Say I have an @Entity A which contains a reference to another @Entity B (or worse, a List of other @Entitys).
JPA will generate first a query to retrieve A:

Code:

SELECT ID, FIELD1, B_ID FROM A WHERE ID=:ID

After which it will generate another query to retrieve the matching B:

Code:

SELECT ID, FIELD1 FROM B WHERE ID=:ID

passing in the retrieved ID.

-- SQL not identical to generated, just for documentation

Is there any way to optimise this to the SQL any human would write?:

Code:

SELECT A.ID, A.FIELD1, B.ID, B.FIELD1 FROM A A, B B WHERE B.ID = A.B_ID

Would save a lot of database roundtrips, which can be a real performance boost.

Cameron McKenzie · **Posted:** Mon Aug 10, 2009 6:44 am

There's no simple way to optimize this that won't overly complicate your mapping. You could issue HQL I guess?

Remember, this all happens inside one, single transaction, so you may have extra SQL, but not extra transactions, which is what you'd get if you just did it all by hand. It's actually fairly efficient. I think your bottlenecks will be in other locations, not here. If you believe in the 80/20 rule about performance optimization, you'll see that this isn't where to focus your attention.

-Cameron McKenze

Cameron McKenzie · **Posted:** Mon Aug 10, 2009 6:44 am

ooops...

jwenting · **Joined:** Tue Jan 03, 2006 9:20 am **Posts:** 74

Thanks. I've been able to reduce things a bit by putting explicit joins on some tables into the jpql.
Won't do that for everything because of the complexity it would create of course (and because jpql doesn't support nested joins), but it reduces the overhead from big selects somewhat.