Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers

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...).

share|improve this answer
1  
+1 Also, multiple index updates. –  Matt S Aug 17 '12 at 19:35
    
@MattS - Good point. Answer updated. –  Oded Aug 17 '12 at 19:45
add comment

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).

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.