0

At the moment I am deploying a node.js application with IIS reverse proxy at a test server. I am working with mssql package to connect to the SQL Server 2014. For testing on my local machine everything worked fine (SQL Server 2019), but now on the test server I mentioned a really strange behaviour of UPDATE and INSERT statements on the server.

Example statement:

UPDATE name 
FROM renters 
WHERE id = 1

Very trivial statements like this, in rather small tables (< 400 rows, < 200 columns) sometimes take 1-2 minutes to execute, while SELECT statements execute immediately without delay.

Up to now I could not identify a reasonable problem that causes this behaviour. I was thinking about maybe it is about the SQL Server version or about some restrictions only on UPDATE and INSERT statements by the Database manager, but in my eyes that's also not really realistic, or maybe due to a strange behaviour of the pooling connections? Am I missing something?

BTW I am using the basic connnector, proposed by mssql with pool connections like this:

const sql = require('mssql')
const sqlConfig = {
  user: process.env.DB_USER,
  password: process.env.DB_PWD,
  database: process.env.DB_NAME,
  server: 'localhost',
  pool: {
    max: 10,
    min: 0,
    idleTimeoutMillis: 30000
  },
  options: {
    encrypt: true, // for azure
    trustServerCertificate: false // change to true for local dev / self-signed certs
  }
}

async () => {
 try {
  // make sure that any items are correctly URL encoded in the connection string
  await sql.connect(sqlConfig)
  const result = await sql.query`select * from mytable where id = ${value}`
  console.dir(result)
 } catch (err) {
  // ... error checks
 }
}
lukinoe
  • 21
  • 4
  • That looks like it's an injection security concern. – Thom A Dec 08 '21 at 09:08
  • @Larnu ... but I don't get why it is not blocked then, but just delayed. – lukinoe Dec 08 '21 at 09:10
  • Are there any update trigger(s) on the table? Poorly implemented triggers, especially ones that access external dependencies like web services, are often a source of performance issues. Triggers should be as simple and efficient as possible. – AlwaysLearning Dec 09 '21 at 00:22
  • @AlwaysLearning Correction to the upper description: I could observe the same behaviour also for INSERT statements. As far as I know there are no triggers on the table. I have to add that the behaviour is true for all tables of the database, not just one. – lukinoe Dec 09 '21 at 08:32
  • What happens when you execute that same statement from SSMS, instead of through Node? – Hans Kesting Dec 09 '21 at 10:39

0 Answers0