Node ms sql blocking code

Question

I am trying to execute a stored procedure on a remote MS sql server from my node application using node-mssql .

Problem : The call seems to be blocking and not asynchronos, since any other request to the application from another client, does not get responded to unless the first request is complete .

 sql.connect(config).then(function() {
        new sql.Request()
        .input('mysp', sql.TVP, tvp)
        .execute('spParam1').then(function(recordsets) {
              callback(null, recordsets[0]);
        }).catch(function(err) {
            callback(err, []);
        });
    }).catch(function(err) {
         callback(err, []);
    });

The 'callback' mentioned is the function call back expected by the api .

I tried a simple example of

   setTimeout(function() {
                 callback("error.message", []);
   }, 60000);

And in this case both clients work, and Node gives back the thread for the 2nd request.

I have tried the example using promises,callbacks and streaming. All end up blocking the I/O.

What am i overlooking here ?

EDIT : Just used a query instead of an 'execute', and it works as expected, without blocking i/o

dbConn.connect().then(function () {
        logger.info("sql connection established")
        var request = new sql.Request(dbConn);
        // request.input('mysp', sql.TVP, tvp)
        //     .execute("spParam1").then(function (recordSet) {
            request.query("WAITFOR DELAY '00:00:22'").then(function(){
                dbConn.close();
                callback("no data", []);
            }).catch(function (err) {
                callback(err, []);
                logger.error(err)
                dbConn.close();
            });
    }).catch(function (err) {
        logger.error(err)
        callback(err, []);
    });

Found something interesting . I created a simple sp which does a delay of 40 seconds, simulating my original sp , and executed the same request MINUS the parameter . It's note worthy that my parameter is a table value with data ~ 9k excel rows The sp executed without blocking the io, when there was no data attached.I further found that the time for which the Io is blocked is proportional to the data, if the data decreased, thread was released faster. — Rajat banerjee, Jun 28 '16 at 04:28

Lou Guerrero · Answer 1 · 2016-08-03T00:12:40.867

Here is a test I performed without any blocking

SQL Procs

With a 1 minute wait

CREATE PROCEDURE [dbo].[usp_node_test_wait]
AS
BEGIN
    SET NOCOUNT ON;

    WAITFOR DELAY '00:01:00';
    SELECT GETDATE() [date]
END

With no wait

CREATE PROCEDURE [dbo].[usp_node_test]
AS
BEGIN
    SET NOCOUNT ON;

    SELECT GETDATE() [date]
END

And the node code Connect to the db the open up a web server

var sql = require('mssql');
var http = require('http');

var PORT = 8080 || process.env.PORT;


var config = {
    user: 'node',
    password: 'node',
    server: 'localhost',
    database: 'nodedb',
    options: {
        instanceName: 'sqlexpress'
    },
    pool: {
        max: 10,
        min: 0,
        idleTimeoutMillis: 30000
    },
    requestTimeout: 70000
}


var connection = new sql.Connection(config);


connection.connect(function(err){
    if(err){
        console.log('Error connecting to the DB ', err);
    } else {
        console.log('Database Connection Established');
        var server = http.createServer(handleRequest);
        server.listen(PORT, function(){
            console.log('Web server listening on http://localhost:%s', PORT);
        });

    }
});

function handleRequest(request, response){

    var req = request;
    console.log('Url Requested: ', req.url)
    if(req.url == '/nowait'){
        var request = new sql.Request(connection);
        request.execute('usp_node_test', function(err, recordsets, returnValue, affected){
            if (err) {
                console.log('Error: ', err)
            } else {
                var data = recordsets[0][0];
                console.log(data.date);
                response.end('Date returned by the server: ' + data.date);
            }

        });
    } else {
        var request = new sql.Request(connection);
        request.execute('usp_node_test_wait', function(err, recordsets, returnValue, affected){
            if (err) {
                console.log('Error: ', err)
            } else {
                var data = recordsets[0][0];
                console.log(data);
                response.end('Date returned by the server: ' + data.date);
            }
        });
    }

}

use http://localhost/nowait to have sql process in request ASAP use http://localhost/anything else to hang waiting for 1 minute

You'll see that one request doesn't interfere with the other, hence no blocking

Rajat. What I'm suggesting the way you are connecting is causing the blocking. I created some real code to verify that using connection pooling no blocking occurs. — Lou Guerrero, Aug 03 '16 at 00:02
I created two sql procedures, one that waits for 1 minute and another that returns the data asap. — Lou Guerrero, Aug 03 '16 at 00:03
I can verify that the connection is not a concern. As i have mentioned, in case of a delay in query execution eg request.query("WAITFOR DELAY '00:00:22'") calls are not blocked, and both requests are served. It's actually similar to your example of "usp_node_test_wait". The issue as i figured was the time consumed 'while' making the request from my box to the server. Once the request is made, the other call is responded to. — Rajat banerjee, Aug 05 '16 at 06:30

score 0 · Answer 2 · answered Nov 16 '16 at 21:17

0

Just got a confirmation from the guys at Tedious https://github.com/tediousjs/tedious/issues/475#issuecomment-260009906

looks like it's a blocking call .

answered Nov 16 '16 at 21:17

Rajat banerjee

1,781
3
17
35

Node ms sql blocking code

2 Answers2