javascript - Node.js mssql module is very slow - Stack Overflow

admin管理员组

文章数量:1417555

I am trying to improve the performance of a Node.js REST API that uses SQL Server as its database. This API can be very slow (e.g. 1.5s response time).

After digging into the code, I have found that the JavaScript code itself is quite fast (runs for less than 1ms) but what is slowing everything down is the sql queries executed via the mssql module.

Even queries as simple as "SELECT [id] from [users]" take around 1 to 2 seconds to plete when the 'users' table only contains 5 rows! This issue seems to be happening within the mssql module because when I run the same queries on SSMS or via python (pymssql), they plete almost instantly as they should.

Does anyone know of a solution for this or an alternative module that could be used? (any other suggestions are wele)

This is the code I use to make the queries:

const config = {...}

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

sql.connect(config)
.then(pool => {
    date = Date.now();
    return pool.request()
    .query('select [id] from [users]');
})
.then(result => {
    console.dir('This query took ' + (Date.now() - date) + 'ms to return ' + result.recordset.length + ' rows!');
})
.catch(err => {
   console.log(err);
})

sql.on('error', err => {
    console.log(err);
})

and the output is:

'This query took 1287ms to return 5 rows!'

I am trying to improve the performance of a Node.js REST API that uses SQL Server as its database. This API can be very slow (e.g. 1.5s response time).

After digging into the code, I have found that the JavaScript code itself is quite fast (runs for less than 1ms) but what is slowing everything down is the sql queries executed via the mssql module.

Even queries as simple as "SELECT [id] from [users]" take around 1 to 2 seconds to plete when the 'users' table only contains 5 rows! This issue seems to be happening within the mssql module because when I run the same queries on SSMS or via python (pymssql), they plete almost instantly as they should.

Does anyone know of a solution for this or an alternative module that could be used? (any other suggestions are wele)

This is the code I use to make the queries:

const config = {...}

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

sql.connect(config)
.then(pool => {
    date = Date.now();
    return pool.request()
    .query('select [id] from [users]');
})
.then(result => {
    console.dir('This query took ' + (Date.now() - date) + 'ms to return ' + result.recordset.length + ' rows!');
})
.catch(err => {
   console.log(err);
})

sql.on('error', err => {
    console.log(err);
})

and the output is:

'This query took 1287ms to return 5 rows!'

• javascript
• sql-server
• node.js

Share Improve this question Follow edited Dec 13, 2017 at 0:34 Pierre Berton asked Dec 13, 2017 at 0:00 Pierre BertonPierre Berton 17022 silver badges1212 bronze badges

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 5

Found the solution! The config object can have a pool.min property that specifies the minimum number of connections that must remain active.

I did not know about this property and had thought that the module kept the connection active by default, now I added pool.min = 5 and everything works much better, problem solved!

Hopefully this can help some people

本文标签： javascriptNodejs mssql module is very slowStack Overflow