Question

我要优化这个 SQL 查询的性能。如果我用一百万个密钥来填充这个散列, 查询需要大约一分钟时间。我如何才能优化这个 Java 方法来加快执行?

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

如果您的 JDBC 驱动程序支持它, 请使用 < a href=" http://www.tuaticerspoint. com/jdbc/jdbc- batch- process. htm" rel= “ noreferrer” > batch process 。它很容易使用, 并且往往对此类情景非常有效。

Answer 2

有人也许有更好的主意,但你是否考虑过通过临时表格通过甲骨文的钥匙列表,然后在 PL/SQL 函数内执行循环。这将降低流量,DB将进行处理。

Answer 3

创建一个临时表格, 并插入所有 ids 。然后为在临时表格 t 中找到的人做一个删除。

Answer 4

我认为最好使用Callable statement and oracle procedure 。

< 坚固 > 片段

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  /

友情链接