Environment -

JBoss 4.0.5 with EJB 3.0 and  Oracle 9.2.0.4

I have a one-to-many bidirectional relation between two entities A and
B. There could be several thousand B entities in the collection. I
basically just need the first and last entities of the collection B
when B is ordered by time. I don't want to load all the B entities
into the app server memory because I know that I need only the first
and last entities in the collection. The problem is that I can't think
of a way to do this in a single query. Here's how I am doing it, first
I fire a query to get entity A and then for each of the entities of A
I fire two queries each to get the first and last entities B in the B
collection. This is turning out to be very slow as if there are 200 A
entities, then 400 additional queries need to be fired to get the two
B entities for each of the A entity. What makes it worse is that I can
determine the first and last B entities that I need only after
ordering the collection by time. So the 400 queries that get fired to
get B entities have 'Order By' in them.

Is there a better way to do this?

Thanks for the help.

Puneet