Entity Perfomance(The Proper way)

[smileyq]

I am new to entity beans and have been working to figure out the proper method to use entity beans in a web based application. Currently I have created a Stateless Session Bean to work with the Local Entity beans. I am noticing performance issues when using relationships between two Entity Beans. For example if I call customer.getOrders() which returns a collection of all related Order entity beans and try to iterator of them it seems to appear much slower than by using straight JDBC. Below is some sample code from the Session Bean. Thanks for any help to set me on the right path.

 public java.util.Collection getOrders(int clientId)
 ClientException
 {
 ArrayList orderList = new ArrayList();
 try
 {
 Context ctx = new InitialContext();
 Object obj = ctx.lookup(ClientLocalHome.JNDI_NAME);

 // Get the home interface
 ClientLocalHome home =
 (ClientLocalHome)PortableRemoteObject.narrow(obj, ClientLocalHome.class);

 // Get the stub
 ClientLocal client =
 home.findByPrimaryKey(new ClientPK(new Integer(clientId)));

 // Get the Collection of orders
 Iterator iter = client.getOrders().iterator();
 while(iter.hasNext())
 {
 Order order = new Order();
 OrdersLocal local = (OrdersLocal)iter.next();
 order.setOrderId(local.getOrderId().intValue());
 order.setOrderDate(local.getOrderDate());
 order.setClient(client.getName());
 order.setTotalRevenue(local.getTotalRevenue()
 .doubleValue());
 // Add the orderDO to the list
 orderList.add(order);
 }


 }
 catch(Exception e)
 {
 throw new ClientException("Unable to retreive orders. Cause: "
 + e.getMessage());
 }

 // Return the collection of orders
 return(orderList);
 }

[jamesstrachan]

Presumably you are using CMP.

My guess is that the client.getOrders().iterator() statement is getting a set of Primary Keys for the order.

As you progress through the iterator, each order in turn is loaded in a separate SQL statement.

So, for n orders, the application is making n+1 SQL operations.

You can prove this if you can turn on statement tracing in the database layer.

You will probably find that you need to go back to a direct JDBC call to get reasonable performance.

Entity Beans work best when the proportion of reads to writes is high.

So they work really well on reference data (order types, delivery methods, etc.), and work well on tasks like amending an order.

But they are not so helpful when, as in your example, you are reading a small portion of the underlying data and not referencing the data again.

James

[smileyq]

Is it good programming pratice to mix and match EJB/CMP together along with JDBC in your application? For example use JDBC for most of my listing of for example clients orders etc.. and use entity beans to modify the orders, clients or at an order to the client etc.. Would this cause problems with the persisitence layer? Thanks for your input it is really appreciated.