Kendo ui grid: filterable mode row works slow

2 posts, 0 answers
  1. Oscar
    Oscar avatar
    3 posts
    Member since:
    Aug 2018

    Posted 15 Aug 2018 Link to this post

    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>
    }
  2. Stefan
    Admin
    Stefan avatar
    3008 posts

    Posted 16 Aug 2018 Link to this post

    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
    Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
Back to Top