RadGridView DataBinding is slow....

[tphan]

Hi,

// dt is obtains from a database and contains the SQL Look like "Select ID, Parent_ID, Description from Customers"
// Customers is a self-reference table with Parent and Child Relationship

dt is a HUGE recordset contains more than 200,000 records. The Bottle-neck when I executed the code is the DataBinding, it stops on the line RadGridView1.DataSource = dt; Since it is the self-reference table...the RadGridView is display in hierarchy... e.g. Parent has child...child has another child... levels by levels...

public void Page_Load()
{
RadGridView1.BeginUpdate()
RadGridView1.DataSource = dt; 
RadGridView1.Relations.Clear(); 
RadGridView1.Relations.AddSelfReference(RadGridView1.MasterTemplate, "ID", "PARENT_ID"); 
RadGridView1.EndUpdate()
}

My Question is...Can I display the levels by demand ? Yes...there is a demo example on http://www.telerik.com/help/winforms/gridview-hierarchical-grid-load-on-demand-hierarchy.html BUT it is not related to self-reference table...Do you guys has any better approach to do it ?

Many Thanks!

[tphan]

OR simply saying...is that any way to speed up the data binding for a self-reference gridview ?
I run a SQL in Database Query, the time cost is 1 second BUT when databinding in self-reference gridview. it told me 10 seconds.
How can the gridview control downgrade the performance of the query ?

[Julian Benkov]

Hello Charles,

This huge recordset can not be processed fast using self-reference hierarchy mode. The internal build process creates BTree for all items and then creates more GridViewChilRowCollections based on the parent metadata.

The only possible solution in this situation is use Load-On-Demand hierarchy mode and manually setup each opened inner level.

Do not hesitate to contact us if you have further questions or issues. 
 
Kind regards,
Julian Benkov
the Telerik team

[tphan]

Hello

I don't udnerstand the meaning of  "manually setup each opened inner level." Could you share with some sample code ? So that I can try your suggestion on my code as well ?

Thanks !

[Tomas]

We found a way to load multi-level hierarchy on demand, but questions remain.
- Is there a way to get column widths to be aligned, as in self-referencing hierarchy?  
- Is there a better way to avoid child-template border then handling cell formatting event?
- Seems child rows always have [+] expand button, even if there are no child rows.  Is there a way to control when this button is visible or not?  

For reference code is:

        void radGridView1_RowSourceNeeded(object sender, GridViewRowSourceNeededEventArgs e)
        {
            Random random = new Random();
            int rowCount = random.Next(20);
            for (int j = 0; j < rowCount; j++)
            {
                GridViewRowInfo row = e.Template.Rows.NewRow();
                row.Cells["id"].Value = counter++;
                row.Cells["name"].Value = "test";
                e.SourceCollection.Add(row);
            }
        }


        public void SetupTemplate(GridViewTemplate template)
        {
            template.AllowAddNewRow = false;
            template.ShowColumnHeaders = false;
            template.ShowFilteringRow = false;
            template.Columns.Add(new GridViewTextBoxColumn("id"));
            template.Columns.Add(new GridViewTextBoxColumn("name"));
        }


        public void creatingHierarchicalGridInUnboundMode()
        {
            SetupTemplate (radGridView1.MasterTemplate);
            
            GridViewTemplate l1 = new GridViewTemplate();
            SetupTemplate (l1);
            radGridView1.MasterTemplate.Templates.Add(l1);
            l1.HierarchyDataProvider = new GridViewEventDataProvider(l1);

            GridViewTemplate l2 = new GridViewTemplate();
            SetupTemplate (l2);
            l2.HierarchyDataProvider = new GridViewEventDataProvider(l2);
            l1.Templates.Add(l2);

            radGridView1.ViewCellFormatting += new CellFormattingEventHandler(radGridView1_ViewCellFormatting);
            this.radGridView1.RowSourceNeeded += new GridViewRowSourceNeededEventHandler(radGridView1_RowSourceNeeded);
            LoadUnboundData();
        }


        void radGridView1_ViewCellFormatting(object sender, CellFormattingEventArgs e)
        {
            if (e.CellElement is GridDetailViewCellElement)
            {
                GridDetailViewCellElement cell = e.CellElement as GridDetailViewCellElement;
                cell.ChildTableElement.DrawBorder = false;
                e.CellElement.Padding = new Padding(0);
                e.CellElement.BorderWidth = 0;  
            }
        }


        private void LoadUnboundData()
        {
           using (radGridView1.DeferRefresh())
           {
               for (int i = 0; i < 10; i++)
               {
                   radGridView1.MasterTemplate.Rows.Add(counter++, "test");
               }
           }
        }

[Julian Benkov]

Hi Tomas,

You cannot achieve the same look and behavior when using hierarchy mode in RadGridView. However, you can change some visual settings to change how RadGridView looks. To do this, you should handle the ViewCellFormatting event:

void radGridView1_ViewCellFormatting(object sender, CellFormattingEventArgs e)
{
    GridDetailViewCellElement element = e.CellElement as GridDetailViewCellElement;
    if (element != null)
    {
        element.DrawBorder = false;
        element.Padding = Padding.Empty;
    }
}

Regarding the plus/minus sign, please consider the following  forum post.

If you have further questions, I will be glad to help.

All the best,
Julian Benkov
the Telerik team

[Laura]

I have a question regarding the use of the RowSourceNeeded event....   I have set this up, but the user has the ability to change the data in the child template.  Prior to using the RowSourceNeeded event, I used the CurrentChanged event of the binding source for the child grid.  Now that I am using the RowSourceNeeded event, I cannot trigger that event to update the database.

What is the recommendation for updating the database after a changed has been made by the user to the child grid using this method.  Note: I am using the DataRowView for the ParentRow.DataBoundItem and using the GetChildRows method as dictated by the example online. 

Laura

[Dess | Tech Support Engineer, Principal]

Hello Laura, 

Thank you for writing.  

The RowSourceNeeded event is fired when you click the expander for a certain parent row, not before that. Once a row has been expanded, the child rows are cached for further reuse. Hence, the RowSourceNeeded event won't be fired for the same row anymore. If you need to change the data for the already expanded rows, it is necessary to refresh the DataView of the respective child template. Thus, the RowSourceNeeded event will be fired again:

this.radGridView1.MasterTemplate.Templates[0].DataView.LazyRefresh();

 
I hope this information helps. Should you have further questions I would be glad to help.

Regards,
Dess
Telerik by Progress

[Laura]

Thank you Dess, but there is no event that is triggering upon the change that allows me to call the update to the data source... 

[Laura]

When I use this methodology http://docs.telerik.com/devtools/winforms/gridview/hierarchical-grid/load-on-demand-hierarchy and make a change to a row in the template, the database does not get updated.  Previously, I was using the CurrentChanged event on the binding source as noted in your documentation.  I had a separate binding source (with no relation to the parent grid) prior and it was working fine, it just took a very long time to update.

Now that I have moved to the load-on-demand method, I am not triggering that event at all and the database is not being updated.

[Dess | Tech Support Engineer, Principal]

Hello Laura, 

Thank you for writing back. 

When using the load on demand approach, you add the unbound rows programmatically to the child template. In this mode, the child template is not in bound mode. Hence, you should save the changes to the database programmatically. You can use the CellValueChanged or CurrentRowChanged event in order to save the changes. Alternatively, you can bind the child levels in RadGridView. Thus, when a cell value is updated, the change will be directly reflected DataSource and you can save the changes to the database.

I hope this information helps. If you have any additional questions, please let me know. 

Regards,
Dess
Telerik by Progress