SQL Server Bulk insert throws request timeout error?

Question

The following code bulk inserts data into a SQL Server database:

sqlConnection.connectionPool.getConnection().then(pool => {
    const request  = pool.request();
        request.bulk(table, (err, result) => {
            if(err){
                console.log('bulk insert error');
                console.log(err);
                reject(err);
                return;
            }


                EXEC dbo.AddSurveyPoints @SurveyPointTable = @Table

                DROP TABLE `+tableName+`;`;
                  request.query(exeProcedure, function(err, recordset){
                      if(err){
                          console.log('Error addSurveyPoints :' + err);
                          reject(err);
                          return;
                      }
                      fulfill(result);
                  });
              });
        })

The code runs with no error.

But after loading testing with jmeter, it throws this error :

ResourceRequest timed out

What do you mean when you say "The code runs with no error"? Is it on the JMeter GUI or the IDE? Please do elaborate on "after loading testing with jmeter". Does it mean you have added some load or is it just for a single user? — M Navneet Krishna, Jan 24 '20 at 04:03
@MNavneetKrishna when i test with limited request in jmeter let say 100 Threads (users) it work fine but when change number of thread to 500 it start throwing Resource request timeout — Aimal Khan, Jan 24 '20 at 06:00
I feel it is an issue with the capacity and not the tool. What are the max connections size in DB that has been configured? and what is the connection timeout defined? Also, do you notice the ResourceRequest timed out error after a while or even while the users ramp up? — M Navneet Krishna, Jan 28 '20 at 06:08
Do you really need to do this on sync way? Why do you not return some response and execute the bulk on async mode? — rpereira15, Feb 01 '20 at 18:09

score 0 · Answer 1 · answered Jan 31 '20 at 10:53

Try with below settings:

in your app.js or where you are making connection.

const pool = new sql.ConnectionPool({
    user,
    password,
    server,
    database, 
    pool: { 
        max: 100000, 
        min: 1, 
        idleTimeoutMillis: 50, 
        evictionRunIntervalMillis: 5, 
        softIdleTimeoutMillis: 5
    }
});

I hope this will work smoothly with Jmeter too.

SQL Server Bulk insert throws request timeout error?

1 Answers1