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)

image

SSMS

image

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

Most upvoted comments

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 as nvarchar typed strings. In my case the SomeColumn is varchar and 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.