Saturday 22 April 2017

Why does Query execution time is so higher for Mysql Native driver for node.js , possible alternatives?

Why does same query takes around 300 ms more than actual execution time when using Mysql native driver for Nodejs .with and without using "create pool" options.

Please refer highlighted section in below attached screenshot

Mysql workbench execution

Also for native driver execution time,Please refer below attached screenshot,

enter image description here

Codebase for node.js Mysql Native driver

var mysql = require('mysql');
var connectionpool = mysql.createPool({
    connectionLimit: 100, //important
    host: 'localhost',
    user: config.development.username,
    password: config.development.password,
    database: config.development.database,
    multipleStatements: true,
});

exports.getConnection = function(callback) {
    connectionpool.getConnection(callback);
};

 var pre_query = new Date().getTime();
    var sqlstatements = "select  * from employees where recordstate<>'raw'and  DATE(createdAt) " + "between ('1982-03-24') and ('2017-04-23')    order by employeesID desc limit 20 offset 0;" + "select COUNT(*) as count   from employees where recordstate<>'raw'and  " + "DATE(createdAt) between ('1982-03-24') and ('2017-04-23')    order by employeesID desc";
    connections.getConnection(function(err, c) {
        console.log(sqlstatements)
        c.query(sqlstatements, function(err, projects) {


            console.log(err);
            if (!err) {
                c.release();

                var red = new Object();
                red.rows = JSON.parse(JSON.stringify(projects[0]));
                red.count = JSON.parse(JSON.stringify(projects[1]))[0].count;
                var post_query = new Date().getTime();
                // calculate the duration in seconds
                var duration = (post_query - pre_query) / 1000;
                console.log(duration)
                    // console.log(red);
                res.json(red);

            }
        })
    })



via Rizwan Patel

No comments:

Post a Comment