Use a DirtyFlag to intelligently update datagrid rows
A fellow developer from Kodak asked if I could look into optimizing a
slow running query in their sales forecast applications. The application was written
in C#, ASP.NET, and SQL Server along with several thousand stored procedures.
Performance problems often occur in specific sections of bottleneck code.
There are several techniques to locate this slow running code,
but from a short examination of this application, I noticed that the database update was occurring inside a loop
that updated each row regardless of whether data had changed or not.
This meant that a 50 row datagrid would produce 50 SQL Updates. Since only a couple
rows where normally updated, this was highly inefficient. A timing confirmed that this was indeed the problem section.
public void Update_Changes(object sender, EventArgs e)
{
foreach(DataGridItem dgi in grd12Months.Items)
{
String strSQL = "Update CPI_MnthlyBrkdn_12M_A1 Set ";
strSQL += " Qty_01= " + Convert.ToDouble(t1.Text);
strSQL += ", Qty_02="+ Convert.ToDouble(t2.Text);
...
cmd.ExecuteNonQuery();
}
}
|
Solution
The solution I implemented was to add a DirtyFlag to the row to track when a
user changed the data, then inside the update loop, update only those rows that
had a dirtyflag != false.
The first step is to add an HTML column the datagrid to track when the data row
becomes dirty. In this case, I used a Template Column due to the highly
specific formatting Kodak requires:
<asp:TemplateColumn Visible="True">
<HeaderStyle Width="0px">
<ItemStyle HorizontalAlign="Left">
<HeaderTemplate>
DirtyFlag
</HeaderTemplate>
<ItemTemplate>
<asp:textbox id="txtDirtyFlag" runat="server" Visible=true
CssClass="MultipleDataEditBox" Width="0px"
Text='%lt;# DataBinder.Eval(Container.DataItem, "DirtyFlag", "{0:N0}") %>'>
</asp:textbox>
</ItemTemplate>
</asp:TemplateColumn>
|
Setting the dirtyflag was a slightly more difficult problem than I originally anticipated. The
problem is that setting the dirtyflag involves invoking javascript on the onKeyPress event, but adding
javascript to a dynamically created ASP.NET control must be done via C# inside the code-behind.
After a quick google search, I found that I could add custom
javascript thru C# via the Attributes property:
private void grd12Months_OnItemDataBound (object sender, DataGridItemEventArgs e)
{
if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
{
e.Item.Cells[6].Attributes.Add ("onkeypress", " document.getElementById('grd12Months__ctl"
+dg_row_counter.ToString()+"_txtDirtyFlag').value='6'; ");
e.Item.Cells[7].Attributes.Add ("onkeypress", " document.getElementById('grd12Months__ctl"
+dg_row_counter.ToString()+"_txtDirtyFlag').value='7'; ");
e.Item.Cells[8].Attributes.Add ("onkeypress", " document.getElementById('grd12Months__ctl"
+dg_row_counter.ToString()+"_txtDirtyFlag').value='8'; ");
e.Item.Cells[9].Attributes.Add ("onkeypress", " document.getElementById('grd12Months__ctl"
+dg_row_counter.ToString()+"_txtDirtyFlag').value='9'; ");
e.Item.Cells[10].Attributes.Add ("onkeypress", " document.getElementById('grd12Months__ctl"
+dg_row_counter.ToString()+"_txtDirtyFlag').value='10'; ");
e.Item.Cells[11].Attributes.Add ("onkeypress", " document.getElementById('grd12Months__ctl"
+dg_row_counter.ToString()+"_txtDirtyFlag').value='11'; ");
e.Item.Cells[12].Attributes.Add ("onkeypress", " document.getElementById('grd12Months__ctl"
+dg_row_counter.ToString()+"_txtDirtyFlag').value='12'; ");
e.Item.Cells[13].Attributes.Add ("onkeypress", " document.getElementById('grd12Months__ctl"
+dg_row_counter.ToString()+"_txtDirtyFlag').value='13'; ");
e.Item.Cells[14].Attributes.Add ("onkeypress", " document.getElementById('grd12Months__ctl"
+dg_row_counter.ToString()+"_txtDirtyFlag').value='14'; ");
e.Item.Cells[15].Attributes.Add ("onkeypress", " document.getElementById('grd12Months__ctl"
+dg_row_counter.ToString()+"_txtDirtyFlag').value='15'; ");
e.Item.Cells[16].Attributes.Add ("onkeypress", " document.getElementById('grd12Months__ctl"
+dg_row_counter.ToString()+"_txtDirtyFlag').value='16'; ");
e.Item.Cells[17].Attributes.Add ("onkeypress", " document.getElementById('grd12Months__ctl"
+dg_row_counter.ToString()+"_txtDirtyFlag').value='17'; ");
}
}
|
Finally, add the test for DirtyFlag to update only changed rows:
public void Update_Changes(object sender, EventArgs e)
{
foreach(DataGridItem dgi in grd12Months.Items)
{
TextBox DirtyFlag = (TextBox) dgi.FindControl("txtDirtyFlag");
if (DirtyFlag.Text != "false")
{
String strSQL = "Update CPI_MnthlyBrkdn_12M_A1 Set ";
strSQL += " Qty_01= " + Convert.ToDouble(t1.Text);
strSQL += ", Qty_02="+ Convert.ToDouble(t2.Text);
...
cmd.ExecuteNonQuery();
}
}
}
|
And hide the DirtyFlag HTML column
A final performance test confirmed that updates were occurring nearly 100 times faster - problem solved!
|