aspose file tools*
The moose likes JDBC and the fly likes Performance of Updatable ResultSet vs Update/Delete Statements Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Performance of Updatable ResultSet vs Update/Delete Statements" Watch "Performance of Updatable ResultSet vs Update/Delete Statements" New topic
Author

Performance of Updatable ResultSet vs Update/Delete Statements

hemant mandge
Greenhorn

Joined: Jul 04, 2012
Posts: 2
I want to know how the performance of updatable ResultSets compare to a regular update/delete statements?


More Details:

I’ve a table which could have several thousands of rows. I need to update value from one of the column for each row with new value. e.g Encrypt Social Security Number for every customer and update it in table.

So, I’ve to do a SELECT statement, loop through the ResultSet and update record.

Now the question is, should I use an updatable result set when doing my SELECT statement or would it perform better if I get a READ-ONLY result set and fire a UPDATE statement (with the primary key) for every row that I want to UPDATE.


Need Help Urgently.

Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

I have no idea on updatable result sets, but I would build up a batch of update statements, no idea how big a batch would be best.

edited: for appaling typing
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

And welcome to the ranch
hemant mandge
Greenhorn

Joined: Jul 04, 2012
Posts: 2
Thanks Wendy,

Following two methods for updating Tables with example.

Which one of Following TWO METHODS will have good performance.

METHOD ONE:- [updatable resultset]
following is piece of code using updatable resultset



METHOD TWO:- [ update statement ]
for having Series of update statement i need to have same piece of code with createStatement in READ_ONLY Mode.
It will fetch each record one by one and execute update query for SSN

Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 39436
    
  28
Welcome again

We don’t use the word “urgent” on this forum. Nor do we like to ask the question again louder. Your question is a bit like this one:
Which gives better performance? A bicycle or a knife and fork?
It is only worth considering performance if you are sure the two do the same thing. You ought to check the updateString method and read what it does. If it does something different from update/delete statements, then your question is meaningless.

Why are you using getString() together with toString()?

Also, you should be circumspect about performance. It ought to be the last consideration, and only when there is a performance problem should you do anything about it. Chances are, that many single field updates will have poor performance because the updating time is overwhelmed by the multiple connections and disconnections. It is likely that updating millions of fields will take a long time, because you overwhelm the available memory, but Wendy is right that you cannot know which size of update will have the best performance. That will probably vary, depending on machines used, network load, etc. Network load, etc., might mean that an update takes 10× as long at 4pm as at 4am.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Performance of Updatable ResultSet vs Update/Delete Statements