Column filter dropdown-list gets slow

[Heiko]

Hi!

When clicking the filter button on a column the time before the filter list appears gets longer and longer. The more records the table on which the grid is based has, the longer it takes until the list is shown.

First, I use a QueryableCollectionView as the ItemsSource of the grid. All filtering and sorting is done on this qcv first, then on the UI of the grid itself. So the base query of the qcv means: load all records of table x.
I use my own logic for "DistinctValuesLoading" since I use some enums as filter but mainly I call "GetDistinctValues(e.Column, true, null)" which normally worked fine. I found the place where the delay happens but have no idea how to fix it.
When calling "GetDistinctValues" on the grid, the QueryableSourceCollection is passed to a number of functions and finally a function in "/Core/Data/Collections/EnumerableExtensions.cs" is called which is named "Any(...)". This function finally does a "foreach (...)" on the QueryableSourceCollection which means that ALL data is loaded - this is how the QueryableSourceCollection is working, I presume. Which in the end means the more records I have, the longer this function takes to finish.

I know this sounds complicated, but it is a real showstopper at the moment since my customer is waiting more than 10 seconds before the filter list comes up. Any idea how to fix it?

Regards
Heiko

[Stefan]

Hello Heiko,

Thank you for the detailed explanation of the behavior you are experiencing.

Generally speaking, when a null value is passed for the maximumValueCount argument of the GetDistinctValues method, all distinct values will be loaded. Thus, the more items you have populated in RadGridView, the slower the distinct values list will be loaded. What I can suggest you for this scenario is to try to limit the number of the distinct values displayed by the column by setting a fixed value for the maximumValueCount argument. Have you considered using such an approach? If yes, what is the obstacle to adopting it?

I will be looking forward to your reply.

Regards,
Stefan X1
Telerik by Progress

[Heiko]

Hello Stefan!

I thought this answer would come. No, it is NOT the function that is fetching the distinct values itself, Any(...) is called before! Just take a look into "GridViewColumn.Filtering.cs", function: "private IQueryable GetDistinctValuesQuery(IQueryable source, bool filter, int? maximumValueCount)". Second line is:

if (filter && source.IsNotEmpty())
{
    result = this.FilterDistinctValues(result);
}

You see that "source.IsNotEmpty()" is called before the distinct values are fetched. IsNotEmpty() is a function in "EnumerableExtensions.cs" witch itself is calling the following internal function:

internal static bool Any(this IEnumerable source, Func<object, bool> predicate)
{
    if (source == null)
    {
        throw new ArgumentNullException("source");
    }

    if (predicate == null)
    {
        throw new ArgumentNullException("predicate");
    }

    foreach (var item in source)
    {
        if (predicate(item))
        {
            return true;
        }
    }
    return false;
}

This is where you see the foreach... loop which is fetching ALL records of the source collection, that means: all records without any filter condition. Predicate is only lambda "item => true".

In my opinion this is a bad design and it makes it impossible to use when you have a large database.

Regards
Heiko

[Stefan]

Hello Heiko,

Thank you for for the detailed information and your effort to research the case.

The IsNotEmpty check of the source collection within the GetDistinctValuesQuery method is removed as of R1 2017. May I kindly ask you to test your project with this assemblies version or the latest official ones(R1 2017 SP1)?

Best Regards,
Stefan X1
Telerik by Progress

[Heiko]

Hi Stefan!

Good news - yes, now it is working.

Thank you for your help.
Regards

Heiko

[Stefan]

Hello Heiko,

I am glad that the problem is solved by updating the assemblies version.

In case any other assistance with our components is needed, feel free to contact us again.

All the best,
Stefan X1
Telerik by Progress

[Heiko]

I did already ;-)