CODEWITHSUNDEEP
c#sql-serverasp.net-coreado.net
Theodorus Agum Gumilang
Theodorus Agum Gumilang

Reputation: 1486

ADO.net Server Timeout when bulk Insert Happen, how to Improve it?

so I have Application for Inserting data using parametrized Query in ADO Net, but I notice there will be a Server Timeout Error. When there are so many Insert happen. The Row Inserted is almost 1500 Row. For every request. And in Busy hour there can be 10 - 30 Request at the same time. Is adding the timeout will solve the issue. In the normal situation, the request takes only 7 - 15 seconds to complete. But in my logs, I will see the Exception Error sometimes in a busy hour. Is adding the Timeout will solve the Issue or any tuning can I do to improve the performance

foreach (var item in collection) {
{
  using (var con = new SqlConnection("connectionString here"))
 {
   con.Open();
   var sql = "INSERT INTO  my_table (a, b, c) VALUES (@a,@b,@c);"

   using (var comm = new SqlCommand(sql, con))
   {
       comm.Parameters.Add("@a", SqlDbType.Int);
       comm.Parameters.Add("@b", SqlDbType.NVarChar);
       comm.Parameters.Add("@c", SqlDbType.Int);
      
       comm.Parameters["@a"].Value = aVal;
       comm.Parameters["@b"].Value = bVal;
       comm.Parameters["@b"].Size = bVal.Length;
       comm.Parameters["@c"].Value = cVal;
       comm.ExecuteNonQuery();
       con.Close();
      
   }
 

  }
}

Upvotes: 0

Views: 659

Answers (1)

Brando Zhang
Brando Zhang

Reputation: 28007

According to your description, I suggest you could consider using below ways to improve the performance.

(1)I suggest you could try to use ADO.net SqlBulkCopy to insert the record more quickly.

The general steps for performing a bulk copy operation are as follows:

1.Connect to the source server and obtain the data to be copied. Data can also come from other sources, if it can be retrieved from an IDataReader or DataTable object.

2.Connect to the destination server (unless you want SqlBulkCopy to establish a connection for you).

3.Create a SqlBulkCopy object, setting any necessary properties.

4.Set the DestinationTableName property to indicate the target table for the bulk insert operation.

5.Call one of the WriteToServer methods.

6.Optionally, update properties and call WriteToServer again as necessary.

7.Call Close, or wrap the bulk copy operations within a Using statement.

Example:

//I directly copy a table's schema, you could directly create a new table with the same sql server table column schema.
DataTable dt = s_EmptyUploadTable.Copy();
foreach (var itm in yourList) {
    DataRow row = dt.NewRow();
    row["Field1"] = itm.Field1;
    row["Field2"] = itm.Field2;
    dt.Rows.Add(row);
}
using (SqlConnection cn = new SqlConnection(yourConnectionString)) {
    cn.Open();
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(cn)) {
        bulkCopy.DestinationTableName = "dbo.YourActualSQLServerTableName";
        bulkCopy.WriteToServer(dt);
    }
    cn.Close();
}

(2) If you use bulkcopy still couldn't solve your issue, I suggest you could try to store the data into a cache like redis firstly. Then you could write a asp.net core background service to read the data from redis and insert the data into the database.

More details about how to use redis , you could refer to this article. More details about how to use asp.net background service, you could refer to this article.

Upvotes: 1

Related Questions