[Solved] Kendo ui grid: filterable mode row works slow

[Oscar]

I have created a grid with more than 300K records and i'm using filter mode row. My problem is that i'm taking a lot of time to get the suggestions for my filters(around 40s). As i understand, from your documentation, when i use mode row a new request is sent to server to retrieve again all data.There is an option to make filter works fast? How can i achieve that? 

 

@section Scripts{
    <script>

        function numberWithSpaces(x) {
            return x.toString().replace(/\B(?=(\d{3})+(?!\d))/g, " ");
        }



        function filterAutoCompleteDataSource(e) {
            var gridFilter = e.sender.dataSource.filter();
            e.sender.element.find(".k-autocomplete input").data("kendoAutoComplete").dataSource.filter(gridFilter);
        }

        $(document).ready(function () {
            $('.datapicker').datetimepicker(
                {
                    format: 'DD.MM.YYYY',
                    locale: 'ru'
                });

            var _grid = null;
            function grid_init() {
                var dataSearch = {
                    organizationSearchKey: document.getElementById('organizationSearchKey').value,
                    dateStartSearchKey: document.getElementById('dateStartSearchKey').value,
                    dateEndSearchKey: document.getElementById('dateEndSearchKey').value,
                    accountDebitSearchCode: document.getElementById('accountDebitSearchCode').value,
                    accountCreditSearchCode: document.getElementById('accountCreditSearchCode').value,
                    costArticleSearchCode: document.getElementById('costArticleSearchCode').value
                }
                if (!dataSearch.organizationSearchKey == "") {
                    var dataSource = new kendo.data.DataSource({
                        transport: {
                            read: {
                                url: "/AccountingTransactions?handler=Json",
                                dataType: "json",
                                data: dataSearch

                            }
                        },
                        schema: {
                            data: "results",
                            total: "total",
                            model: {
                            id: "accountingTransactionKey",
                            fields: {
                                accountingTransactionKey: { editable: false, nullable: false },
                                date: { editable: false, nullable: false },
                                organization: { editable: false, nullable: false },
                                accountDebit: { editable: false, nullable: true },
                                costArticleUsed: { editable: false, nullable: true },
                                accountCredit: { editable: false, nullable: true },
                                isIntraGroupPartnerOrganization: { editable: false, nullable: true },
                                currency: { editable: false, nullable: true },
                                sum: { editable: false, nullable: true },
                                rateRUB: { editable: false, nullable: true },
                                sumRUB: { editable: false, nullable: true },
                                rateUSD: { editable: false, nullable: true },
                                sumUSD: { editable: false, nullable: true },
                                content: { editable: false, nullable: true },
                                costArticle: { editable: false, nullable: true },
                                profitLossArticle: { editable: false, nullable: true },
                                construction: { editable: false, nullable: true },
                                nomenklature: { editable: false, nullable: true },
                                nomenklatureGroup: { editable: false, nullable: true },
                                basicResource: { editable: false, nullable: true },
                                nonMaterialAsset: { editable: false, nullable: true },
                                partnerDebit: { editable: false, nullable: true },
                                partnerCredit: { editable: false, nullable: true },
                                contractDebit: { editable: false, nullable: true },
                                contractCredit: { editable: false, nullable: true },
                                employee: { editable: false, nullable: true },
                                bankAccount: { editable: false, nullable: true },
                                organizationalUnit: { editable: false, nullable: true },
                                stock: { editable: false, nullable: true },
                                futureLossArticle: { editable: false, nullable: true },
                                futureProfitArticle: { editable: false, nullable: true },
                                profitLossArticleErpIdName: { defaultValue: { id: -1, name: "" } },
                                investProfitLossArticleErpIdName: { defaultValue: { id: -1, name: "" } },
                                nomenklatureErpIdName: { defaultValue: { id: -1, name: "" } },
                                projectErpIdName: { defaultValue: { id: -1, name: "" } }
                            }
                           }

                        },
                        pageSize: 100,
                        page: 1,
                        serverPaging: false,
                        aggregate: [
                            { field: "sum", aggregate: "sum" },
                            { field: "sumRUB", aggregate: "sum" },
                            { field: "sumUSD", aggregate: "sum" }
                        ],
                        /*group: {
                            field: "sum", aggregates: [
                                {field: "date", aggregate: "sum"}]
                        }*/
                    });


                    $('#grid').empty();

                    $("#grid").kendoGrid({
                        dataSource: dataSource,

                        height: 700,
                        sortable: true,
                        pageable: {
                            buttonCount: 5
                        },
                        dataBound: filterAutoCompleteDataSource,
                        filterable: {
                            mode: "row",
                        },

                        columns: [
                            {
                                field: "date",
                                title: "Дата",
                                template: "#= kendo.toString(kendo.parseDate(date), 'dd-MM-yyyy') #",
                                width: "80px",
                                locked: true,
                                filterable: false,
                            },
                            {
                                field: "organization",
                                title: "Организация",
                                width: "200px",
                                locked: true,
                                filterable: false,

                            },
                            {
                                field: "accountDebit",
                                title: "Дт",
                                width: "60px",
                                locked: true,
                                filterable: false,
                            },
                            {
                                field: "accountCredit",
                                title: "Кт",
                                width: "60px",
                                locked: true,
                                filterable: false,
                            },

                            {
                                field: "isIntraGroupPartnerOrganization",
                                title: "ВГО",
                                width: "50px",
                                template: "#if(isIntraGroupPartnerOrganization == 1) {# #: 'СБЕ' # #} #",
                                locked: true,
                                filterable: false,
                            },
                            {
                                field: "currency",
                                title: "Вал.",
                                width: "50px",
                                locked: true,
                                filterable: false,
                            },
                            {
                                field: "sum",
                                title: "Сумма",
                                width: "120px",
                                //format: "{0:n2}",
                                locked: true,
                                filterable: false,
                                template: function (dataItem) { return numberWithSpaces(dataItem.sum.toFixed(2)) },
                                footerTemplate: "<b>" +"#: numberWithSpaces(sum.toFixed(2)) #"+"</b>"
                            },
                            {
                                field: "rateRUB",
                                title: "Курс RUB",
                                width: "100px",
                                locked: true,
                                filterable: false,
                                hidden: true,
                            },
                            {
                                field: "sumRUB",
                                title: "Сумма RUB",
                                width: "120px",
                                //format: "{0:n2}",
                                locked: true,
                                filterable: false,
                                template: function (dataItem) { return numberWithSpaces(dataItem.sumRUB.toFixed(2)) },
                                footerTemplate: "<b>" + "#: numberWithSpaces(sum.toFixed(2)) #" + "</b>",
                                hidden: true,
                            },
                            {
                                field: "rateUSD",
                                title: "Курс USD",
                                width: "100px",
                                locked: true,
                                filterable: false,
                                hidden: true,
                            },
                            {
                                field: "sumUSD",
                                title: "Сумма USD",
                                width: "120px",
                                //format: "{0:n2}",
                                locked: true,
                                filterable: false,
                                template: function (dataItem) { return numberWithSpaces(dataItem.sumUSD.toFixed(2)) },
                                footerTemplate: "<b>" + "#: numberWithSpaces(sum.toFixed(2)) #" + "</b>",
                                hidden: true,
                            },
                            {
                                field: "content",
                                title: "Содержание",
                                width: "200px",
                                filterable:
                                {
                                    cell:
                                    {
                                        operator: "contains",
                                        suggestionOperator: "contains",
                                        showOperators: false
                                    }
                                },
                                locked: true

                            },
                            {
                                title:"Аналитика бухгалтерского плана счетов",
                                columns: [
                                    {
                                        field: "costArticle",
                                        title: "Статья затрат",
                                        width: "200px",
                                        filterable:
                                        {
                                            cell:
                                            {
                                                operator: "contains",
                                                suggestionOperator: "contains",
                                                showOperators: false
                                            }
                                        }
                                    },
                                    {
                                        field: "profitLossArticle",
                                        title: "Прочие доходы и расходы",
                                        width: "200px",
                                        filterable:
                                        {
                                            cell:
                                            {
                                                operator: "contains",
                                                suggestionOperator: "contains",
                                                showOperators: false
                                            }
                                        }
                                    },
                                    {
                                        field: "construction",
                                        title: "Объекты строительства",
                                        width: "200px",
                                        filterable:
                                        {
                                            cell:
                                            {
                                                operator: "contains",
                                                suggestionOperator: "contains",
                                                showOperators: false
                                            }
                                        }
                                    },
                                    {
                                        field: "nomenklature",
                                        title: "Номенклатура",
                                        width: "200px",
                                        filterable:
                                        {
                                            cell:
                                            {
                                                operator: "contains",
                                                suggestionOperator: "contains",
                                                showOperators: false
                                            }
                                        }
                                    },
                                    {
                                        field: "nomenklatureGroup",
                                        title: "Номенклатурные группы",
                                        width: "200px",
                                        filterable:
                                        {
                                            cell:
                                            {
                                                operator: "contains",
                                                suggestionOperator: "contains",
                                                showOperators: false
                                            }
                                        }
                                    },
                                    {
                                        field: "basicResource",
                                        title: "Основные средства",
                                        width: "200px",
                                        filterable:
                                        {
                                            cell:
                                            {
                                                operator: "contains",
                                                suggestionOperator: "contains",
                                                showOperators: false
                                            }
                                        }
                                    },
                                    {
                                        field: "nonMaterialAsset",
                                        title: "Нематериальные активы",
                                        width: "200px",
                                        filterable:
                                        {
                                            cell:
                                            {
                                                operator: "contains",
                                                suggestionOperator: "contains",
                                                showOperators: false
                                            }
                                        }
                                    },

                                    {
                                        field: "partnerDebit",
                                        title: "Контрагент Дт",
                                        width: "200px",
                                        filterable:
                                        {
                                            cell:
                                            {
                                                operator: "contains",
                                                suggestionOperator: "contains",
                                                showOperators: false
                                            }
                                        }
                                    },
                                    {
                                        field: "contractDebit",
                                        title: "Договор Дт",
                                        width: "200px",
                                        filterable:
                                            {
                                                cell:
                                                    {
                                                        operator: "contains",
                                                        suggestionOperator: "contains",
                                                        showOperators: false
                                                    }
                                            }
                                    },
                                    {
                                        field: "partnerCredit",
                                        title: "Контрагент Кт",
                                        width: "200px",
                                        filterable:
                                            {
                                                cell:
                                                    {
                                                        operator: "contains",
                                                        suggestionOperator: "contains",
                                                        showOperators: false
                                                    }
                                            }
                                    },
                                    {
                                        field: "contractCredit",
                                        title: "Договор Кт",
                                        width: "200px",
                                        filterable:
                                            {
                                                cell:
                                                    {
                                                        operator: "contains",
                                                        suggestionOperator: "contains",
                                                        showOperators: false
                                                    }
                                            }
                                    },
                                    {
                                        field: "employee",
                                        title: "Сотрудник",
                                        width: "200px",
                                        filterable:
                                        {
                                            cell:
                                            {
                                                operator: "contains",
                                                suggestionOperator: "contains",
                                                showOperators: false
                                            }
                                        }
                                    },
                                    {
                                        field: "bankAccount",
                                        title: "Банковский счет",
                                        width: "200px",
                                        filterable:
                                        {
                                            cell:
                                            {
                                                operator: "contains",
                                                suggestionOperator: "contains",
                                                showOperators: false
                                            }
                                        }
                                    },
                                    {
                                        field: "organizationalUnit",
                                        title: "Подразделение",
                                        width: "200px",
                                        filterable:
                                        {
                                            cell:
                                            {
                                                operator: "contains",
                                                suggestionOperator: "contains",
                                                showOperators: false
                                            }
                                        }
                                    },
                                    {
                                        field: "stock",
                                        title: "Склад",
                                        width: "200px",
                                        filterable:
                                        {
                                            cell:
                                            {
                                                operator: "contains",
                                                suggestionOperator: "contains",
                                                showOperators: false
                                            }
                                        }
                                    },
                                    {
                                        field: "futureLossArticle",
                                        title: "Расходы будущих периодов",
                                        width: "200px",
                                        filterable:
                                        {
                                            cell:
                                            {
                                                operator: "contains",
                                                suggestionOperator: "contains",
                                                showOperators: false
                                            }
                                        }
                                    },
                                    {
                                        field: "futureProfitArticle",
                                        title: "Доходы будущих периодов",
                                        width: "200px",
                                        filterable:
                                        {
                                            cell:
                                            {
                                                operator: "contains",
                                                suggestionOperator: "contains",
                                                showOperators: false
                                            }
                                        }
                                    },
                                ]
                            },
                            {
                                title: "Аналитика управленческого плана счетов",
                                columns: [
                                    {

                                        field: "profitLossArticleErpIdName",
                                        title: "Статья БДР",
                                        width: "250px",
                                        editor: profitLossArticleErpDropDown,
                                        template: "#=profitLossArticleErpIdName.name#",
                                        filterable:
                                        {
                                            cell:
                                            {
                                                operator: "contains",
                                                suggestionOperator: "contains",
                                                showOperators: false
                                            }
                                        },
                                    },
                                    {
                                        field: "investProfitLossArticleErpIdName",
                                        title: "Статья ИБ",
                                        width: "250px",
                                        editor: investProfitLossArticleErpDropDown,
                                        template: "#=investProfitLossArticleErpIdName.name#",
                                        filterable:
                                        {
                                            cell:
                                            {
                                                operator: "contains",
                                                suggestionOperator: "contains",
                                                showOperators: false
                                            }
                                        },
                                    },
                                    {
                                        field: "nomenklatureErpIdName",
                                        title: "Номенклатура УУ",
                                        width: "250px",
                                        editor: nomenklatureErpDropDown,
                                        template: "#=nomenklatureErpIdName.name#",
                                        filterable:
                                        {
                                            cell:
                                            {
                                                operator: "contains",
                                                suggestionOperator: "contains",
                                                showOperators: false
                                            }
                                        }
                                    },
                                    {
                                        field: "projectErpIdName",
                                        title: "Проект",
                                        width: "200px",
                                        editor: projectErpDropDown,
                                        template: "#=projectErpIdName.name#",
                                        filterable: false,

                                    }
                                ]

                            }
                        ],
                        editable: true

                    });
                }
                else {
                    alert("Выберите организацию!");
                }

               var currencyShowed = false;
               $('.btn-action-show-hide-currency').on('click', function (e) {
                    var grid = $("#grid").data("kendoGrid");
                    if(!currencyShowed)
                    {
                        grid.showColumn(7);
                        grid.showColumn(8);
                        grid.showColumn(9);
                        grid.showColumn(10);
                        $(this).removeClass("btn-action-show-currency").addClass("btn-action-hide-currency").html("Скрыть другие валюты");
                        currencyShowed = true
                    }
                    else
                    {
                        grid.hideColumn(7);
                        grid.hideColumn(8);
                        grid.hideColumn(9);
                        grid.hideColumn(10);
                        $(this).removeClass("btn-action-hide-currency").addClass("btn-action-show-currency").html("Показать другие валюты");
                        currencyShowed = false
                    }
                });

            }

            function profitLossArticleErpDropDown(container, options) {
                    $('<input required name="' + options.field + '"/>')
                        .appendTo(container)
                        .kendoDropDownList({
                        autoBind: false,
                        filter: "contains",
                        dataTextField: "name",
                        dataValueField:"id",
                        select: profitLossArticleErpDropDownOnSelect,
                        dataSource: {
                            dataType: "json",
                            transport: {
                                read: "Common/GetProfitLossArticlesErp"
                            }
                        }
                    });
             }

            function investProfitLossArticleErpDropDown(container, options) {
                    $('<input required name="' + options.field + '"/>')
                        .appendTo(container)
                        .kendoDropDownList({
                        autoBind: false,
                        filter: "contains",
                        dataTextField: "name",
                        dataValueField:"id",
                        select: investProfitLossArticleErpDropDownOnSelect,
                        dataSource: {
                            dataType: "json",
                            transport: {
                                read: "Common/GetInvestProfitLossArticlesErp"
                            }
                        }
                    });
             }

            function nomenklatureErpDropDown(container, options) {
                    $('<input required name="' + options.field + '"/>')
                        .appendTo(container)
                        .kendoDropDownList({
                        autoBind: false,
                        filter: "contains",
                        dataTextField: "name",
                        dataValueField:"id",
                        select: nomenklatureErpDropDownOnSelect,
                        dataSource: {
                            dataType: "json",
                            transport: {
                                read: "Common/GetNomenklaturesErpGet"
                            }
                        }
                    });
             }

            function projectErpDropDown(container, options) {
                $('<input required name="' + options.field + '"/>')
                    .appendTo(container)
                    .kendoDropDownList({
                        autoBind: false,
                        filter: "contains",
                        dataTextField: "name",
                        dataValueField: "id",
                        select: projectErpDropDownOnSelect,
                        dataSource: {
                            dataType: "json",
                            transport: {
                                read: "Common/GetProjectsErpGet"
                            }
                        }
                    });
            }

            function profitLossArticleErpDropDownOnSelect(e)
            {
                var grid = e.sender.element.closest(".k-grid").data("kendoGrid");
                var row = e.sender.element.closest("tr");
                var dataItem = grid.dataItem(row);
                var data =
                {
                    accountingTransactionKey: dataItem.accountingTransactionKey,
                    profitLossArticleErpKey: e.dataItem.id
                }
                $.ajax({
                    url: "/FinanceSettings/UpdateFactProfitLossProfitLossArticleErpKey",
                    type: "POST",
                    data: data,
                    success: function (result) {
                        if (result = 'OK') {
                            console.log(result);
                        }
                    }

                });
            }

            function investProfitLossArticleErpDropDownOnSelect(e)
            {
                var grid = e.sender.element.closest(".k-grid").data("kendoGrid");
                var row = e.sender.element.closest("tr");
                var dataItem = grid.dataItem(row);
                var data =
                {
                    accountingTransactionKey: dataItem.accountingTransactionKey,
                    investProfitLossArticleErpKey: e.dataItem.id
                }
                $.ajax({
                    url: "/FinanceSettings/UpdateFactProfitLossInvestProfitLossArticleErpKey",
                    type: "POST",
                    data: data,
                    success: function (result) {
                        if (result = 'OK') {
                            console.log(result);
                        }
                    }

                });
            }

            function nomenklatureErpDropDownOnSelect(e)
            {
                var grid = e.sender.element.closest(".k-grid").data("kendoGrid");
                var row = e.sender.element.closest("tr");
                var dataItem = grid.dataItem(row);
                var data =
                {
                    accountingTransactionKey: dataItem.accountingTransactionKey,
                    nomenklatureErpKey: e.dataItem.id
                }
                $.ajax({
                    url: "/FinanceSettings/UpdateFactProfitLossNomenklatureErpKey",
                    type: "POST",
                    data: data,
                    success: function (result) {
                        if (result = 'OK') {
                            console.log(result);
                        }
                    }

                });
            }

            function projectErpDropDownOnSelect(e) {
                var grid = e.sender.element.closest(".k-grid").data("kendoGrid");
                var row = e.sender.element.closest("tr");
                var dataItem = grid.dataItem(row);
                var data =
                    {
                        accountingTransactionKey: dataItem.accountingTransactionKey,
                        projectErpKey: e.dataItem.id
                    }
                $.ajax({
                    url: "/FinanceSettings/UpdateFactProfitLossProjectErpKey",
                    type: "POST",
                    data: data,
                    success: function (result) {
                        if (result = 'OK') {
                            console.log(result);
                        }
                    }

                });
            }

            $("#btnSearch").click(function () {
                grid_init();
            });

        });

    </script>
}

[Stefan]

Hello, Oscar,

When the Grid has large data sets we recommend using server-side operations.

In this case, as the Grid has paging, sorting and filtering all of these operations has to be done on the server:

https://docs.telerik.com/kendo-ui/api/javascript/data/datasource/configuration/serverpaging

https://docs.telerik.com/kendo-ui/api/javascript/data/datasource/configuration/serverfiltering

https://docs.telerik.com/kendo-ui/api/javascript/data/datasource/configuration/serversorting

We do understand that these operations will involve additional code, but this is recommended with large data sets to ensure that the create DOM and the data requests will be more manageable.

Regards,
Stefan
Progress Telerik