Getting first and last entities in collection in EJB3

[puneet.bansal]

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

 

[Philipp Taprogge]

Hi!

Thus spake (e-mail address removed) on 06/06/2007 04:44 PM:

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.

As I see it, you only have two options: either you search the DB for those
entities as you are doing now or you add that relationship to your object
model by adding references to the "first" and "last" B to every A and updating
them accordingly whenever the collection is modified (perhaps in a @PrePersist
callback?).
If that's not an option for you, you can only try to optimize the queries you
are using now, perhaps by selecting the Bs for more than one A at a time.

HTH,

Phil

 

[puneet.bansal]

You are right Phil. I did think about adding the first and last
attributes to the A entity but I am hoping that I don't have to make a
change to the design of app as the app is working perfectly and I just
have to optimize the performance. Another option that I am exploring
is writing a native Oracle query. Oracle 9 has very powerful analytic
functions and I am hoping I can utilize those to do all this one
query.

Thanks for replying.
Puneet

 

[puneet.bansal]

I managed to do all this in one query by using Oracle specific query.
But now the problem is that I get multiple rows. For each entity A, I
get two rows. Do you know how to get around this? Ultimately I need
the object A having two B entities in its collection.

Thanks.
Puneet

 

[EricF]

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

You probably should use a native SQL query.

Eric