为什么批量插入/更新更快?批量更新如何工作? [英] Why are batch inserts/updates faster? How do batch updates work?

问题描述

为什么批量插入速度更快?是否因为插入单行的连接和设置开销对于一组行是相同的?还有哪些因素可以加快批量插入的速度?

Why are batch inserts faster? Is it because the connection and setup overhead for inserting a single row is the same for a set of rows? What other factors make batch inserts faster?

批量更新如何工作?假设表没有唯一性约束，insert 语句实际上对批处理中的其他 insert 语句没有任何影响.但是，在批量更新期间，更新可能会改变表的状态，因此会影响批处理中其他更新查询的结果.

How do batch updates work? Assuming the table has no uniqueness constraints, insert statements don't really have any effect on other insert statements in the batch. However, during batch updates, an update can alter the state of the table and hence can affect the outcome of other update queries in the batch.

我知道批量插入查询的语法是在一个大查询中包含所有插入值.批量更新查询是什么样的?例如如果我有表单的单个更新查询:

I know that batch insert queries have a syntax where you have all the insert values in one big query. How do batch update queries look like? For e.g. if i have single update queries of the form:

update <table> set <column>=<expression> where <condition1>
update <table> set <column>=<expression> where <condition2>
update <table> set <column>=<expression> where <condition3>
update <table> set <column>=<expression> where <condition4>

当它们成批使用时会发生什么.单个查询会是什么样子?

What happens when they are used in a batch. What will the single query look like ?

并且是批量插入 &更新部分 SQL 标准?

And are batch inserts & updates part of the SQL standard?

推荐答案

我正在寻找关于同一主题的答案，关于批量/批量"更新.人们通常通过将问题与具有多个值集(批量"部分)的插入子句进行比较来描述问题.

I was looking for an answer on the same subject, about "bulk/batch" update. People often describe the problem by comparing it with insert clause with multiple value sets (the "bulk" part).

INSERT INTO mytable (mykey, mytext, myint)
VALUES 
  (1, 'text1', 11),
  (2, 'text2', 22),
  ...

明确的答案仍然避开我，但我在这里找到了解决方案:http://www.postgresql.org/docs/9.1/static/sql-values.html

Clear answer was still avoiding me, but I found the solution here: http://www.postgresql.org/docs/9.1/static/sql-values.html

说清楚:

UPDATE mytable
SET 
  mytext = myvalues.mytext,
  myint = myvalues.myint
FROM (
  VALUES
    (1, 'textA', 99),
    (2, 'textB', 88),
    ...
) AS myvalues (mykey, mytext, myint)
WHERE mytable.mykey = myvalues.mykey

它具有相同的特性，即批量"，也就是在一个语句中包含大量数据.

It has the same property of being "bulk" aka containing alot of data with one statement.

这篇关于为什么批量插入/更新更快?批量更新如何工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！