Poor response times with mssql module

[Victor Espina]

Hi.  I am new to node.js I am trying to create a REST service (using Express) that access data from a SQL Server.  I am using mssql module with Tedious drivers.  I created this  module:

/*

  dbhelper.js

  Helper para acceso a base de datos

  Victor Espina S

  NOI Quality Software, Chile

*/

function dbhelper(params) {

  // Properties

  this.dbcp = params.dbcp; // DB ConnectionsPool object (created in app.js)

  this.config = require("../config");

  // Methods

  this.runQuery = runQuery;

  this.runSP = runSP;

  this.getRow = getRow;

  this.getScalar = getScalar;

}

// runQuery

// Ejecuta una consulta y devuelve el resultado

//

function runQuery(query) {

  var sql = require('mssql');

  var dbcp = this.dbcp;

  var runSelect = function(pool) {

    console.timeEnd("connecting");

    return new Promise(function(resolve, reject) {

      console.time("request");

      var request = new sql.Request(pool);

      console.timeEnd("request");

      console.time("querying");

      request.query(query)

             .then(resolve)

             .catch(reject);

    });

  }

  var returnResults = function(result) {

    console.timeEnd("querying");

    return new Promise(function(resolve, reject) {

      console.time("closing");

      dbcp.close();

      console.timeEnd("closing");

      resolve(result);

    });

  }

  var resolver = function(resolve, reject) {

    try {

      console.time("connecting");

      dbcp.connect()

          .then(runSelect)

          .then(returnResults)

          .then(resolve)

          .catch(reject);

    } catch (err) {

      reject(err);

    }

  }

  return new Promise(resolver);

}

module.exports = dbhelper;

and this is how I am using it:

function dbTest(req, res, next) {

  var dbhelper = require("../modules/dbhelper");

  var db = new dbhelper({ dbcp: res.app.locals.dbcp });

  db.runQuery("select clave,texto from registro order by clave")

    .then(function(result) {

      res.send(result);

    });

}

Everything works great, but the response times are completely unaceptable:  10s for a 10 rows query!!!  Check this image from the node noconsole after two consecutives requests to the server:

Any ideas about what may be going on ?  I tried this same query from a client app and it takes less than 1s.  I am running node.js under OSX and the SQL Server (2016) is running under a virtualized Windows Server 2016, running in the same osx machine.

Victor

[tpx1]

Hi,

I' am a little bit confused. Why do you wrap a Promise into Promises?
It does not make sense, because Promises themselves check if the
returned value is a promise, so the promise pipeline does what you do
internally. There is no need of try/catch, if you do no error handling.
The Promise mechanics wraps your function into a try/catch block. Do
only the transformation in the fullfill function and catch the error in
an reject function and handle it there.

var resolver = function(resolve, reject) {
> try {
> console.time("connecting");
> dbcp.connect()
> .then(runSelect)
> .then(returnResults)
> .then(resolve)
> .catch(reject);
> } catch (err) {
> reject(err);
> }
> }

Instead of using such methods write this.

console.time("connecting");
return dbcp.connect()
.then(runSelect)
.then(returnResults);

Every time, you run new dbHelper, you have a synchronized calls on the
require method. It could be possible that there is your bottle neck. All
things, that you require, you should require on the top of your module,
not in functions, methods.

I hope it will help a little bit.


Thomas

> <https://lh3.googleusercontent.com/-oL3nl7XVGJg/WOuTKkNhzHI/AAAAAAAAA20/JkO_M8BxS20fQbuG-DvjmJqm70tYzCfEgCLcB/s1600/Screen%2BShot%2B2017-04-10%2Bat%2B10.56.29%2BAM.png>

>
>
>
>
>
> Any ideas about what may be going on ? I tried this same query from a
> client app and it takes less than 1s. I am running node.js under OSX
> and the SQL Server (2016) is running under a virtualized Windows Server
> 2016, running in the same osx machine.
>
>
> Victor
>

> --
> Job board: http://jobs.nodejs.org/
> New group rules:
> https://gist.github.com/othiym23/9886289#file-moderation-policy-md
> Old group rules:
> https://github.com/joyent/node/wiki/Mailing-List-Posting-Guidelines
> ---
> You received this message because you are subscribed to the Google
> Groups "nodejs" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to nodejs+un...@googlegroups.com
> <mailto:nodejs+un...@googlegroups.com>.
> To post to this group, send email to nod...@googlegroups.com
> <mailto:nod...@googlegroups.com>.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/nodejs/233108be-6214-45da-bb63-7f038910ba4c%40googlegroups.com
> <https://groups.google.com/d/msgid/nodejs/233108be-6214-45da-bb63-7f038910ba4c%40googlegroups.com?utm_medium=email&utm_source=footer>.
> For more options, visit https://groups.google.com/d/optout.

[Victor Espina]

Thanks for your answer and your tips.  I am still new to this NodeJS / Javascript programming, specially regarding promises.  I tried a simplified version of the code test, just to leave out anything that may be getting on the way, but the times remained the same:

function queryTest2(req, res, next) {
  var config = require("../config");

  var sql = require("mssql");

  console.time("Total request time");
  console.time("sql.connect");
  var pool = sql.connect(config.db)
                .then(function(pool) {
                  console.timeEnd("sql.connect");
                  var request = pool.request();
                  console.time("request.query");
                  return request.query("SELECT clave, texto FROM registro");
                })
                .then(function(result) {
                  console.timeEnd("request.query");
                  console.timeEnd("Total request time");
                  res.send(result);
                })
                .catch(function(err) {
                  res.send(err);
                });
}

Also, I tried with another packaged called "Seriate", based on mssql package, and times were exactly what I was expecting for: < 1s, so definitely the problem is in my code but I still can't figure it out what I am doing wrong.

Victor