Slow performance for SELECT and UPDATE million rows in Oracle via JDBC

Question

I have a user table (Oracle 11g DB) with more than 1 million rows, in which there are all user passwords in plain text, which I am trying to use using the SHA512 algorithm (hash and salt). To start below, my Java class should read all the entries from the user table, hash it, and update it back to the user table.

• I use a prepared statement for SELECT and UPDATE queries
• I set the sample size of the finished expression to 1000 ( setFetchSize(1000) )
• I set the auto commit property to false
• Using the batch method for bulk updates

 try { ps = con.prepareStatement("update user set password=? where ID=?"); psSel = con.prepareStatement("select ID, password from user"); psSel.setFetchSize(1000); rs = psSel.executeQuery(); String hashPassword = null; while (rs.next()) { long id = rs.getLong(1); String pwd = rs.getString(2); hashPassword = <<CALL TO PASSWORD HASHING UTIL>>; ps.setString(1, hashPassword); ps.setLong(2, id); ps.addBatch(); //Every 5000 records update and commit if(++count % batchSize == 0) { ps.executeBatch(); con.commit(); } } ps.executeBatch(); con.commit(); } catch (SQLException e) { e.printStackTrace(); } 

To update 100,000 records, the above method takes about 8 minutes and I feel pretty high.

Database Used: Oracle 11g

Java Version: 1.6

Environment: Windows 7

I'm not sure I missed something. Can you advise or recommend the best way to handle such bulk goods?

UPDATE

I looked at the temporary table - USER , which I created earlier, and saw that the Primary Key constraint was not added to the ID column. I went ahead and added a PK constraint for the ID column and restarted my utility. Now it took 36 seconds to process 100,000 rows .

To be sure that I also created another temporary table USER_TMP2 without PK restriction and ran my utility, and it took 8 minutes , as usual, for 100,000 >

Answer 1

The second time I looked at the temp - USER table that I created earlier, and saw that the primary key constraint was not added to the identifier column. I went ahead and added a PK constraint for the ID column and restarted my utility. Now it took 36 seconds to process 100,000 rows.

To be sure that I also created another temporary table USER_TMP2 without PK restriction and ran my utility, and it took 8 minutes, as usual, for 100,000

Moral of the story: when examining poor performance, the first thing is to examine the indexing of the tables involved - either by a simple check, or by looking at query execution plans; so you don’t have many unnecessary table scans.

Answer 2

Make a look at the user table and extract data from this table. This optimizes the query execution time. This may be useful in your case.