执行批量更新更快

huangapple go评论121阅读模式
英文:

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

huangapple
  • 本文由 发表于 2020年10月22日 21:55:39
  • 转载请务必保留本文链接:https://go.coder-hub.com/64483808.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定