RDBMS：执行多项操作的最佳方式是什么
我使用 PostgreSQL 8.4，但我认为我的问题可以扩展到大多数 RDBMS。
我需要对这些行执行数据更改操作，例如更新或删除，其中指定列具有指定集中的值。例如，我想删除那些 id 位于 (1,4,7,8) 的行。
整个操作要么成功，要么失败，所以我这里有两个选择：
- 使用
IN语法，例如
DELETE FROM my_table WHERE id IN (1,4,7,8)
- 使用在一个事务中执行的多个单个操作，例如
DELETE FROM my_table WHERE id = 1;
DELETE FROM my_table WHERE id = 4;
...
作为普通 SQL 命令执行时，这两种方法有什么区别吗？哪一个更好？
使用 JDBC 准备语句进行这些操作时是否有同样的问题？
I use PostgreSQL 8.4, but i think my question can be expanded to most of the RDBMS.
I need to perform data-changing operation like update or delete for those rows, where the specified column has value from the specified set. For example I want to delete those rows, where id is in (1,4,7,8).
The whole operation should either succeed or fail, so I have two options here:
- Use
INsyntax, like
DELETE FROM my_table WHERE id IN (1,4,7,8)
- Use several single operations executed within one transaction, like
DELETE FROM my_table WHERE id = 1;
DELETE FROM my_table WHERE id = 4;
...
Is there any difference between these two approaches when executed as plain SQL commands? Which one is better?
The same questions when using JDBC prepared statements for these operations?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
在我看来，你应该始终使用交易。通过多次操作方法，您将有更多的网络往返次数。使用的索引也可能有所不同，因此请检查查询计划。
You should always use transactions, in my opinion. With the several-operation approach you will have many more round trips across the network. There may also be a difference in what indexes get used, so check out the query plan.
所以我检查了
EXPLAIN ANALYZE，PostgreSQL在遍历表时对IN (...)请求使用特殊的过滤器。因此，主要区别应该在于性能：使用IN (...)您可以遍历该表一次。通过 N 个单独的请求= ?，您可以遍历表 N 次。虽然 PostgreSQL 应该对此进行优化，使其实际上更快，但仍然必须比IN (...)慢So I checked
EXPLAIN ANALYZEand PostgreSQL uses special filter forIN (...)request when traversing the table. So the major difference should be in performance: withIN (...)you traverse the table one time. With N separate requests= ?you traverse table N times. Though PostgreSQL should optimize this so it's actually faster, but still must be slower thatIN (...)