CODEWITHSUNDEEP
phpmysqlsql
jskidd3
jskidd3

Reputation: 4783

Use MySQL insert multiple times or 1 big query?

I have the opportunity to insert lots of data (about 30 rows every 5 minutes) in two ways:

A) Use PHP to build an insert function and for every new piece of data simply call the function and insert around 30 times

B) Use PHP and build one big query by appending to the SQL string for each bus and then only insert once

Which of these is more efficient?

Upvotes: 0

Views: 361

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269673

The issue with doing individual inserts or bulk inserts -- in this case -- should not be primarily about performance. Any database should be able to handle an insert rate of 6 records / minute.

As mentioned in the comments and other answer, a single call to the database is going to require less overall time than multiple calls. But, is there a downside to such bulk loading?

Yes, there is. First, although the overall insert takes less time, it all occurs during one period. So, the period of time when the tables/rows are locked is actually longer. Such locking can interfere with other queries. In other words, bulk loads could result in occasional hiccups in performance.

A more important consideration, though, gets to the heart of storing data in an ACID-compliant database. What happens if the application goes down before the bulk insert takes place? Are you willing to accept data loss? With individual updates, you know that each record gets into the database, once the insert completes successfully. With bulk updates, you either have to accept data loss or implement another mechanism to prevent it.

As a general rule, it is better to get code to work correctly before embarking on premature optimizations. Because the volume of updates is still rather small, I think this is a premature optimization and individual updates are probably the way to go.

Upvotes: 1

mituw16
mituw16

Reputation: 5250

The second option would be more efficient as there are less database calls.

You could try something along the lines of the following. 

insert into table imatable values (something, something, something), (somethingelse, somethingelse, somethingelse), (etc, etc, etc)

Upvotes: 1

Related Questions