Обсуждение: Multiple inserts

Поиск
Список
Период
Сортировка

Multiple inserts

От
Rakesh Kumar
Дата:

Hi

I am noticing that if I do this

insert into table values(1,a)
insert into table values(2,b)

insert into table values(3,c)
....
commit after 500 rows
it is lot slower (almost 10x) than
insert into table values((1,a),(2,b),(3,c)) .. upto 500

It is obvious that the gain is due to network trip avoided after every insert.

My question is: Are they any known side-effects or drawbacks of using multiple inserts.  I am aware the error checking is much more difficult in the second approach. Any other drawbacks?

thanks

Re: Multiple inserts

От
Dorian Hoxha
Дата:
If the connection is in autocommit, then each statement will also incur a commit (write to the commit log on disk).

On Fri, Sep 23, 2016 at 2:01 PM, Rakesh Kumar <rakeshkumar464@outlook.com> wrote:

Hi

I am noticing that if I do this

insert into table values(1,a)
insert into table values(2,b)

insert into table values(3,c)
....
commit after 500 rows
it is lot slower (almost 10x) than
insert into table values((1,a),(2,b),(3,c)) .. upto 500

It is obvious that the gain is due to network trip avoided after every insert.

My question is: Are they any known side-effects or drawbacks of using multiple inserts.  I am aware the error checking is much more difficult in the second approach. Any other drawbacks?

thanks