Performance in updating database

Thread view:

ahjiang@gmail.com - 10 May 2006 14:27 GMT

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 :)

Reply to this Message

ducnbyu@aol.com - 10 May 2006 18:46 GMT

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)?

Reply to this Message

steve - 10 May 2006 22:29 GMT

> Hi all,
>
[quoted text clipped - 22 lines]
>
> 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

Signature

NewsGuy.Com 30Gb $9.95 Carry Forward and On Demand Bandwidth

Reply to this Message

ahjiang@gmail.com - 11 May 2006 06:41 GMT

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

Reply to this Message

ducnbyu@aol.com - 11 May 2006 19:14 GMT

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#st
href1841

HTH

Reply to this Message

ahjiang@gmail.com - 12 May 2006 06:42 GMT

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?

Reply to this Message

Gilbert Ostlethwaite - 12 May 2006 14:23 GMT

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

Regards

Reply to this Message

Performance in updating database

Free Magazines