EJB design: 56 Selects with CMP CRM vs 1 Select with SQL
Consider a database with a Customer, Order and Product table. A customer wants to know how much he has spent. To find this out information is needed from all 3 tables. Assume that the customer has 10 orders with 5 products in each order. Someone has shown me an example of this where a customer had 10 orders, each with 5 products. Using EJB 2.0 CMP Entity Beans with Container Managed Relationships getting back the results resulted in 56 select statements performed by the container (1 for customer, 10 for Orders and 5 for the Products making up the order). Yet this could be done with 1 well written SQL statement.(This was using WebSphere 5.0). Is the preformance really this bad with CRM? Is there a way to make it better?
- Posted by: Bruce Lombardi
- Posted on: March 21 2003 18:14 EST
I guess its a choice inbetween how fine grained the Entities are to be. I've read a number of discussion on this forum relative to the same and they all seem to carry one common statement behind them, Entities need to be coarse.
I guess most developers go through this phase with J2EE, even experienced ones. I dont consider myself highly experienced, but I get confused too when it comes down to this many times.
The Common rule of thumb I tend to use is, If I am trying to report information , I never use Entities to get the data, it is typically through Session Beans using JDBC directly. I can't wait to shift to using JDO and see how the focus changes.
So to answer your question, yes there is a way to make it better, you just stated it. Is performance really really bad with CMP ( I assume it was a typo when you said CRM , cause then we'll have to fight with the architects who designed the solution) , IMHO NO , use it for the right job. Dont use a screwdriver to open a bottle.
Is the preformance really this bad with CRM?
> Is there a way to make it better?
Of course there is. Your assumption here is that each access to
a relationship element is an SQL on its own. But its not the case
if done within a transaction.
e.g.: get customer: 1 SQL query
get x orders of that customer: 1 SQL query
get products for each of the orders: x queries
Instead, you can use an ejbSelect to perform the complex query
in EJB-QL and with EJB 2.1 supporting aggregrate functions such
as MIN, MAX, COUNT, SUM, AVG etc,. things could be done with
an ejbSelect with just one SQL call to the database.
Btw, Borland Enterprise Server already supports these aggregrate
functions as well as sub-queries etc,. in its EJB 2.0 Container.