I am inserting data in sql server 2008 R2 from web api. There are about 300 records that are inserted in 3 to 4 tables. First 20 to 30 records are easily inserted in few millisecond after that it will take few seconds to few minutes to insert records. The sql server is installed in Windows Server 2012 R2.
If I delete the same record after it is inserted and then again call the api to insert same data, it will just take 2 to 3 seconds to insert.
This is the api code
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using ProductsApp.ApplicationLogics;
using System.Web.Http.Cors;
using Newtonsoft.Json;
namespace ProductsApp.Controllers
{
[EnableCors(origins: "*", headers: "*", methods: "POST")]
public class DataPostController : ApiController
{
[AcceptVerbs("POST")]
public string DataPost([FromBody] List<Models.ReadingData> model)
{
if (model == null)
{
return "Data not found!";
}
string sql, id1;
string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["APIConnectionString"].ConnectionString;
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
using (SqlTransaction tran = con.BeginTransaction())
{
foreach (var item in model)
{
sql = @"INSERT INTO TableA(Col1, Col2, Col3, Col4)
SELECT @Col1, @Col2, @Col3, @Col4;
SELECT SCOPE_IDENTITY();";
using (SqlCommand sqlCommand = new SqlCommand(sql, con, tran))
{
sqlCommand.Parameters.AddWithValue("@Col1", item.value1);
sqlCommand.Parameters.AddWithValue("@Col2", item.value2);
sqlCommand.Parameters.AddWithValue("@Col3", item.value3);
try
{
id1 = Convert.ToString(sqlCommand.ExecuteScalar());
if (item.amount > 0)
{
if (item.something != "0")
{
sql = @"INSERT INTO TableB(Col1, Col2, Col3)
SELECT @Col1, @Col2, @Col3;";
using (SqlCommand sqlCommand2 = new SqlCommand(sql, con, tran))
{
sqlCommand2.Parameters.AddWithValue("@Col1", id1);
sqlCommand2.Parameters.AddWithValue("@Col2", item.value5);
sqlCommand2.Parameters.AddWithValue("@FiscalYearId", item.value6);
try
{
sqlCommand2.ExecuteNonQuery();
}
catch (SqlException ex)
{
tran.Rollback();
return ex.Message;
}
}
}
}
if (item.advanceAmount > 0 || item.outstandingAmount > 0)
{
sql = @"UPDATE CustomersInfo SET AdvanceAmount=0, OutstandingAmount=0 WHERE CustomerId=@CustomerId;
UPDATE COAR SET IsClear=1 WHERE CustomerId=@CustomerId;
INSERT INTO COAR(FiscalYearId, CustomerId, EntryByUserId, OutstandingAmount, AdvanceAmount)
SELECT @FiscalYearId, @CustomerId, @EntryByUserId, @OutstandingAmount, @AdvanceAmount;";
}
else
{
sql = @"UPDATE CustomersInfo SET AdvanceAmount=0 WHERE CustomerId=@CustomerId;
UPDATE COAR SET IsClear=1 WHERE CustomerId=@CustomerId;";
}
using (SqlCommand sqlCommand2 = new SqlCommand(sql, con, tran))
{
sqlCommand2.Parameters.AddWithValue("@FiscalYearId", item.FiscalYearId);
sqlCommand2.Parameters.AddWithValue("@CustomerId", item.CustomerId);
sqlCommand2.Parameters.AddWithValue("@EntryByUserId", item.MeterReaderId);
sqlCommand2.Parameters.AddWithValue("@OutstandingAmount", item.outstandingAmount);
sqlCommand2.Parameters.AddWithValue("@AdvanceAmount", item.advanceAmount);
try
{
sqlCommand2.ExecuteNonQuery();
}
catch (SqlException ex)
{
tran.Rollback();
return ex.Message;
}
}
/*Insert Spot Fine (if any)*/
if (item.Fine > 0)
{
sql = @"INSERT INTO CreditSales(CreditSalesDateAD, CreditSalesDateBS, FiscalYearId,
CustomerId, ParticularsId, Amount, EntryByUserId, Status, MonthSN, MonthId)
SELECT @CreditSalesDateAD, @CreditSalesDateBS, @FiscalYearId,
@CustomerId, @ParticularsId, @Amount, @EntryByUserId, @Status, @MonthSN, @MonthId;";
using (SqlCommand sqlCommand4 = new SqlCommand(sql, con, tran))
{
sqlCommand4.Parameters.AddWithValue("CreditSalesDateAD", item.meterReadingDateAD);
sqlCommand4.Parameters.AddWithValue("CreditSalesDateBS", item.meterReadingDateBS);
sqlCommand4.Parameters.AddWithValue("FiscalYearId", item.FiscalYearId);
sqlCommand4.Parameters.AddWithValue("CustomerId", item.CustomerId);
sqlCommand4.Parameters.AddWithValue("ParticularsId", 5); //Always will be 5
sqlCommand4.Parameters.AddWithValue("Amount", item.Fine);
sqlCommand4.Parameters.AddWithValue("EntryByUserId", item.MeterReaderId);
sqlCommand4.Parameters.AddWithValue("Status", "0");
sqlCommand4.Parameters.AddWithValue("MonthSN", item.monthSN);
sqlCommand4.Parameters.AddWithValue("MonthId", item.MonthId);
try
{
sqlCommand4.ExecuteNonQuery();
}
catch (SqlException ex)
{
tran.Rollback();
return ex.Message;
}
}
}
/*If any tap repair complain*/
if (item.TapRepair == 1)
{
sql = @"INSERT INTO TapRepairs(ComplainDateAD, ComplainDateBS, FiscalYearId, CustomerId, ComplainTypeId,
RepairDateAD, RepairDateBS, RepairDescription, RepairByUserId)
SELECT @ComplainDateAD, @ComplainDateBS, @FiscalYearId, @CustomerId, @ComplainTypeId,
@RepairDateAD, @RepairDateBS, @RepairDescription, @RepairByUserId;";
using (SqlCommand sqlCommand5 = new SqlCommand(sql, con, tran))
{
sqlCommand5.Parameters.AddWithValue("@ComplainDateAD", item.meterReadingDateAD);
sqlCommand5.Parameters.AddWithValue("@ComplainDateBS", item.meterReadingDateBS);
sqlCommand5.Parameters.AddWithValue("@FiscalYearId", item.FiscalYearId);
sqlCommand5.Parameters.AddWithValue("@CustomerId", item.CustomerId);
sqlCommand5.Parameters.AddWithValue("@ComplainTypeId", item.Remarks);
sqlCommand5.Parameters.AddWithValue("@RepairDateAD", item.meterReadingDateAD);
sqlCommand5.Parameters.AddWithValue("@RepairDateBS", "");
sqlCommand5.Parameters.AddWithValue("@RepairDescription", "");
sqlCommand5.Parameters.AddWithValue("@RepairByUserId", item.MeterReaderId);
try
{
sqlCommand5.ExecuteNonQuery();
}
catch (SqlException ex)
{
tran.Rollback();
return ex.Message;
}
}
}
}
catch (SqlException ex)
{
if (ex.Message.Contains("MeterReadingEntries_FYID_MID_CID"))
{
//If meter reading entry already done
continue;
}
else
{
tran.Rollback();
return ex.Message;
}
}
}
}
tran.Commit();
con.Close();
con.Dispose();
}
}
return "ok";
}
}
}
Each table contains max 10 columns only. What may be the cause? Is there anything wrong in the api? Why the insert is fast after the same record is deleted and re-inserted?
Update: Profiler Image Profiler Attached Image
