How to optimize this SQL delete

Question

I want to optimize the performance of this SQL query. If I populate this hashtable with one million keys the query will take around minute. How I can optimize this Java method for faster execution?

private HashMap<String, Boolean> selectedIds = new HashMap<>();
public void deleteSelectedIDs() throws SQLException {
 if (ds == null) {
 throw new SQLException();
 }
 Connection conn = ds.getConnection();
 if (conn == null) {
 throw new SQLException();
 }
 PreparedStatement ps = null;
 ResultSet resultSet = null;
 try {
 conn.setAutoCommit(false);
 boolean committed = false;
 try {
 String sqlDeleteQuery = "DELETE FROM ACTIVESESSIONSLOG WHERE ASESSIONID = ?";
 Set<String> keySet = selectedIds.keySet();
 String[] keys = new String[]{};
 keys = selectedIds.keySet().toArray(keys);
 ps = conn.prepareStatement(sqlDeleteQuery);
 for (int i = 0; i < keys.length; i++) {
 if (selectedIds.get(keys[i]).booleanValue()) {
 ps.setString(1, keys[i]);
 ps.executeUpdate();
 ps.clearParameters();
 selectedIds.put(keys[i], false); //get(keys[i]) = false;
 }
 }
 conn.commit();
 committed = true;
 //selectedIds.clear();
 } finally {
 if (!committed) {
 conn.rollback();
 }
 }
 } finally {
 ps.close();
 conn.close();
 }
 }
private HashMap<String, Boolean> selectedIds = new HashMap<>();
public void deleteSelectedIDs() throws SQLException {
 if (ds == null) {
 throw new SQLException();
 }
 Connection conn = ds.getConnection();
 if (conn == null) {
 throw new SQLException();
 }
 PreparedStatement ps = null;
 ResultSet resultSet = null;
 try {
 conn.setAutoCommit(false);
 boolean committed = false;
 try {
 String sqlDeleteQuery = "DELETE FROM ACTIVESESSIONSLOG WHERE ASESSIONID = ?";
 Set<String> keySet = selectedIds.keySet();
 String[] keys = new String[]{};
 keys = selectedIds.keySet().toArray(keys);
 ps = conn.prepareStatement(sqlDeleteQuery);
 for (int i = 0; i < keys.length; i++) {
 if (selectedIds.get(keys[i]).booleanValue()) {
 ps.setString(1, keys[i]);
 ps.executeUpdate();
 ps.clearParameters();
 selectedIds.put(keys[i], false); //get(keys[i]) = false;
 }
 }
 conn.commit();
 committed = true;
 //selectedIds.clear();
 } finally {
 if (!committed) {
 conn.rollback();
 }
 }
 } finally {
 ps.close();
 conn.close();
 }
 }

Answer 1

Per the other example - step 1 should be to get out your profiler.

In any case - I suspect you would get significantly reduced round trip times by taking advantage of JDBC batching capability. Here's an example: http://www.tutorialspoint.com/jdbc/jdbc-batch-processing.htm (see the second one - using a prepared statement).

In short - you create your DELETE prepared statement, drop the values into a batch, and then execute the batch in one go. Use a prepared statement to reduce parse time for the server.

Depending on if you want to ramp up the complexity, you might want to split the rows into tasks and run them in parallel threads.

Answer 2

If you look at this in a profiler, I will bet that the most time is being used at this line:

ps.executeUpdate();

I would gather all strings to be deleted into one statement like:

DELETE FROM ACTIVESESSIONSLOG WHERE ASESSIONID IN ('A','B','C')

and send that 1 SQL statement instead of 1,000,000 statements.

Second:

instead of:

 Set<String> keySet = selectedIds.keySet();
 String[] keys = new String[]{};
 keys = selectedIds.keySet().toArray(keys);

I would use an iterator:

 Set<String> keySet = selectedIds.keySet();
 Iterator<String> iter = keySet.iterator();
 while(!iter.hasNext()) {
 String key = iter.next();
 //.. revise substituting key for keys[i]
 }

This avoids the need to copy all of those string references around. It might be worth it to simply operate on a Set of strings that only possesses members that are true if selectedIds is not used for other purposes.

• user1285928: Thank you for the reply! The big problem is that I don't have exact number of the rows that I have to delete every time. Sometimes I might delete only 10 rows. How I can use one statement.
• user1285928: I tried to implement the iterator but I failed - something is missing. Would you show me the complete edited source code, please?
• tom: Some databases (like oracle) have a maximum query length and I am pretty sure that sending one delete with 1.000.000 values in the in statement will hit that.