Single batched query or mapped queries for efficiency?

I'm running node-mysql for inserting data into my MySQL database running DynamoDB.

I have the options of performing 'mini' insert queries inside a for loop:

var myData= [{"key": "abc", "value": "xyz"},{..},{..}]
var mapData = myData.map(function(data){
    var key = data.key;
    var value = data.value;
    var payload = {
      key: key,
      value: value
    };

    connection.query("INSERT INTO mytable SET ?", payload, function(err,result){
       //Do stuff
    });
});

Alternatively I can use a nested array approach:

myData = [["key", "value"], [.., ..], [.., ..]]
connection.query("INSERT INTO mytable (key, value) VALUE ?", [myData], function(err, result){
  //Do Stuff..
});

My database is very large and the myData array will also be very large.

Which method is the most efficient?

Solution

In scenarios like this, if performance is important then it's a good idea to do your own tests on the hardware you intend to run the application on. Factors such network latency, disk latency and available RAM come into play - along with how busy the database server may be with other queries or tasks.

In many scenarios, you would expect the former operation to be fastest as it only needs to go through the MySQL query analyser once and the data can be written in bulk to persistent storage rather than as many small writes - although once again that depends on what flush configuration options your database and OS are using.

The downside of a large write is that the database needs to wait until it has the whole query before it can process it because it has to check the entire write is valid, and depending on the MySQL table format, it may lock the table preventing other operations from proceeding. Short smaller writes may get the job done quicker.

Note also that MySQL has a max_allowed_packet, setting which needs increasing for genuinely large writes.

TLDR - If it's important, test both and use what works in your specific application.