I have a table A that has many children of table B. By looking at production data, every A has around 100 rows in table B. Let's say that I want to query all the A's with just the first and last B's for each A (the highest and lowest values).

What is the best way to go about doing that using HQL? Should I denormalize those values and put them into table A, or is there a solution that I can use to make this fast without loading every B in the database?

Thanks for sharing your experience with this kind of problem with me since I'm no database expert.

The reason this is a problem is that sometimes I need to load 30 or 40 objects of type A. If they all have 100 children, that's 3000 to 4000 B's being loaded, which is unacceptable even with fetch joining. Since I only need the first and last B of each A (which would reduce the rows from 3000/4000 to 60/80), I would really like to learn how to write such a query to speed things up.

If there is no such query, should I just create redundant pointers to the first and last B on object A?

You can map the collection as lazy, and then use a filter or use a where-mapping if you really only need those two elements all the time.

Michael, thanks very much for your quick response.

Would I execute several queries, one for all A's and then seperate queries to fetch the first and last B for each A? Or can I do this all in a single HQL query?

I'm not the great when it comes to SQL. When I fiddled with this and tried using min() and max(), I got back the same min and max for the entire table instead for each A. Or do I need to do some weird sub-query thing? Thanks again for your help.

You can probably query for [A, max B, min B] using HQL but you can not load an A with only those two elements in the colletion. You can however use a second filter query to filter the collection.

Thanks, I believe I got it to work in about 20 milliseconds now.