英文:
Execute batch update faster
问题
在一个旧项目中,我有一个带有3个索引的表,这3个索引分别在3列上。我正在执行批量查询(约5000个更新查询),类似于 UPDATE mytable set mycolumn = 'blah' when myIndexedColumn = 'someId'
。
executeBatch
命令大约需要1小时30分钟,我使用的是 Oracle 11g 数据库和 Java 6,还有 Spring Batch。相关的表包含 170 0000 行。
Statement ps = null;
Connection connection = null;
try {
int counter = 0;
connection = myDatasource.getConnection();
connection.setAutoCommit(false);
ps = connection.createStatement();
for (String request : myListOfRequests) {
ps.addBatch(request);
}
ps.executeBatch();
connection.commit();
} catch (SQLException ex) {
LOGGER.error("My Errors : {}", ex.getMessage());
} finally {
if (ps != null) {
ps.clearBatch();
ps.close();
}
if (connection != null) connection.close();
}
我已经删除了这些索引,但我没有注意到显著的差异。我不能使用现代技术。同时删除并重新构建新表并不是一个安全的任务。那么你有什么想法可以改进这个任务呢?
英文:
In an old project, I have a table with 3 indices on 3 columns, I am executing batch queries (~= 5000 update queries) like UPDATE mytable set mycolumn = 'blah' when myIndexedColumn = 'someId'
.
The executeBatch command, takes 1h:30 approximately, I use oracle 11g database, and Java 6, Spring batch also. The concerned table holds 1 700 000 rows.
Statement ps = null;
Connection connection = null;
try {
int counter = 0;
connection = myDatasource.getConnection();
connection.setAutoCommit(false);
ps = connection.createStatement();
for (String request : myListOfRequests) {
ps.addBatch(request);
}
ps.executeBatch();
connection.commit();
} catch (SQLException ex) {
LOGGER.error("My Errors : {}", ex.getMessage());
} finally {
if (ps != null) {
ps.clearBatch();
ps.close();
}
if (connection != null) connection.close();
}
I have dropped the indices, but I didn't noticed a significant difference. I cannot use modern technologies. Also deleting and rebuilding a new table is not a secured task. So have you an idea how I can improve this task?
答案1
得分: 1
解决方案,最初由jawad abbassi在问题中发布:
感谢@curiosa,我已经以以下方式使用了preparedStatement而不是statement:
PreparedStatement ps = null;
Connection connection = null;
String sql = "UPDATE MYTABLE SET COLUMN1 = ? WHERE COLUMN2 = ?";
try {
connection = myDataSource.getConnection();
connection.setAutoCommit(false);
ps = connection.prepareStatement(sql);
for (MyBean bean : myListOfBeans) {
ps.setBigDecimal(1, bean.getColumn1());
ps.setString(2, bean.getColumn2());
ps.addBatch();
}
ps.executeBatch();
} catch (SQLException ex) {
LOGGER.error("My errors : {}", ex.getMessage());
} finally {
if (ps != null) {
connection.commit();
ps.close();
connection.close();
}
}
英文:
Solution, originally posted by jawad abbassi in the question:
Thanks to @curiosa, I have used preparedStatement instead of a statement in this way:
PreparedStatement ps = null;
Connection connection = null;
String sql = "UPDATE MYTABLE SET COLUMN1 = ? WHERE COLUMN2 = ?";
try {
connection = myDataSource.getConnection();
connection.setAutoCommit(false);
ps = connection.prepareStatement(sql);
for (MyBean bean : myListOfBeans) {
ps.setBigDecimal(1, bean.getColumn1());
ps.setString(2, bean.getColumn2());
ps.addBatch();
}
ps.executeBatch();
} catch (SQLException ex) {
LOGGER.error("My errors : {}", ex.getMessage());
} finally {
if (ps != null) {
connection.commit();
ps.close();
connection.close();
}
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论