我想优化此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();
}
}
最佳答案
如果您的JDBC驱动程序支持它,请使用batch processing。它易于使用,并且在这种情况下往往可以很好地工作。