Re-use prepared statements node-js mssql in a loop

Question

I am working on a TS/NodeJS/MSSQL project and I basically want to iterate an array of something, let's say products, run a select query to determine if said product is in the database and if not insert it.

The purpose of this exercise is to use prepared statements for both the select, insert and be able to reuse them in the loop iterating the products.

So let's say we have array of this:

export interface IProduct{
 name:string;
 price:number;
}

then we create the connection:

const mssql = require(mssql);
let connection = new mssql.ConnectionPool({
 server:[server address],
 database:[database name],
 user:[username],
 password:[password]
});

then the prepared queries

let insertQuery = new msssql.PreparedStatement(connection);
insertQuery.input("name",mssql.TYPES.NVarChar);
insertQuery.input("price",mssql.TYPES.Float);

then let's iterate the products array:

async iterateProducts(products:Array<IProduct>){
//first prepare the query if it's not prepared
if(!insertQuery.prepared)
  await insertQuery.prepare("INSERT INTO Products (name,price) values (@name,@price)");

products.forEach(async (p)=>{
    let insertResult = await insertQuery.execute(p);
    //do anything after insert
  });
}
//then unprepare query
await insertQuery.unprepare();

The error I keep getting is

Can't acquire connection for the request. There is another request in progress

I am really new to this stuff, so there is a big chance I am totally missing something obvious or misusing it somehow.

Note: Code is pseudo-code, just describing the problem. Don't take it as production working code. So if there is a dot or comma or anything missing or wrong, it's probably ok.

Any help would be greatly appreciated. Thanks!

score 0 · Answer 1 · answered Dec 19 '19 at 15:29

0

I think this complain due to MARS, however I'd strongly suggest against doing this kind of operations, especially in loops. If possible - try to prepare a single batch and deal with this logic using SQL and in a single transaction. This will be way faster and should ensure consistent database state.

answered Dec 19 '19 at 15:29

Evaldas Buinauskas

13,739
11
55
107

I am not sure I understand your proposal. What do you mean _deal with this logic using SQL_ ? I have performance in mind, that's why I am trying to use prepared queries. – Yiorgos Dec 19 '19 at 15:42
Sorry I was not clear enough. You're using prepared queries, that is great. However if you look closely, for each iteration you have two sequential database round trips, one to check existence the other one to inset the data. Multiple this by 1000 and you'll have 2000 database requests. This will definitely be slow. What I'd like to recommend is trying to batch your requests. Either create a temporary table or table variable, insert all of the products you want to operate with into it and then do the comparison in SQL. This means you have just a single database round trip to insert and compare – Evaldas Buinauskas Dec 19 '19 at 17:42
that's more clear yes. Unfortunately doesn't address the issue I am facing, specifically with node-mssql. I mean even if there are more performant ways to achieve the same, I still trying to figure how to re-use prepared queries with node-mssql – Yiorgos Dec 19 '19 at 18:35
to help the discussion, I updated the code and removed the select/find part. node-mssql behaves the same, even if it's only inserts. – Yiorgos Dec 19 '19 at 19:09

Re-use prepared statements node-js mssql in a loop

1 Answers1