ASP.NET MVC Webgrid Efficient Paging

Question

I have a ASP.NET MVC 4 project and a SQL View (vvItem). ItemController

    MVCAppEntities db = new MVCAppEntities();
    public ActionResult Index()
    {
        var itemqry = db.vvItem.OrderBy(s => s.name);
        //var pageditems = itemqry.Skip(10).Take(20); // 25 seconds
        return View(itemqry.ToList()); // 88 seconds
    }

Index.cshtml View

@model IEnumerable<MVCApplication1.Models.vvItem>
@{
    var norows = 20;
    var grid = new WebGrid(Model, canPage: true, rowsPerPage: norows);
    grid.Pager(WebGridPagerModes.NextPrevious);  
    @grid.GetHtml(tableStyle: "table",  headerStyle: "header", columns: grid.Columns(
          grid.Column(columnName: "name", header: "Name", canSort: true),
          grid.Column(columnName: "quantity", header: "Quantity", canSort: true),  
          grid.Column(columnName: "code", header: "Code", canSort: true),
          grid.Column(columnName: "Price", header: "Price", canSort: true),
          ))}

In vvItem I have almost 400000 records. I thought that the webgrid Pager would load (Take()) only the displayed records and it would know to Skip() the first records if I would go to the next pages.

Q : How can I efficiently make a view to load only the displayed records ?

I found 2 solutions : JSON version and NerdDinner

I'm not so good at JSON so I tried the NerdDinner solution. And as in my commented line //var pageditems = itemqry.Skip(10).Take(20); itemqry is already loaded with all the records and it took a lot of time to load.

Q2 : How can I do paging now ? I need to modify the page no. from the Index method.

public ActionResult Index(int? page, string filter1 = " ", string filter2 = " ")

in the nerddinner example they would materialize the x in the view not itemqry. — Mike Miller, Jun 19 '12 at 12:43
I renamed x to pageditems. As you can see with Skip-Take it took 25 sec and without 88 sec — Misi, Jun 19 '12 at 14:10
For question 2 it would be managed in the controller as in the NerdDinner example and as user1439338 posted below. Your view would have to count the number of pages and manage the page links. A dedicated View Model is a good way as is shown at the bottom of the NerdDinner link. — Turnkey, Jun 20 '12 at 17:22

score 2 · Accepted Answer · edited Mar 08 '13 at 03:17

2

I made a SQL Stored Procedure

CREATE PROCEDURE SkipTake 
    @pagNo int, 
    @pageSize int
AS    
    select *
    from (select *, row_number() over (order by COD) as rn 
          from vvSTOC
         ) as T
    where T.rn between (@pagNo - 1) * @pageSize + 1 and @pagNo * @pageSize

I've added this sp in my EF model at Function Import so that it returns an entity (vvSTOC)

    public ActionResult Index(int? page)
    {
        const int pageSize = 20;
        return View(db.spSkipTake(page, pageSize).ToList());
    }

edited Mar 08 '13 at 03:17

Mitch Wheat

295,962
43
465
541

answered Jun 19 '12 at 15:57

Misi

748
5
21
46

This is the SP version. If you have other solutions please fill me in. – Misi Jun 19 '12 at 15:58
How is the db object defined? – Turnkey Jun 19 '12 at 16:04
I have a SQL table and a SQL view(the view displays rows from this table plus some unimportant data(~ 1000 records) from another table) with ~ 400.000 records. Do you want to see the SQL script for the view and from the table ? – Misi Jun 19 '12 at 17:22
No you answered my question above and I think I understand it now. – Turnkey Jun 19 '12 at 20:59
@Misi Can you explain how this was displayed in the view / How to display paged results in view (when we are doing it the SP way)?? – who-aditya-nawandar Sep 02 '13 at 12:19

Turnkey · Answer 2 · 2012-06-19T20:58:22.460

1

It shouldn't execute the query right away as long as itemqry is an IEnumerable or IQueryable type. Can you cast it as an IQueryable as follows?

public ActionResult Index()
{
    IQueryable<vvItem> itemQry = db.vvItem;
    return View(itemQry.OrderBy(s => s.name).Skip(10).Take(20).ToList());
}

If the itemqry is the correct type it won't get executed until the .ToList() is called which converts it to an IList type. Check the SQL that is being produced to be sure.

edited Jun 19 '12 at 20:58

answered Jun 19 '12 at 12:54

Turnkey

9,266
3
27
36

You have written what I have in my commented line from my return. I clocked without Skip() and Take() at 1 minute and a half and with Skip() and Take() at 25 seconds. In SQL, from the same table, if I write TOP 1000 it will return 1000 rows in 1 second – Misi Jun 19 '12 at 13:52
What sort of variable is itemqry if you hover over it in Visual Studio? – Turnkey Jun 19 '12 at 15:30
{System.Data.Objects.ObjectQuery} – Misi Jun 19 '12 at 17:20
I'm not as familiar with ObjectQuery but it sounds like it's not playing nicely with deferred execution. Which is surprising since it implements IQueryable interface. I'll update the answer with another suggestion. – Turnkey Jun 19 '12 at 20:54

score 0 · Answer 3 · edited Jun 19 '12 at 18:14

0

public ActionResult Index(int? pageIndex)
{
   int pageSize = 20;
   var itemIndex = ((pageIndex??1) -1) * pageSize;

    return View(db.vvItem.OrderBy(s => s.name).Skip(itemIndex).Take(pageSize).ToList()); 
}

edited Jun 19 '12 at 18:14

Jon Lin

142,182
29
220
220

answered Jun 19 '12 at 18:12

user1439338

130
7

score 0 · Answer 4 · answered Jul 13 '12 at 14:02

Thanks for this.

I tweaked this to work with some input parameters in my SP to control the PageSize, PageNumber, SortIndex and SortOrder:

declare @sql nvarchar(1000) = '
select * from
(
    select *, row_number() over(order by '+@SortIndex+' '+@SortOrder+') as RowNumber from #Results
) as T
where T.RowNumber between ('+@PageNumber+' - 1) * '+@PageSize+' + 1 and '+@PageNumber+' * '+@PageSize

exec sp_executesql @sql

ASP.NET MVC Webgrid Efficient Paging

4 Answers4

Linked