Node.js with long running SQL query...

[trivedimehulk]

Can someone please guide me how do I solve following problem?

1. My node app is talking to SQL stored procedure
2. SP takes 5 seconds to execute
3. Since node is by design Async for IO operations, it returns response to client browser without waiting for SQL results. Again, since its single threaded event based, I do not want to block all clients till my SP returns results.

How do I solve this problem in my design?

[gireeshpunathil]

• Ideally an SQL DB software will have an associated client driver.
• Also, ideally the client driver that is ported to work with noe.js will provide async API(s) for its primary operations.
• If you are making use of those APIs, you can design your code in such a manner to handle the DB result in asynchronous callbacks, while catering to new client requests in-line.

Hope this clarifies. If you need clarifications in any of the above, please let me know.

[santimacia]

Considering that the driver is async, you should wait the callback before send the response to the client browser.

A good way to implement if the queries take to long, could it be with WebSockets.

[ORESoftware]

@trivedimehulk share your code

[trivedimehulk]

I think I resolved the issue.

I was doing a wrong approach.

Now my SQL calls are sync and they are not blocking other queries too.

Thank you

Mehul Trivedi trivedimehulk@gmail.com +91.999.875.1555

On Thu, Nov 23, 2017 at 12:06 AM, Operations Research Engineering Software+ notifications@github.com wrote:

@trivedimehulk https://github.com/trivedimehulk share your code

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/nodejs/help/issues/967#issuecomment-346438055, or mute the thread https://github.com/notifications/unsubscribe-auth/AFDKdebJaF4QUETqpM4clkCBXKqHjZA8ks5s5Gm5gaJpZM4QbvJ0 .

[ORESoftware]

The only way they could be sync and not be blocking other queries, would be if they were running in their own node.js process.

[trivedimehulk]

Okay.

So I thought I fixed this issue, but I was wrong. Following is what I found:

My app:

---

const sqlInstance = require('mssql');const express = require('express');const app = express(); app.get('/', (req, res) => {

if (req.query.p == 'sql') {

    var setUp = {
        server: 'myservername',
        database: 'Products',
        user: 'Products',
        password: 'Products',
        port: 1433,
        options: {
            encrypt: true
        }
    };

    sqlInstance.connect(setUp).then(function (connection) {
        var _sqlRequest = new sqlInstance.Request(connection)
            .query("[GetAllProducts]")
            .then(function (dbData) {
                if (dbData == null || dbData.length === 0)
                    res.send('No data boss!');
                res.send('Loaded big data');
                sqlInstance.close();
            })
            .catch(function (error) {
                console.dir(error);
            });
    })

} else {
    res.send('Responded to static request!!!');
}})

var printLog = function(log){ console.log(log);};

app.listen(3000, () => console.log('Example app listening on port 3000!'))

---

Explanation: Basically in above app, all I am doing is, if I get a request with parameter "sql", I execute a sql stored proc, which gives me huge data. If I do not receive this param, I just respond with plain text.

Issue: When I execute with SQL param, the SQL call is blocking other non SQL param browser requests too.

I think above issue is with the way mssql package is designed. I also tried to wrap this function in http://alexeypetrushin.github.io/synchronize/docs/index.html which works perfactly fine for other packages other then mssql.

My issue is still open and I will find a solution to this as I have to make a big architectural decision in my company after this finding.

Thank you

Mehul Trivedi trivedimehulk@gmail.com +91.999.875.1555

On Thu, Nov 23, 2017 at 2:57 PM, Operations Research Engineering Software+ < notifications@github.com> wrote:

The only way they could be sync and not be blocking other queries, would be if they were running in their own node.js process.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/nodejs/help/issues/967#issuecomment-346567010, or mute the thread https://github.com/notifications/unsubscribe-auth/AFDKdT2xdv_qr2f09Yc00nznPhimxHLiks5s5TqCgaJpZM4QbvJ0 .

[the1mills]

Can you use code blocks to format your code?

Like this

[the1mills]

```js
Your code here

[trivedimehulk]

I didnt get that.. but here is formatted version

---

const sqlInstance = require('mssql'); const express = require('express'); const app = express(); app.get('/', (req, res) => {

if (req.query.p == 'sql') {

    var setUp = {
        server: 'myservername',
        database: 'Products',
        user: 'Products',
        password: 'Products',
        port: 1433,
        options: {
            encrypt: true
        }
    };

    sqlInstance.connect(setUp).then(function (connection) {
        var _sqlRequest = new sqlInstance.Request(connection)
            .query("[GetAllProducts]")
            .then(function (dbData) {
                if (dbData == null || dbData.length === 0)
                    res.send('No data boss!');
                res.send('Loaded big data');
                sqlInstance.close();
            })
            .catch(function (error) {
                console.dir(error);
            });
    })

} else {
    res.send('Responded to static request!!!');
}

})

var printLog = function(log){ console.log(log);};

app.listen(3000, () => console.log('Example app listening on port 3000!'))

---

Mehul Trivedi trivedimehulk@gmail.com +91.999.875.1555

On Fri, Nov 24, 2017 at 12:22 AM, Alexander Mills notifications@github.com wrote:

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
<https://github.com/nodejs/help/issues/967#issuecomment-346682582>, or mute
the thread
<https://github.com/notifications/unsubscribe-auth/AFDKdfL9vh-2vxbGaXIoojAyScwJiTSEks5s5b7tgaJpZM4QbvJ0>
.

[ORESoftware]

Can you try using the sequelize NPM package here:

http://docs.sequelizejs.com

[trivedimehulk]

It also has same issue. When I execute the call for long sql, it stops serving all other requests.

I have a hint using synchronize.js but it only works with file.read functions.

Mehul Trivedi trivedimehulk@gmail.com +91.999.875.1555

On Fri, Nov 24, 2017 at 12:07 PM, Operations Research Engineering Software+ notifications@github.com wrote:

Can you try using the sequelize NPM package here:

http://docs.sequelizejs.com

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/nodejs/help/issues/967#issuecomment-346753147, or mute the thread https://github.com/notifications/unsubscribe-auth/AFDKdT5iOprKX9ggKQTdmFhh7FgYY17fks5s5mQWgaJpZM4QbvJ0 .

[gireeshpunathil]

@@ -10,4 +10,5 @@ 
sqlInstance.connect(setUp).then(function (connection) {
sqlInstance.connect(setUp).then(function (connection) {
  var _sqlRequest = new sqlInstance.Request(connection)
  .query("[GetAllProducts]")
  .then(function (dbData) {
    if (dbData == null || dbData.length === 0)
      res.send('No data boss!');
      res.send('Loaded big data');
      sqlInstance.close();
  })
  .catch(function (error) {
    console.dir(error);
  });
+  console.log('async check')
})

can you add a statement as shown above to prove / disprove your blocking theory? I am not familiar with the sql driver in question and its async nature, but by assuming that a reasonable node.js client for a DB would have designed its API asynchronously, the added print will execute before the query completion. On the other hand if it is not, the promise wrapping is just a semantic coating, does not help here, and you will have to raise it up with the DB vendor. But let us see!

[trivedimehulk]

Gireesh

This would work as its logging to console. I will get both prints to console fine.

This will work with console logging etc. But what about the browser? Assume node already responded to my browser with res.send and then I get SQL results, which I want to send to browser who raise that query.. that is where this does not work.

Mehul Trivedi trivedimehulk@gmail.com +91.999.875.1555

On Mon, Nov 27, 2017 at 1:49 PM, Gireesh Punathil notifications@github.com wrote:

@@ -10,4 +10,5 @@ sqlInstance.connect(setUp).then(function (connection) { sqlInstance.connect(setUp).then(function (connection) { var _sqlRequest = new sqlInstance.Request(connection) .query("[GetAllProducts]") .then(function (dbData) { if (dbData == null || dbData.length === 0) res.send('No data boss!'); res.send('Loaded big data'); sqlInstance.close(); }) .catch(function (error) { console.dir(error); });+ console.log('async check') })

can you add a statement as shown above to prove / disprove your blocking theory? I am not familiar with the sql driver in question and its async nature, but by assuming that a reasonable node.js client for a DB would have designed its API asynchronously, the added print will execute before the query completion. On the other hand if it is not, the promise wrapping is just a semantic coating, does not help here, and you will have to raise it up with the DB vendor. But let us see!

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/nodejs/help/issues/967#issuecomment-347108705, or mute the thread https://github.com/notifications/unsubscribe-auth/AFDKdaqqlumkbIXcAOPBDh3vgwMQYVObks5s6nCmgaJpZM4QbvJ0 .

[gireeshpunathil]

I did not get you - among these actions:

1. client request
2. server parsing
3. db query
4. server response composition
5. server response write

can you please tell me which order is desired, and which order is actually happening?

[trivedimehulk]

G

If you have 2 mins, I can do a join.me session to demo you this.

Any number you have that I can call?

Thank you

Mehul Trivedi trivedimehulk@gmail.com +91.999.875.1555

On Mon, Nov 27, 2017 at 3:04 PM, Gireesh Punathil notifications@github.com wrote:

I did not get you - among these actions:

1. client request
2. server parsing
3. db query
4. server response composition
5. server response write

can you please tell me which order is desired, and which order is actually happening?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/nodejs/help/issues/967#issuecomment-347126401, or mute the thread https://github.com/notifications/unsubscribe-auth/AFDKdRmgFTDMOC9PAiCaWHtrxPwTnZOYks5s6oIJgaJpZM4QbvJ0 .

[gireeshpunathil]

If we do it ourselves we miss the collective intelligence of this community. Moreover, not in a position to join.me, sorry. Instead can you please elaborate on the expected and the actual sequence, in response to my previous query?

[trivedimehulk]

I agree with you.

Here is the answer to your queries.

===========

I did not get you - among these actions:

1. client request
2. server parsing
3. db query
4. server response composition
5. server response write

can you please tell me which order is desired, and which order is actually happening?

Answer:

Order desired:

client request 1= Browser one requests http://localhost:3000/?p=sql

server parsing = server parses query string

db query = server starts executing big database call to load 250,000 - rows (for example only)

server response composition = server finishes DB execution, and sends plain response as "sql completed"

server response write = res.send

** PLS NOTE below sequence is happening parellely to above sequence - just the query string is changed *****

client request 2 = Browser one requests http://localhost:3000/

server parsing = server parses query string

db query = server starts executing normal code without anything (as query string is not having sql)

server response composition = sends plain response as " no sql execution. plain response."

server response write = res.send Order actually happening:

When I start sequence 1, the sequence 2 never gets responded till sequence 1 is completed

===========

Mehul Trivedi trivedimehulk@gmail.com +91.999.875.1555

On Mon, Nov 27, 2017 at 5:01 PM, Gireesh Punathil notifications@github.com wrote:

If we do it ourselves we miss the collective intelligence of this community. Moreover, not in a position to join.me, sorry. Instead can you please elaborate on the expected and the actual sequence, in response to my previous query?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/nodejs/help/issues/967#issuecomment-347155562, or mute the thread https://github.com/notifications/unsubscribe-auth/AFDKddjhgzDDIYm2Bhi190fwDk_wCuAIks5s6p2RgaJpZM4QbvJ0 .

[gireeshpunathil]

based on your description what I guess you are saying is that an sql based request is blocking a non-sql based request that is issued subsequently, which is nearly impossible - unless the sql based request blocks the process through a blocking I/O call.

Looks like we need to see this in action. I will let you know when we can do a screen share.

[trivedimehulk]

Okay. Sure. Will wait.

I am pretty sure this must be my design fault or I am doing something wrong as many big companies have their CRUD app on mean stack. They would have faced this already.

Let me know when I can Demo.

On Tue, 28 Nov 2017 at 08:34, Gireesh Punathil notifications@github.com wrote:

based on your description what I guess you are saying is that an sql based request is blocking a non-sql based request that is issued subsequently, which is nearly impossible - unless the sql based request blocks the process through a blocking I/O call.

Looks like we need to see this in action. I will let you know when we can do a screen share.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/nodejs/help/issues/967#issuecomment-347399427, or mute the thread https://github.com/notifications/unsubscribe-auth/AFDKdTBOR9y6kLi8JQQcDoYDfNnZeSVlks5s63hSgaJpZM4QbvJ0 .

-- MT +91.999.875.1555 Gmail mobile

[md9projeto]

trivedimehulk Were you able to solve that?

[trivedimehulk]

Nope.

Are you facing same issue?

Mehul Trivedi trivedimehulk@gmail.com +91.999.875.1555

On Thu, Dec 14, 2017 at 12:20 AM, md9projeto notifications@github.com wrote:

trivedimehulk Were you able to solve that?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/nodejs/help/issues/967#issuecomment-351485869, or mute the thread https://github.com/notifications/unsubscribe-auth/AFDKdSx2UOIkVL5QTL4QwAfMK2oQsXS9ks5tABx-gaJpZM4QbvJ0 .

[gireeshpunathil]

@trivedimehulk - hi! how about doing a scree session today? if so, please share the details.

[trivedimehulk]

Hello Gireesh

Yes I am ready.

Shall I send you join.me url?

Mehul Trivedi trivedimehulk@gmail.com +91.999.875.1555

On Thu, Dec 14, 2017 at 1:49 PM, Gireesh Punathil notifications@github.com wrote:

@trivedimehulk https://github.com/trivedimehulk - hi! how about doing a scree session today? if so, please share the details.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/nodejs/help/issues/967#issuecomment-351639900, or mute the thread https://github.com/notifications/unsubscribe-auth/AFDKdTf4STquiV0ZJ9ZDR0FQN_kSYzvuks5tANosgaJpZM4QbvJ0 .

[md9projeto]

No.Just interested. Wanting to know if NODE.JS is good for ERP Development,we in this world are still using statefull servers

2017-12-14 5:28 GMT-02:00 trivedimehulk notifications@github.com:

Nope.

Are you facing same issue?

Mehul Trivedi trivedimehulk@gmail.com +91.999.875.1555 <+91%2099987%2051555>

On Thu, Dec 14, 2017 at 12:20 AM, md9projeto notifications@github.com wrote:

trivedimehulk Were you able to solve that?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/nodejs/help/issues/967#issuecomment-351485869, or mute the thread https://github.com/notifications/unsubscribe-auth/ AFDKdSx2UOIkVL5QTL4QwAfMK2oQsXS9ks5tABx-gaJpZM4QbvJ0 .

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/nodejs/help/issues/967#issuecomment-351630128, or mute the thread https://github.com/notifications/unsubscribe-auth/Ac2IbwVAy8E80QszTSmZkMEmoXI2gtRVks5tAM4KgaJpZM4QbvJ0 .

[gireeshpunathil]

sure, please!

[trivedimehulk]

​https://join.me/976-808-198

Is there a way I can call you? Any number?​

Mehul Trivedi trivedimehulk@gmail.com +91.999.875.1555

On Thu, Dec 14, 2017 at 4:22 PM, Gireesh Punathil notifications@github.com wrote:

sure, please!

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/nodejs/help/issues/967#issuecomment-351676916, or mute the thread https://github.com/notifications/unsubscribe-auth/AFDKdXJTi_EmC6LXvbSArGR1p3NrnH8Uks5tAP38gaJpZM4QbvJ0 .

[trivedimehulk]

Girish

Thank you for your valuable time on the session.

As discussed please find meeting minutes as below:

1. MT to send existing code to Girish - shared zip [ https://drive.google.com/file/d/1FOIGqE1YaKMVNhiBZHkhaE-iKyB0B2Cm/view?usp=sharing]

   • [consists of app & SQL schema - to create table and the stored procedure]

2. MT to try no promise/callback base approach and share the output on where the block is happening

3. Which SQL package has been used? https://www.npmjs.com/package/mssql

Please let me know if you need any further information.

Thank you

Mehul Trivedi trivedimehulk@gmail.com +91.999.875.1555

On Thu, Dec 14, 2017 at 4:24 PM, Mehul Trivedi trivedimehulk@gmail.com wrote:

​https://join.me/976-808-198

Is there a way I can call you? Any number?​

Mehul Trivedi trivedimehulk@gmail.com +91.999.875.1555

On Thu, Dec 14, 2017 at 4:22 PM, Gireesh Punathil < notifications@github.com> wrote:

sure, please!

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/nodejs/help/issues/967#issuecomment-351676916, or mute the thread https://github.com/notifications/unsubscribe-auth/AFDKdXJTi_EmC6LXvbSArGR1p3NrnH8Uks5tAP38gaJpZM4QbvJ0 .

[trivedimehulk]

Hello Girish

Update on using callback instead of promises:

I modified my SQL code to following:

---

sqlInstance.connect(setUp, err => { new sqlInstance.Request().query('[GetAllProducts]', (err, result) => { fn(); sqlInstance.close(); }) }) sqlInstance.on('error', err => { printLog(err); })

---

This too have the same problem. It does block other requests.

When I debugged by adding console logs, I can see when it executes .query() method, that is where it starts blocking other requests.

Hope this helps.

Thank you

Mehul Trivedi trivedimehulk@gmail.com +91.999.875.1555

On Thu, Dec 14, 2017 at 5:28 PM, Mehul Trivedi trivedimehulk@gmail.com wrote:

Girish

Thank you for your valuable time on the session.

As discussed please find meeting minutes as below:

1. MT to send existing code to Girish - shared zip [ https://drive.google.com/file/d/1FOIGqE1YaKMVNhiBZHkhaE- iKyB0B2Cm/view?usp=sharing] - [consists of app & SQL schema - to create table and the stored procedure]

2. MT to try no promise/callback base approach and share the output on where the block is happening

3. Which SQL package has been used? https://www.npmjs.com/package/mssql

Please let me know if you need any further information.

Thank you

Mehul Trivedi trivedimehulk@gmail.com +91.999.875.1555

On Thu, Dec 14, 2017 at 4:24 PM, Mehul Trivedi trivedimehulk@gmail.com wrote:

​https://join.me/976-808-198

Is there a way I can call you? Any number?​

Mehul Trivedi trivedimehulk@gmail.com +91.999.875.1555

On Thu, Dec 14, 2017 at 4:22 PM, Gireesh Punathil < notifications@github.com> wrote:

sure, please!

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/nodejs/help/issues/967#issuecomment-351676916, or mute the thread https://github.com/notifications/unsubscribe-auth/AFDKdXJTi_EmC6LXvbSArGR1p3NrnH8Uks5tAP38gaJpZM4QbvJ0 .

[gireeshpunathil]

@trivedimehulk - thanks, it was a wonderful screen-share experience; I never had this level of clarity and visibility in any remote debugging sessions.

For other's awareness: the conclusive inference from the session is that Request.query seem to hold up the main thread, while the API spec indicates it is async.

while it is apparently a clear case for the owners of the module to inspect, absence of any native code in the package makes me think otherwise - a blocking I/O is impossible from pure JS I guess.

Give me some time, I need to setup the DB server and client. Meanwhile, if it is possible for you, can you replicate this in Linux? If so, run it with

strace -e read -T -f <node app>

this will give an indication if there is a blocking read. (the method applies for windows too, only that I don't know the right tools)

[trivedimehulk]

@gireeshpunathil - Update - I created another version of my demo to connect to Mongo and do the same thing. It does not block the thread. Mongo - behaves in async manner which proves the design of node runtime.

I will continue my research for strace to provide you the logs.

Thank you

[gireeshpunathil]

@trivedimehulk - I was able to recreate this issue with an mssql setup locally. Here is a shorter version of the recreate and its output:

const mssql = require('mssql')
var options = {
server: 'localhost\\foo',
database: 'master',
user: 'foo',
password: 'bar',
port: 1433,
debug: true,
options: { encrypt: false}
}
console.log(process.hrtime()[0] + ' : entry')
mssql.connect(options, (c) => {
  console.log(process.hrtime()[0] + ' : connect')
  var q = new mssql.Request(c)
  console.log(process.hrtime()[0] + ' : request')
  q.query('[GetAllProducts]', (e, d) => {
    console.log(process.hrtime()[0] + ' : query')
    mssql.close()
    console.log(process.hrtime()[0] + ' : ' + 'close')
  })
})

$ node foo.js

174602 : entry
174602 : connect
174602 : request
174610 : query
174610 : close

As can be seen, the time spent in the query call is around 8 seconds, and the thread is blocked for that long. I did a profiling and saw that the data reception and parsing logic in the tedius module is where most of the time is spent.

 [Bottom up (heavy) profile]:
  Note: percentage shows a share of a particular caller in the total
  amount of its parent calls.
  Callers occupying less than 1.0% are not shown.

   ticks parent  name
    199   42.7%  C:\Program Files\nodejs\node.exe
    160   80.4%    C:\Program Files\nodejs\node.exe
    108   67.5%      LazyCompile: *str_format.format C:\gireesh\code\node_modules\sprintf\lib\sprintf.js:137:30
    107   99.1%        LazyCompile: *dataToString C:gireesh\code\node_modules\tedious\lib\packet.js:170:33
    107  100.0%          Function: ~data C:\gireesh\code\node_modules\tedious\lib\debug.js:62:25
    107  100.0%            Function: ~logPacket C:\gireesh\code\node_modules\tedious\lib\message-io.js:226:30

I suspect the module is performing blocking reads and parsing of db data - something like:

stream.on('readable', (d) =>  {
  while(stream.read() != null) {
    // transform data
    // process data
  }
})

this does not fare well with event driven semantics. As long as the stream source can supply data at the rate in which it is being processed in the sink, this will cause throttling in the event loop.

Looking at the tedius project repo, I see several reference to this blocking behavior potentially due to heavy parsing of data. I haven't debugged in depth to isolate it further to see whether it is due to tight reading or heavy parsing, or both.

https://github.com/tediousjs/tedious/issues/319 https://github.com/tediousjs/tedious/issues/475 https://github.com/tediousjs/tedious/issues/467

I recommend you to raise this with mssql / tedius or track their existing bug reports. I haven't observed similar blocking I/O behavior with mongodb or cloudant, but I assume sticking to mssql is a business requirement for you?

Please let me know if you need more information / clarification on this.

[gireeshpunathil]

Couple of edits to the previous comment:

1. A more convincing test case is as follows:

const mssql = require('mssql')
console.log(process.hrtime()[0] + ' : entry')
mssql.connect('mssql://foo:bar@localhost\\foo/master', (c) => {
  console.log(`${process.hrtime()[0]}: connect`)
  var q = new mssql.Request(c)
  console.log(`${process.hrtime()[0]}: request`)
  q.query('[GetAllProducts]', (e, d) => {
    console.log(`${process.hrtime()[0]}: query`)
    mssql.close()
  })
  setTimeout(() => {
    console.log(`${process.hrtime()[0]}: stagnant loop!`);
  }, 100)
})

Output:

1608 : entry
1609: connect
1609: request
1615: query
1615: stagnant loop!

2. Please read: this will cause throttling in the event loop. as this will block reachability to (isolate) the event loop.

[trivedimehulk]

Hello Girish

Thank you for sharing the finding & confirming the issue.

Yes - with mongo, its not an issue.

I will open up a bug with driver vendor.

For now, I have decided to move along with Mongo instead of SQL.

Regards

Mehul Trivedi trivedimehulk@gmail.com +91.999.875.1555

On Sat, Dec 23, 2017 at 12:52 PM, Gireesh Punathil <notifications@github.com

wrote:

Couple of edits to the previous comment:

1. A more convincing test case is as follows:

const mssql = require('mssql')console.log(process.hrtime()[0] + ' : entry')mssql.connect('mssql://foo:bar@localhost\foo/master', (c) => { console.log(${process.hrtime()[0]}: connect) var q = new mssql.Request(c) console.log(${process.hrtime()[0]}: request) q.query('[GetAllProducts]', (e, d) => { console.log(${process.hrtime()[0]}: query) mssql.close() }) setTimeout(() => { console.log(${process.hrtime()[0]}: stagnant loop!); }, 100) })

Output:

1608 : entry 1609: connect 1609: request 1615: query 1615: stagnant loop!

1. Please read: this will cause throttling in the event loop. as this will block reachability to (isolate) the event loop.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/nodejs/help/issues/967#issuecomment-353711742, or mute the thread https://github.com/notifications/unsubscribe-auth/AFDKdfDtzywsa69S4u4w4rbOUn6ZvdPYks5tDKoZgaJpZM4QbvJ0 .

[gireeshpunathil]

@trivedimehulk - so, close?

[trivedimehulk]

Yup.

We can close this.

Very thanks for your support & guidance on this G

Mehul Trivedi trivedimehulk@gmail.com +91.999.875.1555

On Tue, Jan 2, 2018 at 12:35 PM, Gireesh Punathil notifications@github.com wrote:

@trivedimehulk https://github.com/trivedimehulk - so, close?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/nodejs/help/issues/967#issuecomment-354711863, or mute the thread https://github.com/notifications/unsubscribe-auth/AFDKdTzJV1EzmoQQcAmR9TyxAJSb7hUnks5tGdUggaJpZM4QbvJ0 .

[spimou]

Hello there. I am having a similar issue. I use node-mssql with tedious driver, I get a lot of data from a query and I want to know if mssql is still blocking or not. Are there any news?

In my case I use websockets to bring back data.

mssql 6.0.1 appears to be blocking in my case. When I use it to stream data from the server, as it steams, if I do another simple GET request via Postman, the GET cannot complete, until streaming is done.

Also, when I first execute a query to stream, is very slow the first time I do it and then it goes fast as it should. It gets faster if the chunks of data I process are fewer, eg 20 at a time

What should I do? Will pipe and stream help in my case? Or should I change node module for sql ? Is there any other node module to connect and stream from sql to node?

Here is my code , for anyone interested

const sql = require('mssql');
const pool = new sql.ConnectionPool(config);
const poolConnect = pool.connect();

const testStream = (ws , id) => {       
    poolConnect.then((pool) => {   
      const request = new sql.Request(pool);
      request.stream = true;   
      request
      .input('id_param', sql.Int, parseInt(id))
      .query('SELECT * FROM table WHERE id = @id_param ')  

      let rowsToProcess = [];
      let data = [];  

      request.on('row', row => {   
        rowsToProcess.push(row); 
        if (rowsToProcess.length >= 20) {  
          request.pause();
          processRows(false);
        } 
      });

      request.on('done', () => {      
        processRows(true); 
        sql.close(); 
      });

      const processRows = () => { 
          rowsToProcess.forEach((item)=>{ 
                data.push(item.name);   
                data.push(item.surname);   
                data.push(item.age);   
            });    
          ws.send(JSON.stringify({ success:true, message: data }));
          rowsToProcess = [];
          data = []; 
      }//processRows 

    }) //poolConnect.then

Thanks