多个 SQL 更新最佳实践

发布于 2024-12-01 16:48:02 字数 1123 浏览 0 评论 0原文

我正在考虑三种替代方案来替代更新表中多行这一看似简单的任务。我想知道哪一个在效率和代码可读性方面最好。尽管我不确定效率问题有明确的答案，但这可能取决于所使用的数据库。

在我的特定情况下，我使用 PostgresSQL 作为数据库，并且相关表包含不到一百万个条目。我现在有一个流程，负责从该表中提取数据子集，执行一些工作，然后使用时间戳更新这些行。

在我看来，我有三个选择：

使用 in 子句构造单个更新语句，其中的内容将替换为包含要更新的行 id 的构造字符串。

String update = "update mytable set mydate = now() where id in (?)";
StringBuilder ids = new StringBuilder();

对于（项目项目：项目）{ 
    ids.append(item.getId()).append(","); 
}

ids.setLength(ids.length() - 1);
update.replace("?", ids.toString());    
ps = con.prepareStatement(更新);
ps.executeUpdate();

使用批处理

ps = con.prepareStatement("更新 mytable set mydate = now() where id = ?");

对于（项目项目：项目）{ 
    ps.setInt(1, item.getId());
    ps.addBatch();
}

ps.executeBatch();

使用事务

con.setAutoCommit(false);
ps = con.prepareStatement("更新 mytable set mydate = now() where id = ?");

for（项目项目：项目）{ 
    ps.setInt(1, item.getId());
    ps.executeUpdate();
}

con.commit();

其中哪一个被认为是最佳实践？或者您还有其他建议吗？我个人喜欢第二个选项，它简洁且易于准备，但我的直觉是第一个选项将是性能最好的。

原文

I'm considering three alternatives to the seemingly simple task of updating multiple rows in a table. I'd like to know which one is best in terms of efficiency and code readability. Though I'm not sure there is a clear answer to the question on efficiency, which may be dependent upon the database being used.

In my particular case I'm using PostgresSQL as the database, and the table in question contains under one million entries. I have a process right now that is responsible for pulling out a subset of data from this table, performing some work, and then updating those rows with a timestamp.

As I see it I have three options:

Construct a single update statement using the in clause, the contents of which will be replaced with a constructed string containing the ids of rows to update.

String update = "update mytable set mydate = now() where id in (?)";
StringBuilder ids = new StringBuilder();

for (Item item : items) { 
    ids.append(item.getId()).append(","); 
}

ids.setLength(ids.length() - 1);
update.replace("?", ids.toString());    
ps = con.prepareStatement(update);
ps.executeUpdate();

Use a batch

ps = con.prepareStatement("update mytable set mydate = now() where id = ?");

for (Item item : items) { 
    ps.setInt(1, item.getId());
    ps.addBatch();
}

ps.executeBatch();

Use a transaction

con.setAutoCommit(false);
ps = con.prepareStatement("update mytable set mydate = now() where id = ?");

for(Item item : items) { 
    ps.setInt(1, item.getId());
    ps.executeUpdate();
}

con.commit();

Which of these would be considered the best practice? Or is there another way you would propose? I personally like option number two, it's concise and easy to ready, but my gut feeling is that option number one will be the most performant.

分享到QQ

分享到微博