tedious: Slow running query using tedious
I’m using https://github.com/tediousjs/node-mssql/
When using the default tedious driver there is one particular query that takes 25seconds to run through node-mssql whereas if I run it directly on SSMS it takes 200ms
When switching the driver in node-mssql from tedious to msnodesqlv8 it seems to solve the issue
Behavior:
Would expect the run time to be comparable
Running the queries and monitoring on SQL server profiler:
node/mssql (tedious driver)

SSMS

Not sure why but the query consistently takes 25seconds through node/mssql
Configuration:
Connection Configuration
const config = {
user: DB_USER,
password: DB_PASS,
server: DB_HOST,
requestTimeout: 300000,
pool: {
min: 5
}
}
How I connect to run the query:
const dbc = new sql.connect(config);
const ClientDb = 'Client.dbo';
const StockDb = 'Stock.dbo';
export {
dbc,
sql,
ClientDb,
StockDb
}
import { dbc, sql, StockDb } from '../../config/db'
Then the actual query:
return dbc.then(pool => {
return pool.request()
.input('clientId', sql.Int, clientId)
.input('startDate', sql.DateTime, new Date(startDate))
.input('endDate', sql.DateTime, new Date(endDate))
.query(`SELECT v.Id as VehicleId,
n.Name as Make,
m.Name as Model,
v.Variant as Variant,
v.Registration as Registration,
v.Price As Price,
v.OfferPrice as OfferPrice,
v.OfferDisplay as OfferDisplay,
d.Name as DealershipName,
v.StockDate as StockDate,
v.PurchaseStandIn as SIV,
s.Name as PurchaseSource,
DATEDIFF(day, StockDate, GETDATE()) as DaysInStock,
v.SoldDate as SoldDate,
v.SoldPrice as SoldPrice,
s1.Name as SoldSource
FROM ${StockDb}.UsedVehicles v
JOIN ${StockDb}.Models m on m.Id = v.ModelId
JOIN ${StockDb}.Makes n ON n.Id = m.MakeId
JOIN ${StockDb}.Dealerships d on d.Id = v.DealershipId
LEFT JOIN ${StockDb}.UsedPriceSources s on s.Id = v.PurchasePriceSourceId
LEFT JOIN ${StockDb}.UsedPriceSources s1 on s1.Id = v.SoldPriceSourceId
WHERE v.Status = 3
AND v.ClientId = @clientId
AND v.SoldDate between @startDate AND @endDate
ORDER BY SoldDate ASC`)
}).then(result => {
return result.recordset;
}).catch(err => {
console.error(err);
});
Software versions
- NodeJS: 10
- node-mssql: 6.2, also tried the newer 7.0 which uses Tedious 8.3
- SQL Server: 2017
About this issue
- Original URL
- State: closed
- Created 6 years ago
- Comments: 15
Leaving this here in the hope it saves someone a bit of time:
Not sure if it is the same issue, but I encountered something similar today: a query is very slow from typeorm (which uses node-mssql and tedious under the hood, using the other driver did not make a difference), but very quick when copy-pasted and run from SSMS (replacing the parameter values with their literal values first)
Turns out, the reason appears to be that text parameters (
SomeColumn = @0) seem to be inserted in the query asnvarchartyped strings. In my case theSomeColumnisvarcharand due to the mismatch, indexes are not used anymore. I fixed this for now by doing(SomeColumn = CAST(@0 AS VARCHAR(4000))).I am not sure where the type of parameters is defined, it might be worthwhile to fix this somwhere.