DataGridView setting DataSource is too slow

See more:

C#

forms

Hi there,

I'm currently having a really weird problem. I have created a custom control with 2 gridviews and 2 custom scrollbars on it, gridHeader on top, gridData on the bottom. The first gridview (gridHeader) is used for the user to enter a search query on every individual column. The second gridview (gridData) is used to view the resulting data. For each column in gridHeader I also create a filter control that will be placed over the columnheader, with this filter control the user can also filter the data by checking on/off the unique values in list of the column(click on a little button in the column and you'll see the unique values).

The control works and has proven itself, BUT it's too slow. It can take up to 1000 ms to load some datasource. After checking the code by adding timers I figured out the problem is not the heavy filtering I do, it turns out its caused by the gridviews when being assigned to a datatable and also while adding the filter controls to the columnheaders.

To kinda solve the second problem I started by not removing the FilterColumnClass when not detected and leaving them in memory, so the second load will be faster. But if I assign the datasource of the header-gridview filled with only 1 row it takes about 180 ms, while when I assign the datasource of the data-gridview filled with 100 rows it only takes about 10 ms.

When the header-grid is assigned, 1 event gets triggerd which doesn't do anything because of the "_StopEventHandling" flag which is set to true.

I've put the timings as a comment in the code, 1st means; The first time you assign a datatable to the control, 2nd means; The second time you've assigned a datatable. The time in ms you see is the time it took compaired to the beginning of the function or compaired to the last time-snapshot.

Here is part of the code in question:

public class SomeCustomUserControl
{
    private DataTable _AllData = null;
    private DataTable _ViewData = null;
        
    private List<FilterColumnClass> _FilterColumns = new List<FilterColumnClass>();
        
    private bool _StopEventHandling = false;
    private bool _DataBound = false;
    private bool _DataBoundToSendSelected = false;
        
    private int _LastSortColumnIndex = -1;
    private bool _LastSortColumnDir = false;

    public void SetDataSource(DataTable value)
    {
        // Set all filtercontrols to invisible
        foreach (FilterColumnClass filter in _FilterColumns)
        {
            filter.FilterCheckControl.Visible = false;
        }

        // Assigning the received datatable to a local variable
        _AllData = value;

        // Do the databinding
        DataBind();
    }

    private void DataBind()
    {
        if (_AllData != null)
        {
            // Letting the control know we've started databinding the data.
            _DataBound = true;

            // Letting the control know it should NOT handle any events from the grids
            _StopEventHandling = true; 

            // Included in example: scroll down!
            PrepareGridViews(); 
 
            // 1st: 0ms 2nd: 0ms

            // Included in example: scroll down!
            PrepareFilterColumns(); 
 
            // 1st: 472ms 2nd: 252ms
 
            // ================ !!! HERE !!! ====================
            // Creates a new datatable according the filters
            _ViewData = FilterRows(_AllData, _FilterColumns); 
            gridData.DataSource = _ViewData;  
            // ================ !!! HERE !!! ====================
            // THIS IS REALLY FAST: 1st: 12ms 2nd: 10ms
            // The _ViewData datatable contains around 100 records

            // Sorts the new datasource according the last sort
            if (_LastSortColumnIndex != -1)
            {
                if (gridHeader.Columns.Count > _LastSortColumnIndex)
                {
                    try
                    {
                        if (_LastSortColumnDir)
                            gridData.Sort(gridData.Columns[gridHeader.Columns[_LastSortColumnIndex].Name], ListSortDirection.Ascending);
                        else
                            gridData.Sort(gridData.Columns[gridHeader.Columns[_LastSortColumnIndex].Name], ListSortDirection.Descending);
                    }
                    catch
                    {
                    }
                }
            }

            // 1st: 0ms 2nd: 0ms

            // Sets the columns to the users preferences, like column-name, column-width and column-order
            if (gridData.Columns.Count > 0) Personalize_Columns();

            // 1st: 194ms 2nd: 187ms

            // Sets the custom scrollbars min, max and value settings
            PrepareScrollBars();

            // 1st: 0ms 2nd: 1ms

            // Resets the vertical position of the custom scrollbars
            SetVerticalPosition(vScrollBar1.Value);

            // 1st: 0ms 2nd: 0ms

            // Resets the horizontal position of the custom scrollbars
            foreach (DataGridViewColumn col in gridHeader.Columns)
            {
                if (col.DisplayIndex == 0)
                {
                    _LastScrollingColumnIndex = -1;
                    SetHorizontalPosition(col.Index);
                }
            }
            // Checks if the parent-control wants to mark some items in the grid
            AskMarkedItems();

            // 1st: 196ms 2nd: 36ms

            // Letting the control know we're done databinding the data
            _DataBoundToSendSelected = true;

            // Letting the control know it should continue handling the events from the grids
            _StopEventHandling = false;
        }
        else
        {
            // Reset the grids
            gridHeader.DataSource = null;
            gridData.DataSource = null;
            _ViewData = null;
        }
 
        // TOTAL 1st: 874ms 2nd: 486ms
    }

    private void PrepareGridViews()
    {
        // Speeds up the gridviews
        SetDoubleBuffered(gridData, true); 
        SetDoubleBuffered(gridHeader, true);

        // Templating
        gridHeader.RowTemplate.Height = PublicFunctions.UniversalHeight - 6; 
        gridData.RowTemplate.Height = PublicFunctions.UniversalHeight - 6;

        // No columnheader because we use the one from gridHeader
        gridData.ColumnHeadersVisible = false; 
    }
    private void PrepareFilterColumns()
    {
        // Index counter for the column order
        int index = 0;

        // Suspend the layout to speed up the adding of the filter controls
        this.SuspendLayout();

        foreach (DataColumn col in _AllData.Columns)
        {
            // First try to find the the filter column class, if it is already added
            FilterColumnClass filter = _FilterColumns.Find(delegate(FilterColumnClass item) { return item.ColumnName == col.ColumnName; });

            if (filter != null)
            {
                // filter column class found, reset the index to be sure
                filter.Index = index;
                filter.FilterCheckControl.Visible = true;
            }
            else
            {
                // filter column not found, create one and add it to this control
                FilterColumnClass new_filter = new FilterColumnClass();
                new_filter.ColumnName = col.ColumnName;
                new_filter.Index = index;
                new_filter.FilterCheckControl.Visible = true;
                new_filter.FilterCheckControl.eventAskCurrentDataTable += new AskDataTableByInt(FilterCheckControl_eventAskCurrentDataTable);
                new_filter.FilterCheckControl.eventAskSourceDataTable += new AskDataTableByInt(FilterCheckControl_eventAskSourceDataTable);
                new_filter.FilterCheckControl.eventApplyFilter += new EventHandler(FilterCheckControl_eventApplyFilter);

                _FilterColumns.Add(new_filter);
                this.Controls.Add(new_filter.FilterCheckControl);
            }
            index++;
        }

        // Adding of filter controls complete resume the layout
        this.ResumeLayout();

        // ================ !!! HERE !!! ====================
        // 1st: 256ms 2nd: 46ms 
        // ================ !!! HERE !!! ====================

        // Set the filter columns that are not found to invisible
        foreach (FilterColumnClass filter in _FilterColumns)
        {
            bool found = false;
            foreach (DataColumn col in _AllData.Columns)
            {
                if (filter.ColumnName == col.ColumnName)
                {
                    found = true;
                    break;
                }
            }
            if (!found)
            {
                filter.FilterCheckControl.Visible = false;
            }
        }
        // 1st: 0ms 2nd: 0ms

        // Create a datatable for the header with empty strings
        DataTable header = new DataTable("FilterRow");

        // Add all columns of the datasource to it
        foreach (DataColumn col in _AllData.Columns)
        {
            header.Columns.Add(new DataColumn(col.ColumnName, typeof(string)));
        }

        // 1st: 0ms 2nd: 0ms

        // Create a empty row for it
        DataRow filterrow = header.NewRow();

        foreach (FilterColumnClass filter in _FilterColumns)
        {
            // If the filter control is visible we have to take over the old value of that filter.
            if (filter.FilterCheckControl.Visible)
            {
                try
                {
                    filterrow.ItemArray[filter.Index] = filter.FilterTextString;
                }
                catch (Exception ex)
                {
                    PublicFunctions.ReportBug(ex, _Shared);
                }
            }
        }

        // Add the filter row to the header datatable
        header.Rows.Add(filterrow);

        // 1st: 0ms 2nd: 0ms

        // ================ !!! HERE !!! ====================
        // Assign the header datatable to the grid
        gridHeader.DataSource = header; 
        // ================ !!! HERE !!! ====================
        // THIS IS REALLY SLOW 1st: 162ms 2nd: 171ms
        // The header datatable contains 1 row

        // TOTAL 1st: 418ms 2nd: 217ms
    }

    private void SetDoubleBuffered(DataGridView grid, bool setting)
    {
        Type dgvType = grid.GetType();
        PropertyInfo pi = dgvType.GetProperty("DoubleBuffered", BindingFlags.Instance | BindingFlags.NonPublic);
        pi.SetValue(grid, setting, null);
    }
}

I hope you guys can see through the big amount of code.