Re: Java and Relations DBs..

Brad,

Like others have said, reducing the amount of data with a
count(*) column function will help a lot. You might also look
into creating a view that provides the count. Also, are you using
some sort of connection pooling? Establishing a new connection
can take a while. I haven't used Access enough to give any
pointers, but you might look to see if there are any performance
related options like read-only.

David Morris

>>> brad@bvstools.com 01/09/02 11:04AM >>>
Yes, I could do that, but that is essentially what that
loadByCustomerNumber(customerNumber) method is doing.

I could try it, but I keep all my SQL in one class so that I
don't have SQL everywhere.  I guess I could modify it to
retrieve a record count instead of the data.

Brad
www.bvstools.com

On Wed, 9 Jan 2002 13:06:11 -0500
 "Mangavalli, Ramanujam" <RamM@Mvmills.com> wrote:
> I am not a Java guru, but can't you use a single SQL
> statement something
> like
>
> SELECT COUNT(*), CustomerId, CustomerDescription
> from Invoice_Table join customer_table  on
> invoice_table.customerId =
> customer_table.customerId
> group by customerId, customerDescription
>
> You can always build more complex statements, use UNIONs
> to get control
> breaks etc...
>
> Just my 2 cents.
>
>
> -----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!