Re: [Re: Optimization advice/help.]
As an addition to my situation.
It dawned on me earlier that I could be taking advantage of the ISNULL
function to accomplish this in a straight SELECT statement. (e.g. SELECT
sum(ISNULL( c.WEIGHT, b.WEIGHT )) from invoices a LEFT JOIN carModels b ON
a.CarModelID = b.ID LEFT JOIN carCustomizations c ON a.custimazationsID =
C.ID) (At least, I am pretty confident that would work.)
Now, I am not sure how much faster the reports stuff is at making the SQL
well, but I would guess it has to be faster than running the IteratorByQuery.
(For the record, it currently takes close to 40 minutes. Yeah, there are a
lot of other links I am leaving out of this example. :) )
Basically, I am wondering if there is a way for the reports stuff to have
access to the ISNULL SQL function? I see it quite clearly supports the sum()
function, but that is probably a bit more common than the ISNULL.
(Specifically, I am pretty sure that ISNULL is a Transact-SQL thing.)
-josh
Ron Gallagher <[EMAIL PROTECTED]> wrote:
> Josh --
>
> My response is based on the following assumptions:
>
> 1) the 'weight' is defined as a property on the CarAddedParts class
> 2) The list of invoices that a customer has is provided by the 'invoices'
property
> 3) The list of cars that are included on an invoice is provided by the
'cars' property
> 4) The list of additional parts for a car is provided by the
'additionalParts' property
> 5) All of the 'lists' in 2-4 are defined in your repository.
>
> If those assumptions are correct, then the following will do what you're
looking for.
>
> PersistenceBroker broker = ...;
> Criteria criteria = new Criteria();
> criteria.addEqualTo("customerId", new Long(1));
> ReportQueryByCriteria query =
> new ReportQueryByCriteria(
> Customer.class,
> new
String[]{"sum(invoices.cars.additionalParts.weight)"},
> criteria);
> Iterator iterator = broker.getReportQueryIteratorByQuery(query);
> while (iterator.hasNext()) {
> Object[] data = (Object[]) iterator.next();
> // the sum will be in data[0]
> }
> if ((iterator != null) && (iterator instanceof OJBIterator)) {
> OJBIterator ojbIterator = (OJBIterator) iterator;
> ojbIterator.releaseDbResources();
> }
>
> Please note...both the compiler and unit tester on my email client are not
working today, so there may be some syntax errors.
>
> Ron Gallagher
> Atlanta, GA
> [EMAIL PROTECTED]
>
> >
> > From: Josh Berry <[EMAIL PROTECTED]>
> > Date: 2003/07/10 Thu PM 12:30:29 EDT
> > 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:
> >
> &
Re: [RE: Optimization advice/help.]
Thanks for all responses so far. It looks like I did leave out one very
important clarification.
For CarInfo, the getWeight method is coded by me. Since CarCustomizationsInfo
is optional, the base weight will come from either there or from the
CarModelInfo class. Taking the base weight, the total weight is then
calculated by adding all of the individual CarAddedParts weights.
So I do not know of any way to do this using the reports stuff. At
least, not without shooting myself in the foot when it comes to readability.
(I had thought to do a custom SQL to accomplish this, but the logic there
makes it more than a simple select, so I've ditched that idea as my bosses
don't want stored procs.)
To clarify: If a CarInfo has a CarCustomizationInfo, then the base weight
comes from there; otherwise, the base weight comes from the CarModelInfo. This
is what keeps me from being able to use an OJB report (or a single select, for
that matter).
-josh
Charles Anthony <[EMAIL PROTECTED]> wrote:
> 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.
>
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
RE: Optimization advice/help.
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 b
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, wi
Re: Optimization advice/help.
Josh --
My response is based on the following assumptions:
1) the 'weight' is defined as a property on the CarAddedParts class
2) The list of invoices that a customer has is provided by the 'invoices' property
3) The list of cars that are included on an invoice is provided by the 'cars' property
4) The list of additional parts for a car is provided by the 'additionalParts' property
5) All of the 'lists' in 2-4 are defined in your repository.
If those assumptions are correct, then the following will do what you're looking for.
PersistenceBroker broker = ...;
Criteria criteria = new Criteria();
criteria.addEqualTo("customerId", new Long(1));
ReportQueryByCriteria query =
new ReportQueryByCriteria(
Customer.class,
new String[]{"sum(invoices.cars.additionalParts.weight)"},
criteria);
Iterator iterator = broker.getReportQueryIteratorByQuery(query);
while (iterator.hasNext()) {
Object[] data = (Object[]) iterator.next();
// the sum will be in data[0]
}
if ((iterator != null) && (iterator instanceof OJBIterator)) {
OJBIterator ojbIterator = (OJBIterator) iterator;
ojbIterator.releaseDbResources();
}
Please note...both the compiler and unit tester on my email client are not working
today, so there may be some syntax errors.
Ron Gallagher
Atlanta, GA
[EMAIL PROTECTED]
>
> From: Josh Berry <[EMAIL PROTECTED]>
> Date: 2003/07/10 Thu PM 12:30:29 EDT
> 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 kn
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]
