Retrieve only rows needed for the current page

[Derek]

Hi 

 I have this grid. It retrieves 3000+ rows every time opened. It only shows 1st 20 rows. Is there a way to retrieve rows as needed?

Say retrieve 1st 20 rows and then next 20 if user clicks on page 2?

@(Html.Kendo().Grid<PartnerLink.Models.CaseMainGridDisplay>
    ()
    .Name("grid")
    .Columns(columns =>
    {
    columns.Bound(c => c.ASSIGNMENT_ID).Hidden(true);
    columns.Bound(c => c.CASE_REFERENCE).Title("Case Ref").Width(105);
    columns.Bound(c => c.FIRST_NAME).Title("First Name");
    columns.Bound(c => c.SURNAME).Title("Last Name");
    columns.Bound(c => c.status).Title("Status");
    columns.Bound(c => c.DaysAtStatus).Title("Days At Status").Width(135);
    columns.Bound(c => c.CallAgent).Title("Call Agent");
    columns.Bound(c => c.CustomerAdvisor).Title("Customer Advisor");
    columns.Bound(c => c.DATE_CREATED).Title("Created On").Format("{0:dd/MM/yyyy}");
    })
    .Events(evt => evt.Change("onChange").DataBound("onDataBound"))
    .Scrollable()
    .Groupable()
    .ToolBar(tools =>
    {
        if (isExcel)
        {
            tools.Excel();
        }
    })
    .Excel(excel => excel
    .AllPages(true)
    .FileName("CasesList.xlsx")
    .Filterable(false)
    .ProxyURL(Url.Action("Export_Excel", "Home"))
    )
    .Sortable()
    .Resizable(resize => resize.Columns(true))
    .Filterable(ftb => ftb.Mode(GridFilterMode.Menu))
    .Selectable(selectable => selectable
    .Mode(GridSelectionMode.Single)
    .Type(GridSelectionType.Row))
    .Pageable(pageable => pageable
    .Refresh(true)
    .PageSizes(true)
    .ButtonCount(5))
    .DataSource(dataSource => dataSource
    .Ajax()
    .Sort(sort => sort.Add("ASSIGNMENT_ID").Descending())
    .Read(read => read.Action("Assignments_Read", "Home").Data("filterParams")))
);

[Venelin]

Hi Derek,

You can retrieve as many records as you need by using the .PageSize method:

.DataSource(dataSource => dataSource
    .Ajax()
    .Read(read => read.Action("Orders_Read", "Grid"))
    .PageSize(20)
)

Regards,
Venelin
Telerik

[Derek]

Hi

Thank you for the reply!

 PageSize(20) only shows 20 rows in a page but the page is still retrieving all 3000 rows to the client or at least it looks as it is.

My question is how to improve the speed of the grid? If we have 1 row its instant if we have 3000 it takes up to 2 minutes to load.

If we filter it to show some rows based on criteria it is very fast also.

Is there an option to retrieve only the rows for the page that is showing. Say we have 20 rows per page and we click on page 5 can we only retrieve rows from 80-100? We still need to keep our filters working..

Maybe the slow bit is .ToDataSourceResult(request) .. 

Here is the whole view:

@using PartnerLink.Controllers
@{
    Layout = "~/Views/Shared/_mainLoggedIn.cshtml";
    ViewBag.Title = "Home";
    bool isExcel=false;
    bool isReports=false;
    bool isAdmin = false;
    bool isSysAdmin = false;
    if (ViewBag.isExcelRole != null)
    {
        isExcel = (bool)ViewBag.isExcelRole;
    }

    if (ViewBag.isReportsRole != null)
    {
        isReports = (bool)ViewBag.isReportsRole;
    }

    isAdmin = ViewBag.isSysAdmin == true || ViewBag.isAdministratorRole == true;
    isSysAdmin = ViewBag.isSysAdmin == true;
}



<div class="buttons-wrapper">
    <img class="button add-case" data-link="/" src="@Url.Content("~/Content/Images/Index/Cases.png")" />
    <img class="button add-case" data-link="/AddCase" src="@Url.Content("~/Content/Images/Index/Add Case.png")" />
    @if (isReports)
    {
        <img class="button my-reports" data-link="/MyReports" src="@Url.Content("~/Content/Images/Index/My Reports.png")" />
    }
    <img class="button workflow" data-link="/Workflow" src="@Url.Content("~/Content/Images/Index/Work Flow.png")" />
    @if (isAdmin)
    {
        <img class="button" data-link="/Administration" src="@Url.Content("~/Content/Images/Index/Admin.png")" />
    }
</div>

<div id="grid-wrapper">
    @Html.Kendo().CheckBox().Name("myCases").Label("My Cases").Checked(!isSysAdmin).HtmlAttributes(new { @onclick = "filterIndexCases()" })
    @Html.Kendo().CheckBox().Name("allLive").Label("All Live").Checked(isSysAdmin).HtmlAttributes(new { @onclick = "filterIndexCases()" })
    @Html.Kendo().CheckBox().Name("includeCancelled").Label("Include Cancelled").HtmlAttributes(new { @onclick = "filterIndexCases()" })
    @Html.Kendo().CheckBox().Name("includeReferred").Label("Include Referred").HtmlAttributes(new { @onclick = "filterIndexCases()" })
    @Html.Kendo().TextBox().Name("searchReference").HtmlAttributes(new { @placeholder = "Ref, Surname, Tel, PO" })
    @Html.Kendo().Button().Name("buttonSearch").Content("Search").HtmlAttributes(new { @onclick = "caseSearch()" })
    @Html.Kendo().Button().Name("buttonClear").Content("Clear").HtmlAttributes(new { @onclick = "clearSearch()" })
    @(Html.Kendo().Grid<PartnerLink.Models.CaseMainGridDisplay>
        ()
        .Name("grid")
        .Columns(columns =>
        {
        columns.Bound(c => c.ASSIGNMENT_ID).Hidden(true);
        columns.Bound(c => c.CASE_REFERENCE).Title("Case Ref").Width(105);
        columns.Bound(c => c.FIRST_NAME).Title("First Name");
        columns.Bound(c => c.SURNAME).Title("Last Name");
        columns.Bound(c => c.status).Title("Status");
        columns.Bound(c => c.DaysAtStatus).Title("Days At Status").Width(135);
        columns.Bound(c => c.CallAgent).Title("Call Agent");
        columns.Bound(c => c.CustomerAdvisor).Title("Customer Advisor");
        columns.Bound(c => c.DATE_CREATED).Title("Created On").Format("{0:dd/MM/yyyy}");
        })
        .Events(evt => evt.Change("onChange").DataBound("onDataBound"))
        .Scrollable()
        .Groupable()
        .ToolBar(tools =>
        {
            if (isExcel)
            {
                tools.Excel();
            }
        })
        .Excel(excel => excel
        .AllPages(true)
        .FileName("CasesList.xlsx")
        .Filterable(false)
        .ProxyURL(Url.Action("Export_Excel", "Home"))
        )
        .Sortable()
        .Resizable(resize => resize.Columns(true))
        .Filterable(ftb => ftb.Mode(GridFilterMode.Menu))
        .Selectable(selectable => selectable
        .Mode(GridSelectionMode.Single)
        .Type(GridSelectionType.Row))
        .Pageable(pageable => pageable
        .Refresh(true)
        .PageSizes(true)
        .ButtonCount(5))
        .DataSource(dataSource => dataSource
        .Ajax()
        .Sort(sort => sort.Add("ASSIGNMENT_ID").Descending())
        .Read(read => read.Action("Assignments_Read", "Home").Data("filterParams")))
    );
    </div>
        <script>
            function caseSearch() {
                $("#buttonClear").show();
                $("#grid").data().kendoGrid.dataSource.page(1);
            }
            function clearSearch() {
                $("#searchReference").val("");
                $("#buttonClear").hide();
                $("#grid").data().kendoGrid.dataSource.page(1);
            }
            function filterIndexCases() {
                $("#grid").data("kendoGrid").dataSource.read();
            }
            function filterParams() {
                return {
                    myCases: $("#myCases").is(":checked"),
                    allLive: $("#allLive").is(":checked"),
                    includeCancelled: $("#includeCancelled").is(":checked"),
                    includeReferred: $("#includeReferred").is(":checked"),
                    searchReference: $("#searchReference").val()
                }
            }
            function onChange() {
                $('.busyIndicator').show();

                var caseRef = $.map(this.select(), function (item) {
                    return $(item).find("td:first").text();
                });

                location.pathname = "/Case/" + caseRef;
            }

            $(window).ready(function () {
                $("#grid-wrapper").css("height", $(window).height() - 120 - $(".button").height() + "px");
                $(".k-checkbox-label[for='myCases']").attr("style", "top:285px; left:10%;")
                $(".k-checkbox-label[for='allLive']").attr("style", "top:285px; left:15%;")
                $(".k-checkbox-label[for='includeCancelled']").attr("style", "top:285px; left:20%;")
                $(".k-checkbox-label[for='includeReferred']").attr("style", "top:285px; left:25%;")
                $("#searchReference").attr("style", "top:285px; left: 30%;");
                $("#buttonSearch").attr("style", "position:relative; top:285px; left: 30%;");
                $("#buttonClear").attr("style", "position:relative; top:285px; left: 30%;");

                $(window).on("resize", function () {
                    $("#grid-wrapper").css("height", $(window).height() - 120 - $(".button").height() + "px");
                    $("#grid").data("kendoGrid").resize();
                });

                $(".button").click(function () {
                    $('.busyIndicator').show();
                    location.pathname = $(this).data("link");
                });

                $("#toggleGrid").click(function () {
                    if ($("#toggleGrid").data("collapsed") == true) {
                        $("#toggleGrid").text("Hide the list of Assignments");
                        $(".buttons-wrapper").animate({
                            "top": "47px",
                            "margin-top": 0,
                            "width": "1000px"
                        }, 200, null, function () {
                            $("#toggleGrid").data("collapsed", false);
                            $("#grid").data("kendoGrid").resize();
                        });
                        $("#grid-wrapper").fadeIn();
                    } else {
                        $("#toggleGrid").text("Show the list of Assignments");
                        $("#grid-wrapper").hide();
                        $(".buttons-wrapper").animate({
                            "top": "33%",
                            "margin-top": "-110px",
                            "width": "1000px"
                        }, 200, null, function () {
                            $("#toggleGrid").data("collapsed", true);
                            $("#grid").data("kendoGrid").resize();
                        });
                    }
                });
                setGridOptions();
                intializeGridToolbars();
                $("#buttonClear").hide();
            });

            function onDataBound(arg) {
                var grid = $("#grid").data("kendoGrid");
                localStorage["cases-filters"] = kendo.stringify(grid.getOptions());
            }

            function setGridOptions() {
                var options = localStorage["cases-filters"];

                if (options) {
                    $("#grid").data("kendoGrid").setOptions(JSON.parse(options));
                }
            }

            function intializeGridToolbars() {
                var html = '<button class="k-button k-button-icontext k-grid-excel"><span class="k-icon k-i-excel"></span>Export to Excel</button>';
                $("#grid .k-grid-toolbar").html(html);
            }

            $(".buttons-wrapper").animate({
                "top": "47px",
                "margin-top": 0,
                "width": "1100px"
            }, 200, null, function () {
                $("#toggleGrid").data("collapsed", false);
                $("#grid").data("kendoGrid").resize();
            });
            $("#grid-wrapper").fadeIn();

        </script>
        <style>
            .k-grid .k-grid-header .k-header .k-link {
                height: auto;
            }

            .k-grid .k-grid-header .k-header {
                white-space: normal;
                vertical-align: top;
            }

            .busyIndicator {
                display: none;
                left: 0;
                top: 0;
                right: 0;
                bottom: 0;
                background: rgba(0, 0, 0, 0.6);
                position: fixed;
                z-index: 9999;
            }

                .busyIndicator p {
                    left: 50%;
                    top: 50%;
                    margin-left: -50px;
                    margin-top: -10px;
                    color: white;
                    position: absolute;
                }
        </style>

        <div class="busyIndicator">
            <p>
                Loading. Please wait...
            </p>
        </div>

[Venelin]

Hi Derek,

By default the DataSource retrieve 10 records if PageSize is not set. This means that you receive 10 records on the client and the grid shows 10 records. The performance issue might be caused by something else. You can easily check how many records really come from the data source by inspecting the response via your browser web developer tool as shown on this short screen cast I captured: http://screencast.com/t/RHTO7xbwgv or via Fiddler.

Regards,
Venelin
Telerik

[Archieval]

Hey @gsharp did you solve your problem? i have here the same scenario where i want to read rows needed per page, thanks

[Archieval]

Hey @Derek's  did you solve your problem? i have here the same scenario where i want to read rows needed per page, thanks

[Alex Hajigeorgieva]

Hi, Archieval,

The dataSource configuration provided by Derek was missing the PageSize() method, once you add it, the grid will request the desired amount and only those number of records will be returned when using the ToDataSourceResult() extension method in the controller:

.DataSource(dataSource => dataSource
    .Ajax()
    .Read(read => read.Action("Orders_Read", "Grid"))
    .PageSize(20)
)

Regards,
Alex Hajigeorgieva
Progress Telerik

[anubha]

hi ,

i have a kendo grid which is inside a popup. In that popup , the grid shows 10 records per page. When i am trying to save the records its saving only 10 records and not the records of the entire grid.

 

any lead is appreciated