更新单个表的多行 [英] Updating multiple rows of single table

问题描述

我需要更新行数超过6万的表的每一行。
目前我这样做 -

I need to update every row of a table having more then 60k rows. Currently I'm doing it like this -

public void updateRank(Map<Integer,Double> map){
        Iterator<Entry<Integer, Double>> it = map.entrySet().iterator();
        while (it.hasNext()) {
            Map.Entry<Integer,Double> pairs = (Map.Entry<Integer,Double>)it.next();
            String query = "update profile set rank = "+ pairs.getValue()+ " where profileId = "+pairs.getKey();
            DBUtil.update(query);
            it.remove();
        }       
}

仅此方法需要大约20多分钟才能完成，点击数据库中的每一行（60k）就是我认为的原因。（虽然我使用dbcp进行连接池，有50个最大活动连接）

This method alone took around 20+ mins to complete, hitting the database for each row(60k) is what i think the reason here.(though i'm using dbcp for connecton pooling, with 50 maximum active connections)

它如果我能用单个数据库命中更新行，那就太好了。那可能吗 ？怎么样？

It'd be great if i'd be able to update rows with single database hit. Is that Possible ? How ?

或者其他任何改善时间的方法？

Or any other way to improve timing here ?

推荐答案

如果每一行都应该获得不能从数据库中的现有数据派生的不同值，那么您无法做很多事情来优化整体复杂性。所以不要期待太多的奇迹。

If every row should get a different value that cannot be derived from the existing data in the database, there is not much you can do to optimize the overall complexity. So do not expect too much wonders.

那就是说，你应该开始使用预备语句和批处理：

That said, you should start using prepared statements and batching:

public void updateRank(Map<Integer,Double> map){
    Iterator<Entry<Integer, Double>> it = map.entrySet().iterator();
    String query = "";
    int i = 0;

    Connection connection = getConnection(); // get the DB connection from somewhere
    PreparedStatement stmt = connection.prepareStatement("update profile set rank = ? where profileId = ?");

    while (it.hasNext()) {
        Map.Entry<Integer,Double> pairs = (Map.Entry<Integer,Double>)it.next();
        stmt.setInt(1, pairs.getValue());
        stmt.setDouble(2, pairs.getKey());
        stmt.addBatch(); // this will just collect the data values
        it.remove();
    }       
    stmt.executeBatch(); // this will actually execute the updates all in one
}

这是做什么的：

1. 预准备语句导致SQL解析器仅解析SQL一次


2. 批处理最小化客户端-server-roundtrips使得每次更新都不是一次


3. 客户端和服务器之间的通信被最小化，因为SQL只传输一次并且数据被收集并作为数据包发送（或者最少的数据包）

此外：

• 请检查数据库列 profileId 是否正在使用索引，以便查找相应的行足够快


• 您可以检查如果您的连接设置为自动提交。如果是这样，尝试禁用自动提交并在更新所有行后显式提交事务。这样单个更新操作也可以更快。

• Please check if the database column profileId is using an index so that looking up the respective row is fast enough
• You could check if your connection is set to auto-commit. If so try to disable auto-commit and explicitly commit the transaction after all rows are updated. This way the single update operations could be faster as well.

这篇关于更新单个表的多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！