[Solved] Very slow DetailTableDataBind

[Adam]

I'm using RadGrid in a ascx control as presented below. I'm having problem with performance with Detail tables. Sometimes there more than one detail table attached to master table row and when I'm expanding hierarchy function Radgrid_DetailTableDataBind2 is called n-times (n detail tables). The worst thing is that between second and third (when there is 3 detail tables) call there is sometimes 10 second delay. Is there some way to improve performance ?

In first approach there was :

Radgrid.MasterTableView.HierarchyLoadMode = GridChildLoadMode.Client;
Radgrid.ClientSettings.ClientEvents.OnHierarchyExpanded = "OnHierarchyExpanded";
Radgrid.ClientSettings.ClientEvents.OnHierarchyCollapsed = "OnHierarchyCollapsed";

but function Radgrid_DetailTableDataBind2 on initialization was called n * Row (n - numer of detail tables, Row - visible rows on page), which was on 10 visible rows was even 40 times.

I'm attaching most important function. IntializeGrid() is called in  protected void Page_Init(object sender, EventArgs e).

IntializeGrid()

{
            Radgrid = new RadGrid();

            Radgrid.ItemEvent += new GridItemEventHandler(Radgrid_ItemEvent);
            Radgrid.Init += new EventHandler(Radgrid_Init);

            Radgrid.NeedDataSource += new GridNeedDataSourceEventHandler(Radgrid_NeedDataSource);

 

Radgrid.PreRender += new EventHandler(Radgrid_PreRender);

Radgrid.DetailTableDataBind += new GridDetailTableDataBindEventHandler(Radgrid_DetailTableDataBind2);

            Radgrid.MasterTableView.HierarchyLoadMode = GridChildLoadMode.ServerOnDemand;

 

...

}

protected void Radgrid_Init(object sender, EventArgs e)
{
 Radgrid.ItemCommand += new GridCommandEventHandler(Radgrid_ItemCommand);

                Radgrid.MasterTableView.DetailTables.Clear();
                ArrayList Cols = (Session[IdOkna + this.ID + "MultiLayerMainCols"] as ArrayList);
                for (int i = 0; i < Cols.Count; i++)
                {
                    GridTableView tableViewLevel = new GridTableView(Radgrid);
                    tableViewLevel.HierarchyLoadMode = GridChildLoadMode.ServerOnDemand;
                    tableViewLevel.AutoGenerateColumns = false;

                    string TableName = Cols[i].ToString();
                    try
                    {
                        MainCol = ((XmlDocument)Session[IdOkna + this.ID + "XML_Doc"]).SelectSingleNode("//columns/child::column[source='" + TableName + "']").SelectSingleNode("columns/child::column[editSync='s']").SelectSingleNode("source").InnerText;
                        XmlNode xg = (SubColumns[MainCol + "." + TableName] as XmlNode);
                        loadsubcolumns(tableViewLevel, xg);
                    }
                    catch
                    {
                        loadSameColumns(tableViewLevel);
                    }
                    Radgrid.MasterTableView.DetailTables.Add(tableViewLevel);

...
}

void Radgrid_PreRender(object sender, EventArgs e)
{
    if ((HttpContext.Current.Session[IdOkna + ID + "MultiLayerMainCols"] as ArrayList).Count != 1)
        indekser = 0;

    if (HttpContext.Current.Session[IdOkna + ID + "ExpandItems"] != null)
    {

        Hashtable table = (HttpContext.Current.Session[IdOkna + ID + "ExpandItems"] as Hashtable);

        foreach (int key in table.Keys)
        {
            Radgrid.MasterTableView.Items[key].Expanded = Convert.ToBoolean(table[key]);

           if ((HttpContext.Current.Session[IdOkna + ID + "MultiLayerMainCols"] as ArrayList).Count != 1)
                indekser = 0;
        }
    }
}

void Radgrid_DetailTableDataBind2(object source, GridDetailTableDataBindEventArgs e)
{
    GridDataItem dataItem = (GridDataItem)e.DetailTableView.ParentItem;

    int index = dataItem.ItemIndex;

    int HCount;
    string Stable;
    string SHIndex;

    int kolumny1, kolumny2;

    if (HttpContext.Current.Session[IdOkna + ID + "MultiLayerMainCols"] != null)
        HCount = (HttpContext.Current.Session[IdOkna + ID + "MultiLayerMainCols"] as ArrayList).Count;
    else HCount = 0;

    if (HttpContext.Current.Session[IdOkna + ID + "SHIndex"] != null)
        SHIndex = HttpContext.Current.Session[IdOkna + ID + "SHIndex"].ToString();
    else SHIndex = "0";

    if (HttpContext.Current.Session[IdOkna + ID + "Stable"] != null)
        Stable = HttpContext.Current.Session[IdOkna + ID + "Stable"].ToString();
    else
        Stable = "";

    kolumny1 = index * HCount + Int32.Parse(SHIndex);

    kolumny2 = index * HCount + indekser;

    if (kolumny1 != kolumny2 && HCount > 1)
        e.DetailTableView.Visible = false;
    else
    {
        e.DetailTableView.Visible = true;

        Hashtable hash = (HttpContext.Current.Session[IdOkna + ID + "SubColumns"] as Hashtable);
        string MainID = HttpContext.Current.Session[IdOkna + ID + "Synchro"].ToString();
        string StringTable = Stable;
        string ColumnID = "";
        if (Stable == "")
        {
            StringTable = (HttpContext.Current.Session[IdOkna + ID + "MultiLayerMainCols"] as ArrayList)[0].ToString();
            ColumnID = (HttpContext.Current.Session[IdOkna + ID + "XML_Doc"] as XmlDocument).SelectSingleNode("//column[source='" + StringTable + "']").SelectSingleNode("columns/column[editSync='s']").SelectSingleNode("source").InnerText.Trim();
        }
        else
            ColumnID = (HttpContext.Current.Session[IdOkna + ID + "XML_Doc"] as XmlDocument).SelectSingleNode("//column[source='" + Stable + "']").SelectSingleNode("columns/column[editSync='s']").SelectSingleNode("source").InnerText;

        string columns = "";
        XmlNode gn = (hash[ColumnID + "." + StringTable] as XmlNode);
        string LinkKey = ColumnID;
        if (gn.ParentNode.SelectSingleNode("editSync").InnerText == "d")
        {
            LinkKey = gn.ParentNode.SelectSingleNode("source").InnerText;
        }
        SQL_builder sql = (SQL_builder)HttpContext.Current.Session[IdOkna + ID + "sql"];

        string id = "";
        DataTable MainTable = (HttpContext.Current.Session[IdOkna + ID + "MainTable"] as DataTable);
        id = MainTable.Rows[index][LinkKey].ToString();

        if (gn.ParentNode.SelectSingleNode("type").InnerText == "t")
        {
            id = id.Insert(0, "'");
            id = id.Insert(id.Length, "'");
        }


        pb = (PolaczBroker)HttpContext.Current.Session[IdOkna + ID + "PolaczBroker"];
        bk = pb.BrokerKlient;

        string MyQuery = "";
        string ForeignKey = "";
        //set sort
        if (gn.Attributes["Sort"] != null)
            sql.SetMultiLayerOrderByClause(gn.Attributes["Sort"].Value);
        // set from
        sql.SetMultiLayerFromClause(StringTable);

        //set parameters from session as hashtable == compatibility so old WF will still be working
        if (HttpContext.Current.Session[IdOkna + ID + "MultiLayerParam"] != null)
        {
            Hashtable MultiParam = (HttpContext.Current.Session[IdOkna + ID + "MultiLayerParam"] as Hashtable);
            if (MultiParam.Contains(StringTable))
            {
                if ((MultiParam[StringTable] as Hashtable).ContainsKey("FromClauseParameters"))
                    sql.SetMultiLayerFromClauseParameters((MultiParam[StringTable] as Hashtable)["FromClauseParameters"].ToString());
                else
                    sql.SetMultiLayerFromClauseParameters("");
                if ((MultiParam[StringTable] as Hashtable).ContainsKey("WhereClause"))
                    sql.SetMultiLayerWhereClause((MultiParam[StringTable] as Hashtable)["WhereClause"].ToString());
                else
                    sql.SetMultiLayerWhereClause("");
            }
            else
            {
                sql.SetMultiLayerFromClauseParameters("");
                sql.SetMultiLayerWhereClause("");
            }
        }
        else
        {
            // new parameters from session and clear it if no session provided
            if (HttpContext.Current.Session[IdOkna + ID + StringTable + "SetMultiLayerWhereClause"] != null)
            {
                sql.SetMultiLayerWhereClause(HttpContext.Current.Session[IdOkna + ID + StringTable + "SetMultiLayerWhereClause"].ToString());
            }
            else
                sql.SetMultiLayerWhereClause("");

            if (HttpContext.Current.Session[IdOkna + ID + StringTable + "SetMultiLayerFromClauseParameters"] != null)
            {
                sql.SetMultiLayerFromClauseParameters(HttpContext.Current.Session[IdOkna + ID + StringTable + "SetMultiLayerFromClauseParameters"].ToString());
            }
            else
                sql.SetMultiLayerFromClauseParameters("");

            if (HttpContext.Current.Session[IdOkna + ID + StringTable + "SetMultiLayerFromClause"] != null)
            {
                sql.SetMultiLayerFromClause(HttpContext.Current.Session[IdOkna + ID + StringTable + "SetMultiLayerFromClause"].ToString());
            }
        }

        if ((gn.Attributes["Self"] != null) && (gn.Attributes["Self"].Value == "true"))
        {
            if (gn.SelectSingleNode("ForeignKey") != null)
                ForeignKey = gn.SelectSingleNode("ForeignKey").InnerText;
            else
                ForeignKey = ColumnID;

            sql.SetMultiLayerColumns("");
        }
        else
        {
            kf = (Konfig)HttpContext.Current.Session[IdOkna + ID + "Konfig"];
            if (gn.Attributes["TypeServer"] != null)
                columns = kf.getColumnList(gn, gn.Attributes["TypeServer"].Value);
            else
                columns = kf.getColumnList(gn, HttpContext.Current.Session[IdOkna + ID + "TypeServer"].ToString());
            sql.SetMultiLayerColumns(columns);

        }

        MyQuery = sql.getMultiLayerSqlScript(ForeignKey, id, "50");

        //get data
        DataTable table = new DataTable();
        if ((HttpContext.Current.Session[IdOkna + ID + "TypeServer"].ToString() == "ADS") ||
            ((gn.Attributes["TypeServer"] != null) && (gn.Attributes["TypeServer"].Value == "ADS")))
            table = (DataTable)bk.SQL_Broker_Query(pb.getBaza(), MyQuery, false).ZwrocDataSet().Tables[0];
        else
        {
            DataSet ds = new DataSet();
            Hashtable danebazy = bk.PRM_OneDataBase(pb.getBaza());

            string adresBazy = ((string[])(danebazy["DBAdres"]))[0]; //adres bazy
            string login = danebazy["DBLogin"].ToString();//login
            string password = danebazy["DBHaslo"].ToString();//hasło
            string catalog = danebazy["DBNazwaBazy"].ToString();//nazwa bazy

            SqlConnection con = new SqlConnection("Data Source=" + adresBazy + ";User ID=" + login + "; Password=" + password + ";initial catalog=" + catalog + ";integrated security=false;");

            SqlDataAdapter adapter = new SqlDataAdapter(MyQuery, con);//("SELECT kat_id, isnull(parent_id,0) as parent_id, nazwa FROM [dbo].[" + subNode.SelectSingleNode("table").InnerText + "_DRZEWO_MAPA_WYSZ] where parent_id=" + id + " order by parent_id", con);
            con.Open();

            adapter.Fill(ds);
            con.Close();
            table = (DataTable)ds.Tables[0];

            //table = (DataTable)bk.SQL_Query(pb.getBaza(), MyQuery, false).Tables[0];
        }

       for (int i = 0; i < table.Rows.Count; i++)
            for (int j = 0; j < table.Columns.Count; j++)
            {
                if ((table.Rows[i][j].GetType().Name != "Decimal") && (table.Rows[i][j].GetType().Name != "Int64") && (table.Rows[i][j].GetType().Name != "Int32") && (table.Rows[i][j].GetType().Name != "Boolean") && (table.Rows[i][j].GetType().Name != "DBNull") && (table.Rows[i][j].GetType().Name != "DateTime") && (table.Rows[i][j].GetType().Name != "Double"))
                {
                    if (table.Columns[j].Caption != "MainRowID")
                    {
                        string strID = table.Columns[j].Caption + "_" + i + "_" + j;

                        string strMouseOver = " onmouseover=CreateTip(this.id,this.innerHTML,'" + table.Columns[j].Caption + "'," + i + ");";

                        string strMouseOut = " onmouseout='RemoveTip();'";

                        table.Rows[i][j] = "<span id='" + strID + "' " + strMouseOver + strMouseOut + " >" + table.Rows[i][j] + "</span>";
                    }
                }
            }

        e.DetailTableView.DataSource = table;
        HttpContext.Current.Session[IdOkna + ID + index + "SubTable"] = table;
    }

    indekser++;
    string[] trace = IdOkna.Split('^');

    System.Diagnostics.Debug.Write(DateTime.Now.Hour.ToString() + ":" + DateTime.Now.Minute.ToString() + "." + DateTime.Now.Second.ToString() + "." + DateTime.Now.Millisecond.ToString() + " | ");
    System.Diagnostics.Debug.WriteLine(trace[2] + "-" + trace[3] + "-" + trace[1] + "-" + trace[0] + "Radgrid_DetailTableDataBind2 " + indekser);
}

If there's missing some code please give me a hint which function...
Thank you in advance

[Marin]

Hi,

 You can check out some of the other hierarchy load modes of the grid:
http://www.telerik.com/help/aspnet-ajax/grid-hierarchy-load-modes.html

HierarchyLoadMode ="Client" will take a lot of time initially and on postback because all detail tables will be databound. But after that expand / collapse will only happen on the client-side without additional postback. While the default value HierarchyLoadMode="ServerOnDemand" will only bind the current detail table that is expanding and the DetailTableDataBind event will not be called multiple times.

Regards,
Marin
the Telerik team