Query execution takes more than 30 seconds using Tedious while same query using Microsoft JDBC Driver for SQL Server on DBeaver takes only 200 ms

thomas-s97 commented 2 weeks ago

Hey everyone,

I'm facing an issue with executing an SQL query. When using this npm package using the standard Tedious driver, the execution time is very long (> 30 seconds). Executing the exact same query on DBeaver using Microsoft JDBC Driver for SQL Server, the query is executed within 200 ms.

Expected behaviour:

This is the exact SQL query I'm executing

SELECT DISTINCT 
    A.ITEMID, 
    A.NAME1 AS 'PRODUCTNAME1', 
    A.STOCKNR, 
    A.COLOR, 
    A.SIZE, 
    t.TransactionID 
FROM 
    ITEM A 
JOIN 
    INVOICEPOSITION IP 
ON 
    IP.ITEMID = A.ITEMID 
JOIN 
    TransactionOverview t 
ON 
    t.INVOICEID = IP.INVOICEID 
WHERE 
    IP.INVOICEID IN ( 
        SELECT 
            INVOICEID 
        FROM 
            TransactionOverview 
        WHERE 
            t.TransactionID IN ( 
                1006102156, 
                1007111955, 
                1008779474, 
                1009303870, 
                1009568893, 
                1009583252, 
                1009714594, 
                1009740813, 
                1009745616, 
                1009797400, 
                1010110763, 
                1010119983, 
                1010210870, 
                1010238107, 
                1010282490, 
                1011175660, 
                1011196118, 
                1011258805, 
                1011268784, 
                1011309250, 
                1011698359, 
                1011765836, 
                1012741114, 
                1012761478, 
                1012761798, 
                1012866040, 
                1013298715, 
                1013578262, 
                1013637171, 
                1014413088, 
                1014705505, 
                1015114052, 
                1015197639
            )
    )

Expected behavior is that the execution time is less than a second.

Actual behaviour:

Execution time takes > 30 seconds. Sometimes even 2 minutes.

Configuration:

I already looked thorough a couple of issues. I identified this issue here where the solution was to directly specify the DB in the config instead of the SQL query. https://github.com/tediousjs/node-mssql/issues/1056

However, in my case this is how my config looks like:

 connection: {
      server: process.env.MSSQL_DB_HOST,
      port: +process.env.MSSQL_DB_PORT,
      database: process.env.MSSQL_DB_NAME,
      user: process.env.MSSQL_DB_USER,
      password: process.env.MSSQL_DB_PASS,
      requestTimeout: 600000,
      pool: {
        max: 2,
        min: 1,
        idleTimeoutMillis: 30000,
      },
      options: {
        encrypt: false,
        trustServerCertificate: false,
      },
    },

And this is how I'm executing the query

pool.query(queryToExecute)

Interestingly, all other SQL queries in my application execute fast with comparable speeds to DBeaver.

Other observations

I was trying to change the number of IDs in this WHERE clause.

WHERE 
            t.TransactionID IN ( 
                1006102156, 
                1007111955, 
                1008779474, 
                1009303870, 
                1009568893, 
                1009583252, 
                1009714594, 
                1009740813, 
                1009745616, 
                1009797400, 
                1010110763, 
                1010119983, 
                1010210870, 
                1010238107, 
                1010282490, 
                1011175660, 
                1011196118, 
                1011258805, 
                1011268784, 
                1011309250, 
                1011698359, 
                1011765836, 
                1012741114, 
                1012761478, 
                1012761798, 
                1012866040, 
                1013298715, 
                1013578262, 
                1013637171, 
                1014413088, 
                1014705505, 
                1015114052, 
                1015197639
            )

When reducing this number to half of the numbers in the statement, the execution time is 8 seconds on node-mssql. When reducing to only 5 IDs in the WHERE clause, execution time in node-mssql is 900 ms.

On DBeaver the execution time does not differ much when reducing the number of IDs in the clause. It always stays < 300ms.

Software versions

NodeJS: v20.8.0
node-mssql: 11 (but also tried 10.1.0)

My current assumption is that the tedious driver seems to have performance issues due to WHERE clause with a larger number of IDs. Is there any way to address this issue?

dhensby commented 2 weeks ago

I'm afraid this isn't something that I can provide much help with because it relies too much on your particular database and systems.

Can you reproduce this in a vanilla database schema you could share? Then it would be easier to look into and a bit more probable that this is an inherent problem with the library and not the database itself.

Also, I usually recommend that people try to reproduce the problem with the vanilla tedious driver, as if the problem persists with just the driver, then the cause is not this library.

thomas-s97 commented 2 weeks ago

Thanks for your quick reply. Let me try to re-repro on a vanilla database schema and a vanilla tedious driver. I'll share my findings here shortly.

thomas-s97 commented 1 week ago

I found a solution that works for me. I re-wrote the query to not use sub-queries which made the performance a lot better from >30 sec query time to less than 1 second.

SELECT DISTINCT
    A.ITEMID, 
    A.NAME1 AS 'PRODUCTNAME1', 
    A.STOCKNR, 
    A.COLOR, 
    A.SIZE, 
    t.TransactionID 
FROM
    ITEM A
JOIN
    INVOICEPOSITION IP
ON
    IP.ITEMID = A.ITEMID
JOIN
    TransactionOverview t
ON
    t.INVOICEID = IP.INVOICEID
WHERE
    t.TransactionID IN ( 
        1006102156, 
        1007111955, 
        1008779474, 
        1009303870, 
        1009568893, 
        1009583252, 
        1009714594, 
        1009740813, 
        1009745616, 
        1009797400, 
        1010110763, 
        1010119983, 
        1010210870, 
        1010238107, 
        1010282490, 
        1011175660, 
        1011196118, 
        1011258805, 
        1011268784, 
        1011309250, 
        1011698359, 
        1011765836, 
        1012741114, 
        1012761478, 
        1012761798, 
        1012866040, 
        1013298715, 
        1013578262, 
        1013637171, 
        1014413088, 
        1014705505, 
        1015114052, 
        1015197639
    )

tediousjs / node-mssql