Batch Insert: Using Single vs. Multiple Statements

In a single transaction, is there any significant performance difference between doing batch insert with a single statement like:

-- BEGIN TRANSACTION

INSERT INTO films (code, title, did, date_prod, kind) VALUES
    ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
    ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

-- COMMIT

or multiple statements like below:

-- BEGIN TRANSACTION

INSERT INTO films (code, title, did, date_prod, kind) VALUES
    ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy');

INSERT INTO films (code, title, did, date_prod, kind) VALUES
    ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

-- COMMIT

</details>


# 答案1
**得分**: 2

任何两种方法之间的差异都会在你在单个BEGIN / COMMIT事务中执行多个INSERT操作（你的第二种方法）时被有效消除。

为什么呢？数据操作的大部分CPU和I/O工作会在提交时发生。如果你处于自动提交模式，每个INSERT都会得到一个隐式提交，并且与之相关的开销，除非你在一个事务中。但在单个事务中进行多个数据操作语句只会产生一次开销。

如果你没有设置这个模式，你可能处于自动提交模式（除非你使用python语言连接器）。

<details>
<summary>英文:</summary>

Any difference between these two approaches is effectively eliminated if you do the multiple INSERT operations (your second approach) within a single BEGIN / COMMIT transaction.

Why? The lion&#39;s share of the cpu and I/O work for data manipulation happens upon commit. If you&#39;re in autocommit mode, each INSERT gets an implicit commit, and the overhead associated with it, unless you&#39;re in a transaction. But multiple data manipulation statements in a single transaction incur the overhead just once.

If you didn&#39;t set the the mode, you&#39;re probably in autocommit mode (unless you&#39;re using the python-language connector). 

</details>



# 答案2
**得分**: 0

在性能方面，使用单个插入语句并包含多个值集通常比在事务内单独执行多个插入语句要快。

例如，假设我们有一个名为“films”的表，其中包含列“code”、“title”、“did”、“date_prod”和“kind”。我们想要将两行数据插入到这个表中。

使用单个插入语句，你可以这样做：

    INSERT INTO films (code, title, did, date_prod, kind) VALUES
    ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
    ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

这个单个插入语句将同时将两行数据添加到“films”表中。

另外，如果使用多个插入语句，你会这样做：

    INSERT INTO films (code, title, did, date_prod, kind) VALUES
    ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy');

    INSERT INTO films (code, title, did, date_prod, kind) VALUES
    ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

在这里，你会执行两个独立的插入语句，每个插入一个行到“films”表中。
从性能的角度来看，单个插入语句通常更快，因为它优化了操作并减少了开销。它只需一次通信和准备插入操作，与在事务内单独执行多个语句相比，性能更高。

<details>
<summary>英文:</summary>

When it comes to performance, using a single insert statement with multiple value sets is generally faster than executing multiple insert statements individually within a transaction.

For example, let&#39;s say we have a table called &quot;films&quot; with columns &quot;code,&quot; &quot;title,&quot; &quot;did,&quot; &quot;date_prod,&quot; and &quot;kind.&quot; We want to insert two rows into this table.

Using a single insert statement, you can do:

    INSERT INTO films (code, title, did, date_prod, kind) VALUES
    (&#39;B6717&#39;, &#39;Tampopo&#39;, 110, &#39;1985-02-10&#39;, &#39;Comedy&#39;),
    (&#39;HG120&#39;, &#39;The Dinner Game&#39;, 140, DEFAULT, &#39;Comedy&#39;);

This single insert statement will add both rows to the &quot;films&quot; table in one operation.

Alternatively, using multiple insert statements, you would do:

        INSERT INTO films (code, title, did, date_prod, kind) VALUES
    (&#39;B6717&#39;, &#39;Tampopo&#39;, 110, &#39;1985-02-10&#39;, &#39;Comedy&#39;);

        INSERT INTO films (code, title, did, date_prod, kind) VALUES
    (&#39;HG120&#39;, &#39;The Dinner Game&#39;, 140, DEFAULT, &#39;Comedy&#39;);

Here, you execute two separate insert statements, each adding one row to the &quot;films&quot; table.
In terms of performance, the single insert statement is generally faster because it optimizes the operation and reduces overhead. It communicates and prepares the insert only once, resulting in improved performance compared to executing multiple statements individually within a transaction.

</details>