6

I have insert 14.485 lines on MySQL like this:

INSERT INTO `bairros` (`id`,`cidade_id`,`descricao`) VALUES (1,8891,'VILA PELICIARI');
INSERT INTO `bairros` (`id`,`cidade_id`,`descricao`) VALUES (2,8891,'VILA MARIANA');
...

It took around 5 minutes.

I had to insert in another table 16.021 lines, same database, so for test I did this:

INSERT INTO `bairros` (`id`,`cidade_id`,`descricao`) VALUES (1,8891,'VILA PELICIARI'),(2,8891,'VILA MARIANA');
...

It took just a few seconds.

What is the difference, for the database, between the scripts? And why one is faster than the other?

3 Answers 3

8

It is the difference between having multiple statements which each has to commit and be saved to the transaction log, inserted into the corresponding indexes (if any) and having a single statement (though very long).

However, this is conjecture.

It is possible that during the first set of inserts, the database server was under high load, and during the second set, it wasn't.

You really need to benchmark both (several runs of each, resetting the DB each time, taking averages etc...).

2
  • 1
    +1 Also, multiple index updates.
    – Matt S
    Commented Aug 17, 2012 at 19:35
  • @MattS - Good point. Answer updated.
    – Oded
    Commented Aug 17, 2012 at 19:45
0

The first one is many queries, the second is just one query. The DBMS can do a lot more optimizations when it has all the data to work with. Namely, it can do some sort of a bulk update which is much faster than adding individual rows to your tables. You also avoid the overhead of executing the query every single time (optimizing, logging, etc).

0

In first block : each insert operation acquires the lock and then after needs to be released, that takes time.

In second : one time hit to database that saves time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.