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

Bliss

in the nerddinner example they would materialize the x in the view not itemqry. – Iorio

I renamed x to pageditems. As you can see with Skip-Take it took 25 sec and without 88 sec – Bliss

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. – Blear

Answer 1

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());
    }

Bliss

This is the SP version. If you have other solutions please fill me in. – Bliss

How is the db object defined? – Blear

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 ? – Bliss

No you answered my question above and I think I understand it now. – Blear

@Bliss 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)?? – Homotaxis

Answer 2

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.

Blear

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 – Bliss

What sort of variable is itemqry if you hover over it in Visual Studio? – Blear

{System.Data.Objects.ObjectQuery<Comenzi.Models.vvItem>} – Bliss

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. – Blear

Answer 3

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()); 
}

Copulation

Answer 4

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

Temperance