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:
> >
> > 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]
> >
> >
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
