Bugger. It's too late to be in the office, it's hot and I'm tired.
I'll try again :
PersistenceBroker broker = ...;
Long customerId = new Long(1);
/* Query/Select 1 : gets all the car parts for the customer */
Criteria c1 = new Criteria();
c.addEqualTo("carModelInfo.carInfo.invoice.customer.id", customerId);
Query q1 = new QueryByCriteria(CarAddedParts.class, c);
Collection parts = broker.getCollectionByQuery(q1);
/* Query/Select 2 : gets all the modelInfos for the customer */
Criteria c2 = new Criteria();
c.addEqualTo("carInfo.invoice.customer.id", customerId);
Query q2 = new QueryByCriteria(CarModelInfo.class, c);
Collection modelInfos = = broker.getCollectionByQuery(q2);
/* Total results from Q1 */
BigDecimal totalParts = new BigDecimal("0");
for (Iterator it = parts.iterator(); iter.hasNext();) {
CarAddedPart part = (CarAddedPart) it.next();
totalParts = totalParts.add(part.getWeight);
}
/* Total results from Q2 */
BigDecimal totalModels = new BigDecimal("0");
for (Iterator it = modelInfos.iterator(); iter.hasNext();) {
CarInfoModel model = (CarInfoModel) it.next();
totalModels = totalModels.add(model.getWeight);
}
BigDecimal totalWeight = totalParts.add(totalModels);
Any remaining errors can
Using a running total inside the code instead of inside the database is a
habit from my particular problem domain (where we often have to do currency
conversions, so we can't do SUM() in the database). Ron's will be far
quicker.
I'm going home now (Ah, the London Underground, Rush Hour, High Temperatures
and High Humidity. Gotta love it.)
Cheers,
Charles.
>-----Original Message-----
>From: Charles Anthony [mailto:[EMAIL PROTECTED]
>Sent: 10 July 2003 17:52
>To: 'OJB Users List'
>Subject: RE: Optimization advice/help.
>
>
>Hi Josh,
>
>Assuming (and this assumption is a bit dodgy) "weight" is stored for
>CarAddedParts and CarModelInfo, and that the weight of a given car =
>CarModelInfo.weight + all CarAddedParts.weight
>
>Long customerId = new Long(1);
>
>Criteria c1 = new Criteria();
>c.addEqualTo("carModelInfo.carInfo.invoice.customer.id", customerId);
>Collection parts = QueryByCriteria(CarAddedParts.class, c);
>
>Criteria c2 = new Criteria();
>c.addEqualTo("carInfo.invoice.customer.id", customerId);
>Collection models = QueryByCriteria(CarModelInfo.class, c);
>
>BigDecimal totalParts = new BigDecimal("0");
>for (Iterator it = parts.iterator(); iter.hasNext();) {
> CarAddedPart part = (CarAddedPart) it.next();
> totalParts = totalParts.add(part.getWeight);
>}
>
>BigDecimal totalModels = new BigDecimal("0");
>for (Iterator it = models.iterator(); iter.hasNext();) {
> CarInfoModel model = (CarInfoModel) it.next();
> totalModels = totalModels.add(model.getWeight);
>}
>
>BigDecimal totalWeight = totalParts.add(totalWeights);
>
>Ron's just replied with an equally sensible solution; seeing
>how I've just
>spent 5 mins working this out, I thought I'd press send anyway.
>
>Cheers,
>
>Charles.
>
>>-----Original Message-----
>>From: Josh Berry [mailto:[EMAIL PROTECTED]
>>Sent: 10 July 2003 17:30
>>To: [EMAIL PROTECTED]
>>Subject: Optimization advice/help.
>>
>>
>>I don't want this to just be a performance question, but I
>>must admit that
>>that is at the heart of my problem.
>>
>>First, a little background on my problem. I will try to work
>>this into a
>>standard shopping cart/customer problem, but it may prove difficult.
>>
>>So... Lets say I have the following classes:
>>
>> *Customer
>> A person buying a car. Obviously many of these exist.
>> *Invoice
>> An individual record of a single customer buying one or
>more cars.
>> Many of these can exist per customer.
>> *CarInfo
>> A total breakdown of a vehicle to be ordered.
>> *CarMakeInfo
>> Basic information on a broad category of vehicle. (e.g.
>>Ford Mustang)
>> *CarModelInfo
>> Specific information on a vehicle.
>> (e.g. 4.0 liter engine, manual locks, dimensions, etc.)
>> *CarCustomizationsInfo
>> Any special changes that a customer requested for the vehicle.
>> This is basically a set of overrides to CarModelInfo. So, either
>> it exists or it does not.
>> (e.g. Dimensions changed due to added roof lights,
>> power locks added.)
>> *CarAddedParts
>> Any of a number of additional items to be added to the vehicle.
>> (e.g. the roof lights )
>>
>>
>>Now... I believe these will be all that is needed.
>>
>>The relationships are as follows.
>>
>> Customer has a 1:M with Invoice.
>> Invoice has a 1:M with CarInfo.
>> CarInfo has a 1:1 with CarMakeInfo.
>> CarInfo has a 1:1 with CarModelInfo.
>> CarInfo has a 1:1 with CarCustomizationsInfo.
>> CarInfo has a 1:M with CarAddedPartsInfo.
>>
>>
>>So, the problem I have is this:
>>
>>We currently do reports where we need to know the total weight
>>of everything a
>>customer has ever ordered. Now, it is clean code with OJB, as
>>I can just loop
>>through all of a customer's invoices calling getWeight for
>>every CarInfo.
>>However, this has performed abysmally slowly.
>>
>>Now, my guess is that OJB does one select to get all of the
>>Invoices. Then,
>>it will do another select per Invoice to get all of the CarInfos.
>>Then, for each CarInfo, it will do 3 selects to get each of
>>CarMakeInfo,
>>CarModelInfo, and CarCustomizationsInfo.
>>In addition, it will do another select per CarInfo to get
>>CarAddedPartsInfo.
>>
>>For a single customer with X invoices averaging Y CarInfos
>that have on
>>average Z CarAddedParts, this yields approximately 1 + X + (4
>>* Y) selects.
>>Obviously, this is a lot of selects to happen once you start
>>doing this over
>>many Invoices that have many CarInfos with lots of AddedParts.
>> And when my
>>numbers average to something like 1000 invoices with about 80
>>parts a piece,
>>this starts getting out of hand quickly.
>>
>>So, the problem I am facing is I do not know a way to avoid
>>all of these
>>selects with OJB. Writing it on my own, I am able to reduce
>>the number down
>>to a fixed 4 selects. I could probably widdle it down more,
>>but I would
>>prefer some maintainability of the code, which is why I would
>>like to keep
>>OJB. Does anyone know of any tricks?
>>
>>Basically, I think my problem is when you have a M:N:O
>>relationship that needs
>>to be looped through. (Also note: in the actual problem I
>have, I have
>>several of these relationships in existance. Think of it as
>>though I had
>>seperate cases for Cars and Trucks, with a few differences
>>between them.)
>>
>>Finally, I thank anyone who had the patience to read through
>>this example and
>>question. Hopefully, I made it simple enough to make sense
>>without being
>>useless.
>>
>>Thanks,
>>
>>-josh
>>
>>
>>
>>---------------------------------------------------------------------
>>To unsubscribe, e-mail: [EMAIL PROTECTED]
>>For additional commands, e-mail: [EMAIL PROTECTED]
>>
>
>
>This email and any attachments are strictly confidential and
>are intended
>solely for the addressee. If you are not the intended
>recipient you must
>not disclose, forward, copy or take any action in reliance on
>this message
>or its attachments. If you have received this email in error
>please notify
>the sender as soon as possible and delete it from your
>computer systems.
>Any views or opinions presented are solely those of the author
>and do not
>necessarily reflect those of HPD Software Limited or its affiliates.
>
> At present the integrity of email across the internet cannot
>be guaranteed
>and messages sent via this medium are potentially at risk.
>All liability
>is excluded to the extent permitted by law for any claims
>arising as a re-
>sult of the use of this medium to transmit information by or to
>HPD Software Limited or its affiliates.
>
>
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: [EMAIL PROTECTED]
>For additional commands, e-mail: [EMAIL PROTECTED]
>
This email and any attachments are strictly confidential and are intended
solely for the addressee. If you are not the intended recipient you must
not disclose, forward, copy or take any action in reliance on this message
or its attachments. If you have received this email in error please notify
the sender as soon as possible and delete it from your computer systems.
Any views or opinions presented are solely those of the author and do not
necessarily reflect those of HPD Software Limited or its affiliates.
At present the integrity of email across the internet cannot be guaranteed
and messages sent via this medium are potentially at risk. All liability
is excluded to the extent permitted by law for any claims arising as a re-
sult of the use of this medium to transmit information by or to
HPD Software Limited or its affiliates.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
