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();
        }
    }

Do you want to optimize the SQL statement or the Java program? If it's the SQL query, how do you know it's the slow part? Did you time it? If so, what's the execution plan? — user330315
– user330315, Commented May 24, 2012 at 16:28
Do you need to get the IDs in Java? Can you compute the set of IDs in SQL and do the complete operation there? And a million SQL round-trips in a minute is pretty good! otherwise you could try batching the IDs into say 50 per SQL statement using WHERE ASESSIONID IN (?,?,.. etc. — Rup
– Rup, Commented May 24, 2012 at 16:28
Well for a start you could iterate through selectedIds.entrySet() rather than turning the keys into an array and then looking up each key as you go. But frankly the Java isn't the problem: it's your approach to generating SQL, and/or pulling all of the IDs into Java in the first place when you could do all of this in the database. — Rup
– Rup, Commented May 24, 2012 at 16:48

NPE · Accepted Answer · 2012-05-24 16:28:49Z

5

If your JDBC driver supports it, use batch processing. It is easy to use, and tends to work very well for this type of scenarios.

answered May 24, 2012 at 16:28

NPE

503k114 gold badges970 silver badges1k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user1285928 Over a year ago

This is a Java Server Faces application hosted on Glassfish server. I don't know is this supported.

user330315 Over a year ago

I agree batching is the way to go

Thorbjørn Ravn Andersen Over a year ago

He already has the commit after all the calls, so I don't think it will help much.

NPE Over a year ago

@ThorbjørnRavnAndersen: Actually, batch processing helps even if there's one commit in the end. What it does do is turn lots of tiny communications with the DBMS into few large ones.

Ryan Kenning · Accepted Answer · 2012-05-24 16:30:03Z

3

Someone may have a better idea but have you considered passing a list of the keys to Oracle via a temp table and then performing the loop within a PL/SQL function. It will lower the traffic and the DB will do the processing.

answered May 24, 2012 at 16:30

Ryan Kenning

995 bronze badges

Comments

Thorbjørn Ravn Andersen · Accepted Answer · 2012-05-24 16:32:16Z

2

Create a temporary table and insert all the ids into there. Then do a single delete for those found in t he temporary table.

answered May 24, 2012 at 16:32

Thorbjørn Ravn Andersen

75.4k35 gold badges203 silver badges359 bronze badges

Comments

Subhrajyoti Majumder · Accepted Answer · 2012-05-24 17:14:13Z

2

I think it'd be better use CallableStatement and oracle procedure.

Snippet

SQL> create type temp_tbl
  2  is
  3  table of number;
  4  /

...


SQL> create or replace procedure stored_p
  2  (
  3    list in temp_tbl,
  4    p_rc  out sys_refcursor )
  5  as
  6  begin
  7    open
  8   p_rc for delete from ACTIVESESSIONSLOG  where ASESSIONID
      in (select * from table(list));
  9  end;
 10  /

edited May 24, 2012 at 17:14

answered May 24, 2012 at 16:35

Subhrajyoti Majumder

41.3k13 gold badges84 silver badges103 bronze badges

2 Comments

Rup Over a year ago

Why - how would that solve the problem of passing all the IDs into the database? Why would that be better than prepared statements?

Subhrajyoti Majumder Over a year ago

create an temp table according to your collection and hold the data into the table and delete data using in clause.

Collectives™ on Stack Overflow

How to optimize this SQL delete

4 Answers 4

4 Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related