Filtering has slowed down...

[Skurken]

Hi Telerik

We have been using RadGridView for ages. Some of our customers have approx 100.000 rows of customers displayed in the grid. We have always gotten good remarks because of the fast filtering in the grids (isLike filtering on Textboxcolumns), however, as of lately it has gotten much slower, even though we have not made changes in code.

Have you made any changes  in grid, that could cause it to slow down?

 

Should it not be lightning fast with 100.000 rows?

 

I have tried  implementing a delayed filtering, so that if the user types "Test" fast, it will only filter once. However, it is giving me a lot of headache - have you seen any examples of people accomplishing this?

[Dimitar]

Hi Anders,

We do not have other reports for similar issues. Could you please specify to which version you have upgraded when your users noticed the delay and the version used prior the upgrade. This way we will be able to perform some tests and determine if there is a difference.

In addition here is how you can implement delayed filter:

Timer timer = new Timer();
bool canFilter = false;
public RadForm1()
{
    InitializeComponent();
    radGridView1.DataSource = GetTable();
    radGridView1.EnableFiltering = true;
    radGridView1.ValueChanging += RadGridView1_ValueChanging;
    radGridView1.FilterChanging += RadGridView1_FilterChanging;
    timer.Tick += Timer_Tick;
    timer.Interval = 3000;
}

private void RadGridView1_ValueChanging(object sender, ValueChangingEventArgs e)
{
    if (radGridView1.CurrentCell is GridFilterCellElement)
    {
        canFilter = false;
        if (!timer.Enabled)
        {
            timer.Start();
        }

    }
}

private void RadGridView1_FilterChanging(object sender, Telerik.WinControls.UI.GridViewCollectionChangingEventArgs e)
{
    e.Cancel = !canFilter;
}

private void Timer_Tick(object sender, EventArgs e)
{
    canFilter = true;
    timer.Stop();
    var editor = radGridView1.ActiveEditor as RadTextBoxEditor;
    radGridView1.ValueChanging -= RadGridView1_ValueChanging;
    if (editor != null)
    {
        var temp = editor.Value;
        var element = editor.EditorElement as RadTextBoxEditorElement;
        element.Text = "";
        element.Text = temp.ToString();
        element.TextBoxItem.SelectionStart = temp.ToString().Length;
    }
    radGridView1.ValueChanging += RadGridView1_ValueChanging;
}

I am looking forward to your reply.

Regards,
Dimitar
Telerik by Progress

[Skurken]

Hi Dimitar

 

Thank you very much for your answer. I have tried implementing your solution, and it has gotten me closer to something that might work.

 

However I have had to change your:

element.Text = "";
element.Text = temp.ToString();

to:

element.Text = "443%%34344243¤¤#¤"¤#"#¤""; // Something unlikely to be in data
 element.Text = temp.ToString();

Otherwise the filtering is not handled if user removes all from filtercell. Solution works, but it is a bit of a shame that it results in two filterings. Performance-wise, it would have been great if it could be just one filtering.

I could live with the above, however, a worse problem is that if the user leaves a filtering cell before the timer ticks, then it gets messed up. I have tried handling the "CellEndEdit" to force a filtering when user leaves filtering cell, but in that event handler "ActiveEditor" is NULL, so that does not seem to be the way to go.

 

Again, thank you very much for your help. Maybe Telerik would consider adding a delayed filter functionality to the GridView directly?

 

[Dimitar]

Hi Anders,

Another solution would be to filter when Enter is pressed, here is an example:

public RadForm1()
{
    InitializeComponent();
    radGridView1.DataSource = GetTable();
    radGridView1.EnableFiltering = true;

    radGridView1.FilterChanging += RadGridView1_FilterChanging;
    radGridView1.CellEditorInitialized += RadGridView1_CellEditorInitialized;
}
bool canFilter = false;
private void RadGridView1_CellEditorInitialized(object sender, GridViewCellEventArgs e)
{
    if (e.Row is GridViewFilteringRowInfo)
    {
        var editor = e.ActiveEditor as RadTextBoxEditor;
        if (editor != null)
        {
            var element = editor.EditorElement as RadTextBoxEditorElement;
            element.TextBoxItem.KeyDown -= TextBoxItem_KeyDown;
            element.TextBoxItem.KeyDown += TextBoxItem_KeyDown;
        }
    }
}

private void TextBoxItem_KeyDown(object sender, KeyEventArgs e)
{
    if (e.KeyCode == Keys.Enter )
    {
        canFilter = true;
    }
}

private void RadGridView1_FilterChanging(object sender, Telerik.WinControls.UI.GridViewCollectionChangingEventArgs e)
{
    e.Cancel = !canFilter;
    canFilter = false;
}

I hope this will be useful. 

Regards,
Dimitar
Telerik by Progress

[Skurken]

Hi Dimitar

Again, thank you for your help. Using enter for filtering is not an option though. We use your grids all over our application, and users are customed to the automatic filtering. So I really need to get it working with the delay. It seems I am almost there, only problem now is that it needs to filter when leaving a filter cell.

Best regards

Anders

[Dimitar]

Hello Anders,

In order to handle this case, you should manually add descriptor when the cell loses the focus. Here is an updated example:

Timer timer = new Timer();
bool canFilter = false;
public RadForm1()
{
    InitializeComponent();
    radGridView1.DataSource = GetTable();
    radGridView1.EnableFiltering = true;
    radGridView1.ValueChanging += RadGridView1_ValueChanging;
    radGridView1.FilterChanging += RadGridView1_FilterChanging;

    timer.Tick += Timer_Tick;
    timer.Interval = 3000;
    radGridView1.CellEndEdit += RadGridView1_CellEndEdit;
}

private void RadGridView1_CellEndEdit(object sender, GridViewCellEventArgs e)
{
    if (radGridView1.CurrentCell is GridFilterCellElement)
    {
        canFilter = true;

        GridViewDataColumn col = e.Column as GridViewDataColumn;

        if (col != null)
        {
            if (col.FilterDescriptor != null)
            {
                col.FilterDescriptor.Value = e.Value;
            }
            else
            {
                col.FilterDescriptor = new Telerik.WinControls.Data.FilterDescriptor(col.FieldName, GridViewHelper.GetDefaultFilterOperator(col.DataType), e.Value);
            }
        }
    }
}
private void RadGridView1_ValueChanging(object sender, ValueChangingEventArgs e)
{
    if (radGridView1.CurrentCell is GridFilterCellElement)
    {
        canFilter = false;
        if (!timer.Enabled)
        {
            timer.Start();
        }

    }
}

private void RadGridView1_FilterChanging(object sender, Telerik.WinControls.UI.GridViewCollectionChangingEventArgs e)
{
    e.Cancel = !canFilter;
}

private void Timer_Tick(object sender, EventArgs e)
{
    canFilter = true;
    timer.Stop();

    radGridView1.ValueChanging -= RadGridView1_ValueChanging;
    if (radGridView1.IsInEditMode)
    {
        radGridView1.EndEdit();
        radGridView1.BeginEdit();
        var editor = radGridView1.ActiveEditor as RadTextBoxEditor;
        if (editor != null)
        {
            var element = editor.EditorElement as RadTextBoxEditorElement;

            element.TextBoxItem.SelectionStart = element.Text.Length;
        }
        radGridView1.ValueChanging += RadGridView1_ValueChanging;
    }
}

Let me know how this works on your side.

Regards,
Dimitar
Telerik by Progress

[Skurken]

Hi Dimitar

I implementet your Cell_EndEdit handler. 

I am seeing something that I can not explain. First time I type something in the filtercell the Cell_EndEdit handles the change correctly. However if I return to the same filtercell and type something more, then e.value contains the old value... the newly typed value is nowhere to be found in the eventargs. :S

Besides this problem everything is now working as intended, however this problem is rather a showstopper now.

[Dimitar]

Hello Anders,

I was unable to reproduce this. I have recorded a small video that shows what I am doing. Could you please check it and let me know what else I need to do?

I am looking forward to your reply.

 

Regards,
Dimitar
Telerik by Progress

[Skurken]

Hi again Dimitar

I have made an example here, also included video... I have simplified it a bit, since current problem is not timer related.

Looking very much forward to hearing from you! :)

Video here: https://www.screencast.com/t/MIgGILfpqj

Code here:

using System;
using System.Data;
using System.Windows.Forms;
using Telerik.WinControls.UI;

namespace TelerikGridTest
{
    public partial class Form1 : Form
    {
        private bool _canFilter;

        private Random randomGenerator = new Random();
        private const int ROWS_COUNT = 50000;
        private string[] randomStrings;

        public Form1()
        {
            InitializeComponent();
            radGridView1.DataSource = GetTable();
            radGridView1.EnableFiltering = true;
            radGridView1.FilterChanging += RadGridView1_FilterChanging;
            radGridView1.CellEndEdit += RadGridView1_CellEndEdit;
        }

        private DataTable GetTable()
        {
            randomStrings = new string[20];

            for (int i = 0; i < this.randomStrings.Length; i++)
            {
                randomStrings[i] = "String " + (i + 1);
            }

            DataColumn stringColumn = new DataColumn("StringColumn", typeof(string));

            var dataTable = new DataTable();
            dataTable.Columns.Add(stringColumn);

            dataTable.BeginLoadData();
            for (int i = 0; i < ROWS_COUNT; i++)
            {
                DataRow row = dataTable.NewRow();
                row["StringColumn"] = this.randomStrings[randomGenerator.Next(0, this.randomStrings.Length - 1)];
                dataTable.Rows.Add(row);
            }
            dataTable.EndLoadData();

            return dataTable;
        }


        private void RadGridView1_CellEndEdit(object sender, GridViewCellEventArgs e)
        {
            if (radGridView1.CurrentCell is GridFilterCellElement)
            {
                _canFilter = true;

                GridViewDataColumn col = e.Column as GridViewDataColumn;

                if (col != null)
                {
                    if (col.FilterDescriptor != null)
                    {
                        col.FilterDescriptor.Value = e.Value;
                    }
                    else
                    {
                        col.FilterDescriptor = new Telerik.WinControls.Data.FilterDescriptor(col.FieldName, GridViewHelper.GetDefaultFilterOperator(col.DataType), e.Value);
                    }
                }

                _canFilter = false;
            }
        }

        private void RadGridView1_FilterChanging(object sender, GridViewCollectionChangingEventArgs e)
        {
            e.Cancel = !_canFilter;
        }
    }
}

 

 

/ Anders

[Dimitar]

Hi Anders,

I was able to reproduce this. It is caused because by default the grid is setting the value of the filter directly, but we are canceling this change. So to handle this you can save the value while the user is typing, in the ValueChanged event:

object value;
private void RadGridView1_CellValueChanged(object sender, EventArgs e)
{
    value = radGridView1.ActiveEditor.Value;
}

Then you can use the value like this:

private void RadGridView1_CellEndEdit(object sender, GridViewCellEventArgs e)
{
    if (radGridView1.CurrentCell is GridFilterCellElement)
    {
        _canFilter = true;

        GridViewDataColumn col = e.Column as GridViewDataColumn;

        if (col != null)
        {
            if (col.FilterDescriptor != null && value != col.FilterDescriptor.Value)
            {
                col.FilterDescriptor.Value = value;
            }
            else
            {
                col.FilterDescriptor = new Telerik.WinControls.Data.FilterDescriptor(col.FieldName, GridViewHelper.GetDefaultFilterOperator(col.DataType), e.Value);
            }
        }

        _canFilter = false;
    }

}

Let me know how this works on your side.

Regards,
Dimitar
Telerik by Progress

[Skurken]

Hi again

I really appreciate your efforts, however, that solution seems to not solve the problem. Instead it blanks the filter whenever you type something for the second time.

 

Video here: VIDEO

 

Code here:

using System;
using System.Data;
using System.Windows.Forms;
using Telerik.WinControls.UI;

namespace TelerikGridTest
{
    public partial class Form1 : Form
    {
        private bool _canFilter;
        private object value;

        private Random randomGenerator = new Random();
        private const int ROWS_COUNT = 50000;
        private string[] randomStrings;

        public Form1()
        {
            InitializeComponent();
            radGridView1.AutoSizeColumnsMode = GridViewAutoSizeColumnsMode.Fill;
            radGridView1.DataSource = GetTable();
            radGridView1.EnableFiltering = true;
            radGridView1.FilterChanging += RadGridView1_FilterChanging;
            radGridView1.CellEndEdit += RadGridView1_CellEndEdit;
            radGridView1.CellValueChanged += RadGridView1_CellValueChanged;
        }

        private void RadGridView1_CellValueChanged(object sender, GridViewCellEventArgs e)
        {
            value = radGridView1.ActiveEditor.Value;
            Console.WriteLine("Value is now: " + value);
        }

        private DataTable GetTable()
        {
            randomStrings = new string[20];

            for (int i = 0; i < this.randomStrings.Length; i++)
            {
                randomStrings[i] = "String " + (i + 1);
            }

            DataColumn stringColumn = new DataColumn("StringColumn", typeof(string));

            var dataTable = new DataTable();
            dataTable.Columns.Add(stringColumn);

            dataTable.BeginLoadData();
            for (int i = 0; i < ROWS_COUNT; i++)
            {
                DataRow row = dataTable.NewRow();
                row["StringColumn"] = this.randomStrings[randomGenerator.Next(0, this.randomStrings.Length - 1)];
                dataTable.Rows.Add(row);
            }
            dataTable.EndLoadData();

            return dataTable;
        }


        private void RadGridView1_CellEndEdit(object sender, GridViewCellEventArgs e)
        {
            if (radGridView1.CurrentCell is GridFilterCellElement)
            {
                _canFilter = true;

                GridViewDataColumn col = e.Column as GridViewDataColumn;

                if (col != null)
                {
                    if (col.FilterDescriptor != null && value != col.FilterDescriptor.Value)
                    {
                        col.FilterDescriptor.Value = value;
                        Console.WriteLine("Filter1: " + col.FilterDescriptor);
                    }
                    else
                    {
                        col.FilterDescriptor = new Telerik.WinControls.Data.FilterDescriptor(col.FieldName, GridViewHelper.GetDefaultFilterOperator(col.DataType), e.Value);
                        Console.WriteLine("Filter2: " + col.FilterDescriptor);
                    }
                }

                _canFilter = false;
            }
        }

        private void RadGridView1_FilterChanging(object sender, GridViewCollectionChangingEventArgs e)
        {
            e.Cancel = !_canFilter;
        }
    }
}

[Dimitar]

Hi Anders,

I have sent you the wrong event (I reused the handler method). The correct event is ValueChanged.

Regards,
Dimitar
Telerik by Progress

[Skurken]

Hi again

I implmented the ValueChanged handler... but now it behaves really weird. When leaving/entering the filter textboxes it sometimes filters on the value, somtimes it does not. I have tried setting value = NULL after setting the filter, but that spawns a new problem.

Video here: https://www.screencast.com/t/3bzJ07E8

Example code here:

using System;
using System.Data;
using System.Windows.Forms;
using Telerik.WinControls.UI;

namespace TelerikGridTest
{
    public partial class Form1 : Form
    {
        private bool _canFilter;
        private object value;

        private Random randomGenerator = new Random();
        private const int ROWS_COUNT = 50000;
        private string[] randomStrings;
        private string[] randomStrings2;

        public Form1()
        {
            InitializeComponent();
            radGridView1.AutoSizeColumnsMode = GridViewAutoSizeColumnsMode.Fill;
            radGridView1.DataSource = GetTable();
            radGridView1.EnableFiltering = true;
            radGridView1.FilterChanging += RadGridView1_FilterChanging;
            radGridView1.CellEndEdit += RadGridView1_CellEndEdit;
            radGridView1.ValueChanged += RadGridView1_CellValueChanged;
        }

        private void RadGridView1_CellValueChanged(object sender, EventArgs e)
        {
            value = radGridView1.ActiveEditor.Value;
        }

        private DataTable GetTable()
        {
            randomStrings = new string[20];
            for (int i = 0; i < this.randomStrings.Length; i++)
            {
                randomStrings[i] = "String " + (i + 1);
            }

            randomStrings2 = new string[20];
            for (int i = 0; i < this.randomStrings2.Length; i++)
            {
                randomStrings2[i] = "String2 " + (i + 1);
            }

            DataColumn stringColumn = new DataColumn("StringColumn", typeof(string));
            DataColumn stringColumn2 = new DataColumn("StringColumn2", typeof(string));

            var dataTable = new DataTable();
            dataTable.Columns.Add(stringColumn);
            dataTable.Columns.Add(stringColumn2);

            dataTable.BeginLoadData();
            for (int i = 0; i < ROWS_COUNT; i++)
            {
                DataRow row = dataTable.NewRow();
                row["StringColumn"] = randomStrings[randomGenerator.Next(0, this.randomStrings.Length - 1)];
                row["StringColumn2"] = randomStrings2[randomGenerator.Next(0, this.randomStrings2.Length - 1)];
                dataTable.Rows.Add(row);
            }
            dataTable.EndLoadData();

            return dataTable;
        }

        private void RadGridView1_CellEndEdit(object sender, GridViewCellEventArgs e)
        {
            if (radGridView1.CurrentCell is GridFilterCellElement)
            {
                _canFilter = true;

                GridViewDataColumn col = e.Column as GridViewDataColumn;

                if (col != null)
                {
                    if (col.FilterDescriptor != null && value != col.FilterDescriptor.Value)
                    {
                        col.FilterDescriptor.Value = value;
                    }
                    else
                    {
                        col.FilterDescriptor = new Telerik.WinControls.Data.FilterDescriptor(col.FieldName, GridViewHelper.GetDefaultFilterOperator(col.DataType), e.Value);
                    }
                }
                _canFilter = false;
            }
        }

        private void RadGridView1_FilterChanging(object sender, GridViewCollectionChangingEventArgs e)
        {
            e.Cancel = !_canFilter;
        }
    }
}

[Dimitar]

Hi Anders,

You can save the value in the cell's Tag property instead of using the global variable:

private void RadGridView1_ValueChanged(object sender, EventArgs e)
{

    radGridView1.CurrentCell.Tag = radGridView1.ActiveEditor.Value;
}

Then you can get it like this:

var val = radGridView1.CurrentCell.Tag;
if (col.FilterDescriptor != null && val != col.FilterDescriptor.Value)
{
    col.FilterDescriptor.Value = val;
}

I want to suggest a bit different solution as well. I have attached a small example that shows how you can create a custom filter cell with a text box added to it. This will allow you to completely bypass the filtering functionality. 

I hope this will be useful. 

Regards,
Dimitar
Telerik by Progress