Node MySQL execute multiple queries the fastest possible

Question

Which is the fastest method gets the query to MYSQL, and then comes back to output:

console.log('queries finished', results)"

Is there an even better method? Please explain your answer!

Thanks!

Method 1:

var connection = mysql.createConnection({multipleStatements: true});

connection.query('SELECT ?; SELECT ?', [1, 2], function(err, results) {
  if (err) throw err;

  console.log('queries done', results);
});

Method 2:

const Db = mysql.createPool({
    connectionLimit: 7,
    dateStrings: true,
    multipleStatements: true
});

Db.getConnection(function(err, connection) {
    if(err) console.log(err);

    connection.query(`
        SELECT "1" AS "first";
        SELECT "2" AS "second";`, function(err, results) {
            connection.release();

            if(err) console.log(err);
            console.log('queries done', results); 
        }                    

    );

});   

Method 3:

const Db = mysql.createPool({
    connectionLimit: 7,
    dateStrings: true,
    multipleStatements: true
});

Db.getConnection(function(err, connection) {
    async.parallel([
        function(callback) {
            connection.query(`
                SELECT "1" AS "first"`, function(err, done) {
                    callback(err, done);
                }
            );                 
        },

        function(callback) {
            connection.query(`
                SELECT "2" AS "second"`, function(err, done) {
                    callback(err, done);
                }
            );                
        }
    ], function(err, results) {
        connection.release();
        if(err) console.log(err);

        console.log('queries finished', results);
    });
});

Method 4:

const Db = mysql.createPool({
    connectionLimit: 7,
    dateStrings: true,
    multipleStatements: true
});

async.parallel([
    function(callback) {
        Db.getConnection(function(err, connection) {
            connection.query(`
                SELECT "1" AS "first"`, function(err, done) {
                    connection.release();
                    callback(err, done);
                }
            );
        });
    },
    function(callback) {  
        Db.getConnection(function(err, connection) {
            connection.query(`
                SELECT "2" AS "second"`, function(err, done) {
                    connection.release();
                    callback(err, done);
                }
            );
        });
    }
], function(err, results) {
    if(err) console.log(err);
    console.log('queries finished', results);
}); 

And I didn't post it, but method 3 and 4 could also be done a without connection pool as well. There's also promises over using the npm module async, what is the fastest and why!? Thanks.

Blench

You can quite simply figure out this yourself using console.time('query') and console.timeEnd('query'). I would recommend to run each method at least 10 times to get an average. It should give you the answer you are looking for. – Murder

I did not know about this!! Awesome. – Blench

Answer 1

Method 1 and 2 are similar except that Pool creates a connection if all connections in the pool are used.

To determine which is faster, you need to know the computing power vs network bandwidth between your application server and your database server.

Here's why:

In method 1 and 2, you are using a single connection to execute multiple queries. If the processing power of the database machine is faster than then network bandwidth to send/receive multiple queries, then method 1 and 2 is more efficient. If the processing power of the database machine is slower than the bandwidth(e.g. both application / mysql server resides on the same machine), then method 3 and 4 will theoretically be faster.

Another factor is whether the statements depend on one another. Because step 1 and 2 essentially runs the statements synchronously, the entire set of operation is an atomic / consistent operation. For method 3 and 4, because they run asynchronously although they are triggered in parallel, there can be instances where a later statement complete earlier than an earlier statement. If that happens and there's dependency, method 3 and 4 will corrupt your data.

TL;DR

• Fast + Synchronous (insert/update, then select results) = Method 1, 2 (Use pooling to reduce the need to open new connections)
• Fast + Asynchronous(mass inserts) = Method 3, 4
• Slow connection between application/database server = Method 1,2

Transilluminate

Answer 2

Of the four above options using 3 real queries in my app, that each take about 1-3 seconds each to execute, method 3 ended up being the fastest for anyone interested.

Method 1 and 2 were about a full 1/2 second slower, and method 4 was only super slightly slower.

Edit: I did these benchmarks by using the console.time('query') suggestion in the comments above.

Blench

it makes sense 3 being the fastest, you use the same connection for both queries, both queries being called in parallel – Natator