CMP/CMR Design Question

Discussions

EJB design: CMP/CMR Design Question

  1. CMP/CMR Design Question (2 messages)

    Hi,

    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.

    Customer
    --------
    CustomerId bigint
    CustomerName varchar
    CustomerRef varchar

    Order
    -----
    OrderId bigint
    OrderQuantity int
    OrderDesc varchar
    CustomerId bigint

    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 :-)

    Cheers,
    Rick

    Threaded Messages (2)

  2. CMP/CMR Design Question[ Go to top ]

    Isn't your order table has composite key on (OrderId, CustomerId) if this is true then you don't need another index, if not then you need to add index on customerId
  3. CMP/CMR Design Question[ Go to top ]

    Hi,

    Thanks, I think that's the problem. Composite keys were not used for some reason (I think they break the Weblogic implementation of CMR).

    I think the whole database design is flawed! This type of relationship is present throughout the database design.

    I think I'll need to add some indexes to try and improve the performance.

    Cheers,
    Rick