Discussion Locked

Looking for a better way to use JDBC.

0 helpful
6 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Is there a better way to select and (at the same time) update rows?

An application selects data and then flags each row as processed. It uses a field "Updateable" to indicate if a records has already been processed. Here are relevant parts of thecode:

PreparedStatement stmtFromEACDR = m_DBConn.prepareStatement(
"select * from EACDR where Updateable = 'Y'",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);

ResultSet rsEACDRs = stmtFromEACDR.executeQuery();

while( rsEACDRs.next() )
{
// ... process record ....

// .... flag the row as processed
rsEACDRs.updateString("Updateable","N");
rsEACDRs.updateRow();

// .... commit every 5000 records
}

The application needs to process a few million records everyday. According to a performance analyzer, setting "Updateable" field to "N" consumes about 30% of the total time. Can anybody recommend a more efficient way to read and flag records as processed?

Any help would be greatly appreciated.

6 replies

by jay eckles

This is just a shot in the dark, but have you tried making the "updateable" flag a different type than a character/string? Perhaps a number?

Updateable=0 || Updateable=1

Perhaps your DBMS can update numbers more quickly than it can update characters.

Jay

by dmitry

Jay, thank you for your suggestion. It appears that update of any type of field (number, char, string, or date) takes about the same time.

by tynant

What perportion of the total row count have the flag set to Y during any execution of the statement. It may be that if this field is indexed and the percentage is over about 1/3 of the total rows the use of the index is actuall degrading performance rather than improving it. Under these sort of circumstances a full table scan can be quicker.

by dmitry

Dave, can your pvovide an example of how to use a stored procedure for this purpose.

This discussion is closed to new replies.