This is a migrated thread and some comments may be shown as answers.

Retrieve only rows needed for the current page

6 Answers 155 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Derek
Top achievements
Rank 1
Derek asked on 23 Jul 2015, 04:49 PM

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")))
);

6 Answers, 1 is accepted

Sort by
0
Venelin
Telerik team
answered on 24 Jul 2015, 08:56 AM
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
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
0
Derek
Top achievements
Rank 1
answered on 27 Jul 2015, 01:18 PM

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>

0
Venelin
Telerik team
answered on 28 Jul 2015, 06:44 AM
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
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
0
Archieval
Top achievements
Rank 1
Veteran
answered on 04 Oct 2020, 12:22 PM
Hey @gsharp did you solve your problem? i have here the same scenario where i want to read rows needed per page, thanks
0
Archieval
Top achievements
Rank 1
Veteran
answered on 04 Oct 2020, 12:22 PM
Hey @Derek's  did you solve your problem? i have here the same scenario where i want to read rows needed per page, thanks
0
Alex Hajigeorgieva
Telerik team
answered on 06 Oct 2020, 11:59 AM

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

Five days of Blazor, Angular, React, and Xamarin experts live-coding on twitch.tv/CodeItLive, special prizes, and more, for FREE?! Register now for DevReach 2.0(20).

Tags
Grid
Asked by
Derek
Top achievements
Rank 1
Answers by
Venelin
Telerik team
Derek
Top achievements
Rank 1
Archieval
Top achievements
Rank 1
Veteran
Alex Hajigeorgieva
Telerik team
Share this question
or