Performance in updating database

[ahjiang]

Hi all,

I post this question here as there are not much activity over at the
database group.

I'm updating 5000 records to the Oracle database with 8 fields. It
took around 1min to finish updating.


My flow of program is as follows

I have set connection autocommit to false.

ResultSet cursor set to FORWARD_ONLY

Fire a query to the database and retrive the resultset of the record.

From the resultset, i do the

update and finally rs.updateRow().

All primary keys are indexes in the table.

After some investigation using a profiler, the method executeUpdate()
is taking the longest time.

How can i further improve the performance?

Appreciate any advices

 

[ducnbyu]

What kind of update are you doing? Are you changing the same field(s)
to the same value(s) or is each update to each row unique? If unique,
where are you getting the new values from? Where is the database
located (remote or local)?

 

[steve]

Hi all,

I post this question here as there are not much activity over at the
database group.

I'm updating 5000 records to the Oracle database with 8 fields. It
took around 1min to finish updating.


My flow of program is as follows

I have set connection autocommit to false.

ResultSet cursor set to FORWARD_ONLY

Fire a query to the database and retrive the resultset of the record.
update and finally rs.updateRow().

All primary keys are indexes in the table.

After some investigation using a profiler, the method executeUpdate()
is taking the longest time.

How can i further improve the performance?

Appreciate any advices

1.we need to know or better still give us the oracle data structure for the
table.

2. I hope you are not trying to load 5K records into your computer for
update.


3. send the code update routine.


with that info , we should be able to sort you out.


Steve

 

[ahjiang]

actually im exporting the data to an excel spreadsheet.

user would make changes and import back to the database

as a requirement, all records would be updated.

so i fire a select query and get the resultset. Using the resultset to
do rs.updateObject(columnname, value) and finally rs.updateRow()..

the database is located remotely

the profiler im using shows rs.executeUpdate() is taking the most time.
So i was thinking how i can improve it?

Thanks

 

[ducnbyu]

Since your program is taking 1 minute to do 5000 updates on 8 fields
each it sounds like the executeUpdate() is executing on the client
making a round trip to the remote server for each row or at least maybe
an undesireable number of round trips.

What you may need is to reduce the number of round trips to the remote
server probably to 1.

Read chapter 12 Performance Extentions/Update Batching in Oracle's JDBC
Developer's Guide and Reference. (At least that's where it is in the
version of Oracle documentation I have access to.) You can set the
batch size to 5000 if you like for 1 round trip. There's also info on
how to find out the default batch size if applicable.

Or here's a public link to the chapter if you don't already have access
to Oracle documentation

http://www.stanford.edu/dept/itss/docs/oracle/10g/java.101/b10979/oraperf.htm#sthref1841

HTH

 

[ahjiang]

i have to use resultset to do rs.updateRow() because using
preparedstatements would make the program complicated...

seems like there is no other way out?

 

[Gilbert Ostlethwaite]

Could you not run the Oracle SQL profile tool TKPROF to see what is
happening on your database?

Regards