Kendo Grid Foreign Key Performance with 1000's of rows

[Matt]

I'm currently implementing a grid with in cell editing in which one of the columns is a forign key column bound to a select list with 1000+ entries.

Each time a user clicks on the forign key column there is a several second delay (during which I assume the dropdown is rendering) before the dropdown appears.

Once the dropdown is rendered it performs well, but the load on each click is causing the column to be almost unusable. 

I've tried overwriting the grid forign key editor template to use virtualization, and while that seems to work initially I do this I get an error with the following text "'ValueMapper is not provided while the value is being set." once a value is set on the grid. We're using 2018.1.117, and from what I read a ValueMapper isn't required, but I could be misinterpreting that. 

Do you have any suggestions around how we could get arround this issue?

[Georgi]

Hello Matt,

When virtualization is enabled the drop down needs a valueMapper in order to scroll to the selected item. You can find more detailed information in the following article:

• https://docs.telerik.com/kendo-ui/controls/editors/dropdownlist/virtualization#value-mapping

A sample can be found below demo:

• https://demos.telerik.com/kendo-ui/dropdownlist/virtualization

I hope this helps.

Regards,
Georgi
Progress Telerik

[Matt]

 

Hey Georgi, Thanks for your response! From that same page is says that: 

"As of the Kendo UI R3 2016 release, the implementation of the valueMapper function is optional. valueMapper is required only if the widget contains an initial value or if the value method is used" 

Does the grid call these methods when using an editor template?

[Georgi]

Hi Matt,

The grid itself does not use those methods. However, the Binder which syncs the values of the editor and the underlying model does use them. Thus, using valueMapper is necessary.

Regards,
Georgi
Progress Telerik

[Matt]

Thanks for your reply. I have a follow up question, I've added virtualization and set up a value mapper to the editor template in the grid using the following razor 

.Virtual(v => v.Enable(true).ValueMapper("mapLocationValues"))

 

My javascript function looks like the following (I realize I may not have implemented this correctly)

function mapLocationValues(options) {
    debugger;
    var value = options.value;
    var index = [_.indexOf(locationLookup, value)];
    options.success(index);
}

 

My issue here is that I click on the cell in the grid, I can see that it renders the editor though the value is blank, and while the value mapper function is called there are no values on the options object (see screenshots attatched), so I'm not really sure how to pick the correct item. 

I'd add that all of the available data items are present in the dropdown list (all local data), and when I don't implement this function I get the error I mentioned above about "ValueMapper is not provided while the value is being set." 

Thanks!

 

[Matt]

So it seems that when you first click on the cell it calls the mapLocationValues with an empty string as the value for values, however as long as you set options.success the function is called again immediately called after with the correct value.

This does cause me some issues however, because if I call options.success and pass in null, [], or -1 the first time the mapper is called the dropdown selects the first item in the list instead of deselecting any items. 

The second time the mapper is called the dropdown selects the correct item, but there is a moment where the incorrect value shows.  

I will add that you can only see this happen if there is a debugger point between the two calls of the method, otherwise you don't see the change. 

My code is below and screenshots are attached showing the first call, the dropdown rendering the first item in the grid, and the dropdown rendering the selected item in the grid: 

var locationIdArray = @Html.Raw(JsonConvert.SerializeObject(Model.AvailableLocations.Select(x => x.Value)));
function mapLocationValues(options) {
    debugger;
    var selectedLocationId = options.value;
    var mappedValue =  locationIdArray.indexOf(selectedLocationId.toString());
    if (mappedValue == -1) {
        options.success([]);
    } else {
        options.success(mappedValue);
    }

}

[Veselin Tsvetanov]

Hello Matt,

I would suggest you to carefully review the following Virtualization demo of the MVC DropDownList:

https://demos.telerik.com/aspnet-mvc/dropdownlist/virtualization

The ValueMapper function is defined like so:

    function valueMapper(options) {
        $.ajax({
            url: "@Url.Action("Orders_ValueMapper", "DropDownList")",
            data: convertValues(options.value),
            success: function (data) {
                options.success(data);
            }
        });
    }

In calls a remote endpoint which iterates all items that should be present in the DropDownList source:

        public ActionResult Orders_ValueMapper(int[] values)
        {
            var indices = new List<int>();

            if (values != null && values.Any())
            {
                var index = 0;

                foreach (var order in GetOrders())
                {
                    if (values.Contains(order.OrderID))
                    {
                        indices.Add(index);
                    }

                    index += 1;
                }
            }

            return Json(indices, JsonRequestBehavior.AllowGet);
        }

It also uses a helper function to convert the values to indices:

    function convertValues(value) {
        var data = {};

        value = $.isArray(value) ? value : [value];

        for (var idx = 0; idx < value.length; idx++) {
            data["values[" + idx + "]"] = value[idx];
        }

        return data;
    }

All the above is needed so that the virtualization of the DropDownList works properly when its initial value is sent. 

If you you face any issues with the above, I would like to ask you to prepare and send me a small isolated runnable sample reproducing the problem. Please, remove any external dependencies and database calls and use sample data created on the server. This way I will be able to troubleshoot the case locally.

As per the initial value of the DropDownList observed, in order to properly populate the widget, a mapper (and a remote) call is needed. Therefore, the change observed is expected.

Regards,
Veselin Tsvetanov
Progress Telerik

[Matt]

Hey Veselin, Thanks for the reply.

I have looked over that demo, but unfortunately our case we have all of the data in the model sent to the page as a list of select list items that are bound to the grid on page load.  We are not able to set up the dropdown list to make remote calls because the initial call to pull all of the locations is expensive, so we only want to do that once if possible. Otherwise every time the user clicks in a grid cell tied to that dropdown we'd make an ajax call that would pull the full list of items from the database and send down the requested page (what happens in the example). From looking at the forums there are several other cases where users are leveraging the rendering speed of the virtualized dropdown without resorting to remote data calls, so while this might not be supported officially it does seem to work.

I just want to clarify (sorry for not being clear) that the value mapper function and the dropdown is working correctly in our case and it is selecting the correct item, its just getting called twice when the grid initializes the editor template, once with no value, and once with the value stored in the grid data item. The unexpected result is that, at least according to the documentation here, if you "return null, [], or -1 and the widget will deselect the currently selected items". 

We're doing that in the above function but the dropdown (at least when paused in the debugger) seems to just select the first item in the list. Again this is a non issue because the function is immediately called with the right value and so if you aren't paused in the chrome debugger you don't actually see that happen. 

Unfortunately I don't have the time allocated to send an example with all dependancies pulled out for you to look at, I just wanted to let you know that this is what we've experienced in case some other user brings up this issue as well.

[Veselin Tsvetanov]

Hello Matt,

Yes, indeed, passing local data to the DropDownList could be achieved. The end result is client-side virtualization of the items in the drop-down.

As per the two mapper calls, that is also expected for the widget. Thank you also for bringing our attention to the case with the fist item being selected after the initial mapper call, even if an empty array is returned. I believe that we will be able to recreate the case locally based on your description and review whether that result is intended.

Many thanks for the time and the effort that you have put to explain the case.

Regards,
Veselin Tsvetanov
Progress Telerik

[Matt]

No problem, thanks for making such a great product and for taking the time to answer questions!