I have a C# windows application, sometimes the application would be used to import records from excel file, i could import 2500 records in one minute using batch insertion on my development machine, when i tried normal regular insertion approach, it also took the same one minute to insert 2500 records, this get worse after i have deployed the application and installed on a client's machine, it took more than two hours to import same number of record, i just felt since i am using bulk insertion, the speed should be greatly optimised, below is my code:
What I have tried:
public int InsertTaxRecordBatchTransaction(IEnumerable<EmployeeDetails> employeeDetails)
{
SQLiteConnection conn = null;
SQLiteCommand cmd = null;
var results = new List<int>();
try
{
string insert = "INSERT INTO IncomeTax (OrganisationId,OrganisationName,StaffId,StaffName,Department,Position,NoOfMonthsWorked,TaxIdNumber,SessionId,Month,Year,BasicSalaryVALUES (@OrganisationId,@OrganisationName,@StaffId,@StaffName,@Department,@Position,@NoOfMonthsWorked,@TaxIdNumber,@SessionId,@Month,@Year,@BasicSalary);";
using (conn = new SQLiteConnection(connstring))
{
conn.Open();
using (var transaction = conn.BeginTransaction())
{
try
{
using (cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = insert;
cmd.CommandTimeout = 900000000;
cmd.Parameters.Add("@OrganisationId", DbType.String, 50).Value = OrganisationId;
cmd.Parameters.Add("@OrganisationName", DbType.String, 50).Value = OrganisationName;
cmd.Parameters.Add("@StaffID", DbType.String, 50).Value = StaffId;
cmd.Parameters.Add("@StaffName", DbType.String, 50).Value = StaffName;
cmd.Parameters.Add("@Department", DbType.String, 50).Value = Department;
cmd.Parameters.Add("@Position", DbType.String, 50).Value = Position;
cmd.Parameters.Add("@NoOfMonthsWorked", DbType.Int16, 5).Value = NoOfMonthsWorked;
cmd.Parameters.Add("@TaxIdNumber", DbType.String, 5).Value = TaxIdNumber;
cmd.Parameters.Add("@SessionId", DbType.String, 20).Value = SessionId;
cmd.Parameters.Add("@Month", DbType.String, 20).Value = Month;
cmd.Parameters.Add("@Year", DbType.String, 20).Value = Year;
cmd.Parameters.Add("@BasicSalary", DbType.Decimal, 2).Value = BasicSalary;
foreach (var employee in employeeDetails)
{
cmd.Parameters.Add("@OrganisationId", DbType.String, 50).Value = employee.OrganisationId;
cmd.Parameters.Add("@OrganisationName", DbType.String, 50).Value = employee.OrganisationName;
cmd.Parameters.Add("@StaffID", DbType.String, 50).Value = employee.StaffId;
cmd.Parameters.Add("@StaffName", DbType.String, 50).Value = employee.StaffName;
cmd.Parameters.Add("@Department", DbType.String, 50).Value = employee.Department;
cmd.Parameters.Add("@Position", DbType.String, 50).Value = employee.Position;
cmd.Parameters.Add("@NoOfMonthsWorked", DbType.Int16, 5).Value = employee.NoOfMonthsWorked;
cmd.Parameters.Add("@TaxIdNumber", DbType.String, 5).Value = employee.TaxIdNumber;
cmd.Parameters.Add("@SessionId", DbType.String, 20).Value = employee.SessionId;
cmd.Parameters.Add("@Month", DbType.String, 20).Value = employee.Month;
cmd.Parameters.Add("@Year", DbType.String, 20).Value = employee.Year;
cmd.Parameters.Add("@BasicSalary", DbType.Decimal, 2).Value = employee.BasicSalary;
results.Add(cmd.ExecuteNonQuery());
}
}
transaction.Commit();
DspMsg = "Tax computed successfully!";
}
catch(Exception ex)
{
transaction.Rollback();
DspMsg = ex.Message+ " some errors have occured, no record was saved! ";
}
}
}
}
catch (SQLiteException ex)
{
DspMsg = ex.Message;
}
return results.Sum();
}