We have a number CMP EJB's linked via CMR within WL81.SP4. The performance doesn't seem too great in my opinion, and seems to be getting progressively worse.
I suspect that it is Database Design related (SQLServer 2000) rather than the actual Weblogic server.
I'm not too hot on D/B design, but I suspect I may have found at least part of the problem.
For example, imagine we have the usual Customer -> Order relationship (One-Many) with the following columns.
Obviously, the relationship is on the CustomerId.
Over time, we will get a gradual increase in the of customers, and a bigger increase in the number of orders. For example, we have 1000 customers with 100,000 orders.
Within our CustomerEJB, we have a method getOrders(), which returns a collection of all the orders for a particular customer.
Now, to my real question.....
On the database, there is a relationship between Customer & Order. However, there is not an index present on the CustomerId column of the Order table. When we access the getOrders() method, will this effectively table scan the whole of the Order table matching the CustomerId, or is it more clever that this?
I suspect that this is happening, and this is why things are getting progressively worse. I'm wondering whether we need to start adding indexes to all the 'Many' end of a 'One-Many' relationships to ensure that table scans to not occur.
Thanks for listening, hope you can help :-)