I want to know how the performance of updatable ResultSets compare to a regular update/delete statements?
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.
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
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.
subject: Performance of Updatable ResultSet vs Update/Delete Statements