Lately I had to write a windows service which has more number of insert/undate statements. During this process we faced performance challanges. To over come this we have taken ASP.Net batch update technique. It worked really well for us. In this approach, we can execute a chunk of DML (Insert, Update, Delete) statements at once. I set batch size to 10,000 update statements. It worked flawlessly. To achvie this I have used ADO.Net in data layer. SqlDataAdapter class plays major role in ths process.
Enough of theory. Let us get into sample on how to do this.
Example: Update employee Grade of all employees in an organization. In this example we will update 10,0000 employees grades
Step #1: Create datatable which holds all employees.
DataTable dt = new DataTable("Employees");
DataColumn dcEmployeeId = new DataColumn("EmployeeId");
DataColumn dcGradeId = new DataColumn("Grade");
dt.Columns.Add(dcEmployeeId);
dt.Columns.Add(dcGradeId);
// employee is a collection of employees. Assume that it has 10,000 employees in it.
foreach (var emp in employees)
{
var row = dt.NewRow();
row["EmployeeId"] = emp.EmployeeId;
row["Grade"] = emp.Grade;
dt.Rows.Add(row);
// Since we are planning to update this data, below three statements
row.AcceptChanges();
row.BeginEdit();
row.EndEdit();
}
Step #2: Execute batch from Data Access Layer:
SqlCommand sqlCommand = null;
SqlDataAdapter adapter = null;
SqlConnection testConnection = null;
using (testConnection = new SqlConnection(<< Connection String >>))
{
adapter = new SqlDataAdapter();
var updateSQL = string.Format(@"UPDATE emp SET emp.Grade = @Grade
FROM Employees emp
WHERE emp.EmployeeId = @EmployeeId");
sqlCommand = new SqlCommand(updateSQL, testConnection);
adapter.UpdateCommand = sqlCommand;
adapter.UpdateCommand.Parameters.Add("@EmployeeId", SqlDbType.Decimal, 4, "EmployeeId");
adapter.UpdateCommand.Parameters.Add("@Grade", SqlDbType.Int, 4, "Grade");
adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
adapter.UpdateBatchSize = 0;
// Execute the update. Below database is the table created in step #1
adapter.Update(dt);
}
Enough of theory. Let us get into sample on how to do this.
Example: Update employee Grade of all employees in an organization. In this example we will update 10,0000 employees grades
Step #1: Create datatable which holds all employees.
DataTable dt = new DataTable("Employees");
DataColumn dcEmployeeId = new DataColumn("EmployeeId");
DataColumn dcGradeId = new DataColumn("Grade");
dt.Columns.Add(dcEmployeeId);
dt.Columns.Add(dcGradeId);
// employee is a collection of employees. Assume that it has 10,000 employees in it.
foreach (var emp in employees)
{
var row = dt.NewRow();
row["EmployeeId"] = emp.EmployeeId;
row["Grade"] = emp.Grade;
dt.Rows.Add(row);
// Since we are planning to update this data, below three statements
row.AcceptChanges();
row.BeginEdit();
row.EndEdit();
}
Step #2: Execute batch from Data Access Layer:
SqlCommand sqlCommand = null;
SqlDataAdapter adapter = null;
SqlConnection testConnection = null;
using (testConnection = new SqlConnection(<< Connection String >>))
{
adapter = new SqlDataAdapter();
var updateSQL = string.Format(@"UPDATE emp SET emp.Grade = @Grade
FROM Employees emp
WHERE emp.EmployeeId = @EmployeeId");
sqlCommand = new SqlCommand(updateSQL, testConnection);
adapter.UpdateCommand = sqlCommand;
adapter.UpdateCommand.Parameters.Add("@EmployeeId", SqlDbType.Decimal, 4, "EmployeeId");
adapter.UpdateCommand.Parameters.Add("@Grade", SqlDbType.Int, 4, "Grade");
adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
adapter.UpdateBatchSize = 0;
// Execute the update. Below database is the table created in step #1
adapter.Update(dt);
}
