RE: Java and Relations DBs..

Brad,

Your problem might be that you are executing too many individual queries
when performing this operation. I am assuming that the method
invoiceList.loadByCustomerNumber is performing the database operation to
load the invoices for the given customer. Just the fact that you are
performing a database operation no matter how many records are returned adds
an enormous amount of overhead to this process.

The model that you are following will be affected by the growth of your
database. The more customers the more queries you execute and the more
invoices you have the more data is loaded just for the sake of counting.

When creating the type of object that you are working with you must be
careful not to have to load the whole database into memory just for the sake
of performing a simple operation such as counting the number of invoices.
Add static helper methods to your customer and or invoice classes that would
perform this type of operation as a single SQL query. Only return the fields
that you need in that query.

If you are creating an object model to just be a memory representation of
the database then you will have problems. The application should never be
designed to load all the data into memory in order to perform an operation.

Etienne

-----Original Message-----
From: Brad Stone [mailto:brad@bvstools.com]
Sent: Wednesday, January 09, 2002 12:30 PM
To: java400-l@midrange.com
Subject: Java and Relations DBs..


Hi guys.  For those of you that are using Java for your
business systems, I have a question.

Let's assume you have a customer table.  You also have an
invoice header table.  You also have classes for Customer,
CustomerList (a list of customers), Invoice, and InvoiceList
(a list of invoices).

Now let's assume that you wanted to print a count of the
number of invoices for each customer.  It would be something
like..

CustomerList customerList = new CustomerList();
customerList.load();

for(int i=0; i<customerList.size(); i++) {
  Customer customer = customerList.getCustomer(i);
  int number = customer.getNumberOfInvoices();
  System.out.println(customer.getCustomerNumber() + ":" +
number);
}

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.

Any other ideas for this type of procedure?  I plan on
moving this type of application to the AS/400 in the future.
I will be using JDBC and SQL for data access (loading of my
container classes).

I could see many other scenarios similar to this because of
how our relational DBs are build.  Getting the number of
detail lines on an invoice, etc.. just because of how our
DBs are designed.

Thanks!
_______________________________________________
This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
mailing list
To post a message email: JAVA400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/java400-l
or email: JAVA400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.