I have this code in my aspx page
and then this in my codebehind
If I remove the nested gridview it runs nice and fast, but with the nested gridview it is painfully and I do mean painfully slow. There is not a huge amount of data. The first gridview has maybe 130 rows and the each row has 0-20 rows in the nested gridview. But with the nested gridview in place it takes close to 5 minutes to run, so it feels like I must be doing something wrong. Running the queries individually takes nowhere near this amount of time. Any ideas how I can speed this up?
Thanks,
Willie
Code:
<asp:Content ID="Content2" ContentPlaceHolderID="MainContentPlaceHolder" Runat="Server">
<script type="text/javascript" src="[URL unfurl="true"]http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>[/URL]
<script type="text/javascript">
function divexpandcollapse(divname) {
var img = "img" + divname;
if ($("#" + img).attr("src") == "images/plus.png") {
$("#" + img)
.closest("tr")
.after("<tr><td></td><td colspan = '100%'>" + $("#" + divname)
.html() + "</td></tr>");
$("#" + img).attr("src", "images/minus.png");
} else {
$("#" + img).closest("tr").next().remove();
$("#" + img).attr("src", "images/plus.png");
}
}
</script>
<asp:GridView ID="MatGridView" runat="server" AllowSorting="True" DataKeyNames="fabpn" OnSorting="MatGridView_Sorting" HeaderStyle-BackColor="Black" HeaderStyle-ForeColor="White" OnRowDataBound="OnRowDataBound"
RowStyle-BackColor="Gray" AlternatingRowStyle-BackColor="White" AlternatingRowStyle-ForeColor="#000" AutoGenerateColumns="false" OnRowCreated="OnRowCreated" >
<Columns>
<asp:TemplateField>
<ItemTemplate>
<a href="JavaScript:divexpandcollapse('div<%# Eval("fabpn") %>');"><img alt="Details" id='imgdiv<%# Eval("fabpn") %>' src="images/plus.png" /></a><div id='div<%# Eval("fabpn") %>' style="display: none;">
<asp:GridView ID="gvDetails" runat="server" AutoGenerateColumns="false" CssClass = "ChildGrid">
<Columns>
<asp:BoundField DataField="ALTERNATE_KEY" HeaderText="Buyer Name" ReadOnly="true" />
<asp:BoundField DataField="CUSTOMER_PO_NUM" HeaderText="PO#" ReadOnly="true" />
<asp:BoundField DataField="JOB_DESCRIPTION" HeaderText="Description" ReadOnly="true" />
<asp:BoundField DataField="JOB_DELIVERY_DATE" HeaderText="Delivery Date" ReadOnly="true" />
<asp:BoundField DataField="Finished Goods P/N" HeaderText="F/G Part #" ReadOnly="true" />
<asp:BoundField DataField="F/G Kit#" HeaderText="F/G Kit #" ReadOnly="true" />
<asp:BoundField DataField="QTY_TO_MAKE" HeaderText="Qty To Make" ReadOnly="true" />
<asp:BoundField DataField="REMAINING_QTY" HeaderText="Qty Required" ReadOnly="true" />
</Columns>
</asp:GridView>
</div>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField ItemStyle-CssClass="col" DataField="fabpn" HeaderText="Fabric P/N" ReadOnly="True" SortExpression="fabpn" />
<asp:BoundField ItemStyle-CssClass="col" DataField="fabdesc" HeaderText="Description" ReadOnly="True" SortExpression="fabdesc" />
<asp:BoundField ItemStyle-CssClass="col" DataField="qty_allocated_wip" HeaderText="Qty Alloc To WiP" ReadOnly="True" SortExpression="qty_allocated_wip" />
<asp:BoundField ItemStyle-CssClass="col" DataField="qty_on_hand" HeaderText="Qty On Hand" ReadOnly="True" SortExpression="qty_on_hand" />
<asp:BoundField ItemStyle-CssClass="col" DataField="unit_of_measure" HeaderText="UoM" ReadOnly="True" SortExpression="unit_of_measure" />
<asp:BoundField ItemStyle-CssClass="col" DataField="ffree" HeaderText="Future Free" ReadOnly="True" SortExpression="ffree" />
<asp:HyperLinkField runat="server" HeaderText="Add/View Status" DataNavigateURLFields="fabpn" DataNavigateUrlFormatString="~/AddMaterialStatus.aspx?PartNum={0}" Text="Add/View Status" ></asp:HyperLinkField>
</Columns>
<EmptyDataTemplate>
There are currently no items in this table.
</EmptyDataTemplate>
</asp:GridView>
</asp:Content>and then this in my codebehind
Code:
public void bindGridView(string sortExp, string sortDir)
{
string connStr = ConfigurationManager.ConnectionStrings["onlinestatus"].ConnectionString;
SqlConnection mySQLconnection = new SqlConnection(connStr);
if (mySQLconnection.State == ConnectionState.Closed)
{
mySQLconnection.Open();
}
SqlCommand mySqlCommand = new SqlCommand("SELECT Distinct SUPPLIER, RTRIM(CM.[Fabric P/N]) as fabpn, [Fabric Description] as fabdesc, WAREHOUSE_TO_USE, Planner, QTY_ON_HAND, [FUTURE FREE] as ffree, QTY_ALLOCATED_WIP, Status, UNIT_OF_MEASURE, " +
"cast(Notes as varchar(50)) + ' on ' + cast(CONVERT(date,EnteredDate) as varchar(20)) + ' by ' + cast(UserName as varchar(20)) as Note " +
"FROM tbl_OnLineStatusMaterialsCM CM left outer join tbl_CustomerStatus CS on CM.[Fabric P/N] collate SQL_Latin1_General_CP1_CI_AS = CS.[Fabric P/N] " +
"where (Planner <> 'T') and (QTY_ON_HAND + QTY_ALLOCATED_WIP <> 0) and (WAREHOUSE_TO_USE <> 'DR') and SUPPLIER = '" + Session["Supplier"] + "'", mySQLconnection);
SqlDataAdapter mySqlAdapter = new SqlDataAdapter(mySqlCommand);
DataSet myDataSet = new DataSet();
mySqlAdapter.Fill(myDataSet);
DataView myDataView = new DataView();
myDataView = myDataSet.Tables[0].DefaultView;
if (sortExp != string.Empty)
{
myDataView.Sort = string.Format("{0} {1}", sortExp, sortDir);
}
MatGridView.DataSource = myDataView;
MatGridView.DataBind();
if (mySQLconnection.State == ConnectionState.Open)
{
mySQLconnection.Close();
}
}
protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
e.Row.Cells[7].ToolTip = DataBinder.Eval(e.Row.DataItem,"Note").ToString();
string fabpn = MatGridView.DataKeys[e.Row.RowIndex].Value.ToString();
GridView gvDetails = (GridView)e.Row.FindControl("gvDetails");
SqlDataSource dbSrc = new SqlDataSource();
dbSrc.ConnectionString = ConfigurationManager.ConnectionStrings["onlinestatus"].ConnectionString;
dbSrc.SelectCommand = "SELECT ALTERNATE_KEY,CUSTOMER_PO_NUM,JOB_DESCRIPTION,JOB_DELIVERY_DATE,[Finished Goods P/N],REMAINING_QTY,QTY_TO_MAKE,WAREHOUSE_TO_USE,SUPPLIER,[Fabric P/N],Job,[F/G Kit#] FROM view_MaterialDetailsForWeb Where SUPPLIER = '" + Session["Supplier"] + "' and [Fabric P/N] = '" + fabpn + "' order by JOB_DELIVERY_DATE";
gvDetails.DataSource = dbSrc;
gvDetails.DataBind();
}
}If I remove the nested gridview it runs nice and fast, but with the nested gridview it is painfully and I do mean painfully slow. There is not a huge amount of data. The first gridview has maybe 130 rows and the each row has 0-20 rows in the nested gridview. But with the nested gridview in place it takes close to 5 minutes to run, so it feels like I must be doing something wrong. Running the queries individually takes nowhere near this amount of time. Any ideas how I can speed this up?
Thanks,
Willie
