asked on

Improving the performance of a simple JDBC project

Hello,

I have a simple JDBC class that happens to query a rather large table (~ 1 million rows) and has a considerable number of columns (~ 300+).

I'm trying to see if it is possible to improve the performance of this code, by either:

Pooling the connection somehow so we can have like 10 or so queries running against the same table?

Removing the looping necessary to get all the columns from the ResultSet object; I noticed a considerable improvement in runtime when I manually unrolled this (over 50% improvement)

I'm hoping there's a dynamic way to find the number of columns once, and use something like:

rs.getColumn(1) + "|" + rs.getColumn(2) + "|" + ... + rs.getColumn(x)

The most "straightforward and simple" way I can think of doing its is having a Python script generate and execute a dynamically generated Java file. Now, that doesn't sound very straightforward to me... so I'm hoping there's something new for me to learn in Java.

Appreciate any help on this!

CEHJ

I'm hoping there's a dynamic way to find the number of columns once

There is - from ResultSetMetaData.getColumnCount. But why should the number of columns be subject to change?

The best way to optimize queries in general is proper indexing, but we need to know more about how you're consuming and using the data

Errang Genevre

ASKER

There is - from ResultSetMetaData.getColumnCount. But why should the number of columns be subject to change?

No, that's not what I meant... I know how to get the column count.

What I want to do, is avoid this loop...

String temp = "";
for (int i = 0; i < columnCount; i++) {
    if (temp.length() == 0) {
        temp = rs.getColumn(i);
    } else {
        temp = temp + "|" + rs.getColumn(i);
    }

Open in new window

for every row.

Is there a macro of some sort I can use in Java so that I only need to get the expression once?

SOLUTION

dpearson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

mccarl

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Errang Genevre

ASKER

Hm... Now that you mention it, I did run the original query first; but I didn't let it complete; cancelled it after 30 minutes.

Then I tried unrolling the loop which sped it up.

Then, I removed the loop unrolling optimization and ran the query; that's how I measured the time.

Pulling all record and all columns.

CEHJ

What I want to do, is avoid this loop...

That part of your code with string concatenation is odd - what are you doing there and why?

Using a cursor (which is essentially and generally what you're doing when you're iterating a ResultSet) is certainly not without overhead. The only way to avoid overhead would be if results were cached in memory.

Having said that, there is no reason you can't refer to the columns by name, but you didn't address my question:

But why should the number of columns be subject to change?

Without seeing more of your code it's not easy to advise

Errang Genevre

ASKER

But why should the number of columns be subject to change?

The number of columns doesn't change.

That loop is to concatenate all the columns into one string... Please tell me there is a better way.

Errang Genevre

ASKER

But I will be running lots of queries, so I don't want to hard code anything.

The number of columns obviously changes from query to query.

ASKER CERTIFIED SOLUTION

CEHJ

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Errang Genevre

ASKER

Yea, unfortunately for this question all I can say is that there will be about a million rows and 300 columns; all text/varchar fields.

Errang Genevre

ASKER

But I do understand it's frustrating working without all the information; but I'd get fired if I give more information than in allowed to... Lol.

Thanks for the help so far.