问题：多行插入与合并 [英] question: multiple row insert vs. merge
问题描述
我的应用程序需要将一千个值行插入一个大多数空表中
（从文件中读取数据）。我可以使用插入，也可以使用合并。
使用合并的优势在于，在表格
不为空的少数情况下，它可以处理更新部分，这使得
应用程序更清洁。
但是，我担心的是合并状态会降低插入的速度
新数据，因为在大多数情况下表是空的。
所以我的问题（在我进行任何综合测试之前）是：
1.如果它是一个空表，它会比插入更慢吗？
2.假设我们可以执行多行值插入，那么插入比使用和/或
更快哪个没有多行选项？
非常感谢！
My app needs to insert thousand value rows into a mostly empty table
(data are read from a file). I can either use inserts, or use merge.
The advantage of using merge is that in the few cases where the table
is not empty, it can take care of the updating part, which makes the
app cleaner.
However, my concern is the merge state would slow dowm the insertion
of new data, since in most cases the table is empty.
So my questions (before I run any comprehensive tests) are:
1. would merge slower than insert if it is an empty table?
2. given that we can do multiple row value inserts, would insert be
even faster than merge with which there is no multipe row option?
Thanks a lot!
推荐答案
Henry J.写道：
Henry J. wrote:
我的应用程序需要将一千个值行插入一个大多数空表中
（从文件中读取数据）。我可以使用插入，也可以使用合并。
使用合并的优势在于，在表格
不为空的少数情况下，它可以处理更新部分，这使得
应用程序更清洁。
但是，我担心的是合并状态会降低插入的速度
新数据，因为在大多数情况下表是空的。
所以我的问题（在我进行任何综合测试之前）是：
1.如果它是一个空表，它会比插入更慢吗？
2.假设我们可以执行多行值插入，那么插入比使用和/或
更快哪个没有多行选项？
My app needs to insert thousand value rows into a mostly empty table
(data are read from a file). I can either use inserts, or use merge.
The advantage of using merge is that in the few cases where the table
is not empty, it can take care of the updating part, which makes the
app cleaner.
However, my concern is the merge state would slow dowm the insertion
of new data, since in most cases the table is empty.
So my questions (before I run any comprehensive tests) are:
1. would merge slower than insert if it is an empty table?
2. given that we can do multiple row value inserts, would insert be
even faster than merge with which there is no multipe row option?
这是DB2 for zOS吗？哪个版本？
请记住，在DB2 for zOS中支持NOT ATOMIC数组合并。
当然DB2 for LUW支持常规MERGE和VALUES
子句可以有多行（或者基于查询）
干杯
Serge
-
Serge Rielau
DB2解决方案开发
IBM多伦多实验室
Is this DB2 for zOS? Which version?
Keep in mind that in DB2 for zOS supports NOT ATOMIC "array merge".
And of course DB2 for LUW support just regular MERGE with a VALUES
clause than can have multiple rows (or be based on a query)
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
9月2日，4：59 * pm，Serge Rielau< srie ... @ ca.ibm.comwrote：
On Sep 2, 4:59*pm, Serge Rielau <srie...@ca.ibm.comwrote:
这是DB2 for zOS ？哪个版本？
请记住，在DB2 for zOS中支持NOT ATOMIC数组合并。
当然DB2 for LUW支持常规MERGE和VALUES
子句可以有多行（或者基于查询）
Is this DB2 for zOS? Which version?
Keep in mind that in DB2 for zOS supports NOT ATOMIC "array merge".
And of course DB2 for LUW support just regular MERGE with a VALUES
clause than can have multiple rows (or be based on a query)
DB2版本是DB2 / LINUXX8664 9.1.4。
The DB2 version is DB2/LINUXX8664 9.1.4.
Henry J.写道：
Henry J. wrote:
9月2日下午4:59，Serge Rielau< srie .. 。@ ca.ibm.comwrote：
On Sep 2, 4:59 pm, Serge Rielau <srie...@ca.ibm.comwrote:
>这个DB2 for zOS？哪个版本？
请记住，在DB2 for zOS中支持NOT ATOMIC数组合并。
当然DB2 for LUW只支持带有VALUES
子句的常规MERGE而不是多个行（或基于查询）
>Is this DB2 for zOS? Which version?
Keep in mind that in DB2 for zOS supports NOT ATOMIC "array merge".
And of course DB2 for LUW support just regular MERGE with a VALUES
clause than can have multiple rows (or be based on a query)
DB2版本是DB2 / LINUXX8664 9.1.4。
The DB2 version is DB2/LINUXX8664 9.1.4.
好的，那么我不理解你对多行MERGE的评论。
如果目标表是空的，MERGE将会快速决定
没有行匹配。所以我不担心表现。
如果有疑问，请比较计划（和测试）。
干杯
Serge
-
Serge Rielau
DB2解决方案开发
IBM多伦多实验室
OK, then I don''t understand your comment on multi row MERGE.
Also if the target table is empty The MERGE will be quick to decide that
no row matches. So I''m not worried about performance.
When in doubt compare the plans (and test).
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
这篇关于问题：多行插入与合并的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
