Sai Pullabhotla wrote:
Hello Every One,
* *
*Question: *
Does any one 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 (keeps track
of some history) and need to purge the history on a regular basis if a
record is older than specified number of days. I also need to delete
some other files from the file system that correspond to a specific
record. So, I’ve to do a SELECT statement based on the DATE, loop
through the ResultSet, delete the files from the file system, and if
everything is successful so far, delete the record from the database.
Now the question is, should I use an updatable result set when doing
my SELECT statement and use the *ResultSet.delete* method to delete
the current row, or would it perform better if I get a READ-ONLY
result set and fire a *DELETE statement* (with the primary key) for
every row that I want to delete.
I appreciate your help.
Regards,
Sai Pullabhotla
President
jMethods, Inc.
Phone: +1 (402) 408-5753
Fax: +1 (402) 408-6861
www.jMethods.com <http://www.jMethods.com>
Hi -
It's a matter of how long a lock is held - from the Developers Guide,
section: Updatable result sets:
Updatable cursors lock the current row with an update lock when
positioned on the row, regardless of isolation level. Therefore, to
avoid excessive locking of rows, only use concurrency mode
ResultSet.CONCUR_UPDATABLE or the FOR UPDATE clause when you actually
need to update the rows. For more information about locking, see Types
and scope of locks in Derby systems.
<http://db.apache.org/derby/docs/10.2/devguide/cdevconcepts36402.html>
