blocks|key|5131624|text|将多个插入合并到一个命令中总是会比执行单独的插入更快地执行。原因是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5131625|5131626|很多工作都是解析SQL完成的-对于多版本，只有一次解析工作|unordered-list-item|5131627|更多的工作是完成权限检查-再说一次，只有当|5131628|数据库连接“聊天”时才完成-对于多版本，握手只做一次。当使用较差的网络connection|5131629|Finally，多版本时，您会真正注意到此问题，从而为服务器提供了优化|5131630|操作的机会|5131631|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|R|8|@]|9|@]|A|$]]|$1|F|3|G|5|E|7|S|8|@]|9|@]|A|$]]|$1|H|3|I|5|E|7|T|8|@]|9|@]|A|$]]|$1|J|3|K|5|E|7|U|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|V|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|O|$]]

Combining multiple inserts into one command is always going to execute much more quickly than executing separate inserts. The reasons are:

<ul>
<li>A lot of work is done parsing the SQL - with multi version, there's only one parsing effort</li>
<li>More work is done checking permissions - again, only done once</li>
<li>Database connections are "chatty" - with multi version, handshaking only done once. You really notice this issue when using a poor network connection</li>
<li>Finally, multi version gives opportunity for server to optimize the operation</li>
</ul>

blocks|key|3347341|text|一般的想法是让SQL数据库做它的事情，而不是试图将数据库视为某种类型的磁盘读取。我见过很多次，开发人员会从一个表中读取，然后再从另一个表中读取，或者执行一个通用查询，然后遍历每一行，看看这是否是他们想要的。通常，最好让SQL数据库来做它自己的事情。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3347342|在这种情况下，我看不出单行插入与多行插入有什么优势。我猜可能会有一些，因为你不需要做多次准备和提交。|3347343|实际创建一个临时数据库并尝试一下应该不会太困难。创建一个包含两列的数据库，并让程序生成数据以放入表中。给自己留出足够的时间去做。例如，此表将包含多少项？还有，你认为你一次要插入多少个？假设创建一个包含1,000,000个项的表，并在此表中一次插入1000个项，一次插入100个项，每次插入一个项。只需使用增量运算符生成数据。一次可以插入的项目数量可能有一个“甜蜜点”。|3347344|在我公正且始终正确的观点中，您可能会发现这种差异并不值得为之烦恼，您应该使用使您的代码最易于维护的方法。|3347345|我有一个编程格言：，你想优化代码的地方可能是错误的地方，。我们喜欢效率，但我们通常会攻击错误的项目。而且，无论我们在效率方面挤出了什么，我们最终都会浪费在维护上。|offset|length|style|BOLD|3347346|所以，只要编写最容易理解的程序就行了，不要担心效率过高。|3347347|entityMap^0|0|0|0|0|9|J|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|S|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|T|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|U|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|V|8|@$J|W|K|X|L|M]]|9|@]|A|$]]|$1|N|3|O|5|6|7|Y|8|@]|9|@]|A|$]]|$1|P|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|Q|$]]

There is a general idea to let the SQL database do its thing and not try to treat the database as some sort of disk read. I've seen many times where a developer will read from one table, then another, or do a general query and then run through each row to see if it's the one they want. Generally, it's better to let the SQL database do its thing.

In this case, I can't really see an advantage of doing a single vs. multiple row insert. I guess there might be some because you don't have to do multiple prepares, and commits. 

It shouldn't be too difficult to actual create a temporary database and try this out. Create a database with two columns, and have the program generate data to toss into the tables. Give yourself a decent amount to do. For example, how many items will this table have? And, how many do you think you'll be inserting at once? Say create a table of 1,000,000 items, and insert into this table 1000 items at a time, 100 items at a time, and one item at a time. Just generate data using the increment operator. There may be a "sweetspot" of the number of items you can insert at once.

In my unbiased, and always correct opinion, you'll probably find that the difference isn't worth fretting over, and you should instead employ the method that makes your code the easiest to maintain.

I've have a programming dictum: The place where you want to optimize your code is probably the wrong place. We like efficiency, but we usually attack the wrong item. And, whatever we've squeezed out in terms of efficiency, we end up wasting in maintenance.

So, just program what is the easiest to understand and don't fret about being overly efficient.

blocks|key|1445900|text|只需添加几个在插入时需要考虑的其他性能差异因素：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1445901|1445902|Foreign键-如果要插入的表具有外键，SQL+Server实际上需要在插入时联接到外键表。当您在一个查询中执行插入时，SQL+server可以更有效地执行这些提交-由于您没有提到事务，因此我假设您一定是使用SQL+Server自动提交模式。对于如此少的行数，创建40个事务与创建1个事务的开销可能会高于维护日志以允许回滚的开销。但是，如果要插入400000行，在一个语句/事务中插入可能比插入400000个单独的行更昂贵，因为准备回滚到400000行的成本非常高(如果要插入400000行，通常最好成批插入->最佳批大小可以通过测试确定)。此外，在超过某个行数的情况下，禁用外键、插入行，然后重新启用它们可能会更有效。|unordered-list-item|offset|length|style|BOLD|1445903|1445904|entityMap^0|0|0|0|8|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|N|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|O|8|@$F|P|G|Q|H|I]]|9|@]|A|$]]|$1|J|3|-4|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|L|$]]

Just to add a couple of other performance differentiators to think about on insertion:

<ul>
<li>Foreign Keys - If the table you are inserting into has foreign keys, SQL Server effectively needs to join to the foreign key tables on insert. When you do your inserts in one query, SQL server can be more efficient in doing these joins.</li>
<li>Transactions - As you don't mention transactions, I assume you must be using SQL Server auto-commit mode. With such a small number of rows, it is likely that the overhead of creating 40 transactions vs. 1 transaction would be higher than maintaining the log to allow rollback. However, if you were inserting 400000 rows, it would likely be more expensive to insert in one statement/transaction than insert 400000 separate rows as the cost to be prepared to roll back up to 400000 rows is very high (if you were to insert 400000 rows, it usually is best to insert in batches -> the optimal batch size can be determined through testing). Also, above a certain row count, it may become more efficient to disable the foreign keys, insert the rows, then re-enable them.</li>
</ul>

I'm currently in a situation where I'm building a script that I know will need to insert multiple rows. I'm doing this in Perl, so in terms of parameterization, it's much easier to insert each row individually. In terms of speed, I'm guessing running just one insert statement will be faster (although latency will be relatively low as I'm quite close to the database itself). I'm thinking the number of rows per run of the script will be about 20-40 on average. That said, what would be the approximate performance differences between running just 1 INSERT INTO statement v.s. running one for each row? Note: The server is running SQL 2008.

[EDIT]Since there seems to be a lot of confusion, I'd like to clarify that what I'm really asking for is the theory behind how a multi-row insert is handled by SQL Server 2008. Does it essentially just convert it internally into a bunch of individual insert statements and run those over one connection, or does it do something more intelligent?

Yes, I know I can run timed loops. No, that's not what I'm asking for. [/EDIT]

What Are the Performance Differences Between Running One vs Many Inserts

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我目前正在构建一个脚本，我知道该脚本需要插入多行。我是用Perl来做这件事的，所以在参数化方面，单独插入每一行要容易得多。就速度而言，我猜只运行一条insert语句会更快(尽管延迟会相对较低，因为我离数据库本身很近)。我认为每次运行脚本的行数平均约为20-40行。也就是说，仅运行一条INSERT INTO语句与为每行运...

问运行一个插入与多个插入之间的性能差异是什么
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问运行一个插入与多个插入之间的性能差异是什么EN