RE: Java and Relations DBs..

["Larry Loen" <lwloen@xxxxxxxxxx>]

>The getNumberOfInvoices method inside of the Customer class
>looks something like this:

>public int getNumberOfInvoices() {
>  InvoiceList invoiceList = new InvoiceList();
>  invoiceList.loadByCustomerNumber(this.getCustomerNumber());
>  return invoiceList.size();
>}

>I've been doing something similar to this and am finding the
>performance to be quite horrid.  Is there a better approach
>that could be used?  The app I am working on is using a
>600mhz PC with 128meg Memory and a local MS Access DB using
>JDBC to retrieve data.

>I'm wondering where the bottleneck could be.  Is it creating
>all of the InvoiceList objects inside of the
>getNumberOfInvoices() method?  DB access normally seems
>pretty fast, so I don't think that's a problem.

I agree with Etienne Richards here.  Find out what is happening and, as
important, going to happen.  You need to know how many DB records are going
to be fetched and how often based on what your application asks for.  That
will predict your performance.  It isn't important how fast the DB is
generally -- the question is "how many records am I fetching?"

The cost may grow to M queries times N customers times P invoices
(customarily set to a single number, N cubed, in the academic literature).
This is still polynomial performance, but not what most people want to see
in a "bread and butter" application.  It means every customer, daily query,
and invoice you add makes things much worse.  If you can hold onto the Java
objects as the table is altered, then it is "only" N times P, but are you
doing that now?

Is any of this true?  If it is, there's some ploys you can try, such as
SELECT COUNT(*) FROM TABLENAME that should work well on OS/400 (and, at
least avoids creating all the Java objects).  What you need to find is ways
to keep from dragging in all the records of the table to make the count.
You especially need to avoid dragging the data over the wire if this ends
up a client/server implementation.  That can possibly be done with SELECT
COUNT(*) or it might be done in more complex cases with data base triggers
updating customer and invoice counts either globally or in the customer
record for invoices "by customer" as you insert, delete, and update.



Larry W. Loen  -   Senior Linux, Java, and iSeries Performance Analyst
                          Dept HP4, Rochester MN