最快的是1x插入512行，4x插入128行或512x插入1行 [英] Which would be fastest, 1x insert 512 rows, 4x insert 128 rows, or 512x insert 1 rows

问题描述

我有512行要插入到数据库中.我想知道在一个大插入中提交多个插入是否有任何好处.例如

I've got 512 rows to insert to a database. I'm wondering if there is any advantage to submitting multiple inserts over one large insert. For example

1x 512行插入-

1x 512 row insert --

INSERT INTO mydb.mytable (id, phonenumber)
VALUES (1, 555-555-5555) , (2, 555-555-5555) , (3, 555-555-5555), //repeat to id = 512

VS 4x 128行插入

VS 4x 128 row insert

INSERT INTO mydb.mytable (id, phonenumber)
VALUES (1, 555-555-5555) , (2, 555-555-5555) , (3, 555-555-5555), //repeat to id = 128
INSERT INTO mydb.mytable (id, phonenumber)
VALUES (129, 555-555-5555) , (130, 555-555-5555) , (131, 555-555-5555), //repeat to id = 256, then next 128, then next 128.

VS 512x 1行插入

VS 512x 1 row insert

INSERT INTO mydb.mytable (id, phonenumber)
VALUES (1, 555-555-5555)
INSERT INTO mydb.mytable (id, phonenumber)
VALUES (2, 555-555-5555) // repeat until id = 512

还有一个关于测试的问题，如果我将其设置为测试-假设我采用第一种方法，即插入一个大的512行.这需要0.5秒.然后，下次需要0.3秒的时间–我认为这种缓存是否会发生，就像编程语言两次执行相同操作时一样，会在sql中发生? (因此，每种方法都需要获得良好的测试结果平均值吗?)

And a question about testing this, if I were to set this up as a test - Say I do the first approach, one large 512 row insert. That takes 0.5 seconds. Then the next time it takes 0.3 seconds -- does this caching that I think will happen, like it does when a programming language performs the same action twice, happen in sql? (Thusly would it be neccesary to get a good average of test results for each approach?)

做非常大的插入(例如，五百万个)时，我还应该考虑其他什么问题??的确，如果发送到数据库的数据包太大，它将永远无法接收或执行查询-我是否还会因插入非常大的数据而遇到麻烦?

What other considerations should I take when doing extremely large inserts (say, half a million)? Is it true that if the packet sent to the database is too large it will never recieve or execute the query -- Will I even run into trouble ever for making a very large insert?

推荐答案

我的答案是假设使用SQL Server；我怀疑我所说的内容将适用于其他SQL引擎.

My answer is assuming SQL Server; I suspect what I say would be applicable to other SQL engines.

任何SQL Server查询的大部分开销都是制定执行计划.如果您是单个插入操作，则必须制定执行计划一(1)次；如果执行512个单独的插入，则必须制定512次执行计划.因此，一次插入的开销要少得多.

Much of the overhead of any SQL Server query is development of an execution plan. If you do this as a single insert, it will have to develop the execution plan one (1) time; if you do 512 seperate inserts, it will have to develop the execution plan 512 times. So it is considerably less overhead to do a single insert.

我不会惊讶地发现引擎发现了您不知道要做，不想做或无法做到的其他效率.但是，如果只是节省执行计划，那么仍然值得一次插入.

I wouldn't be surprised to find that the engine finds other efficiencies that you either wouldn't know to do, wouldn't think to do, or wouldn't be able to do. But if it was only the execution plan savings, it's still worth doing in a single insert.

这篇关于最快的是1x插入512行，4x插入128行或512x插入1行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！