ASP.NET MVC3 -Performance Improvement through paging concept, I Need an example?

I am working on application built on ASP.NET MVC 3.0 and displaying the data in MVC WebGrid.

I am using LINQ to get the records from Entities to EntityViewModel. In doing this I have to convert the records from entity to EntityViewModel.

I have 30K records to be displayed in the grid, for each and every record there are 3 flags where It has to go 3 other tables and compare the existence of the record and paint with true or false and display the same in grid.

I am displaying 10 records at a time, but it is bit very slow as I am getting all the records and storing in my application.

The Paging is in place (I mean to say -only 10 records are being displayed in web grid) but all the records are getting loaded into the application which is taking 15-20 seconds. I have checked the place where this time is being spent by the processor. It's happening in the painting place(where every record is being compared with 3 other tables).

I have converted LINQ query to SQL and I can see my SQL query is getting executed under 2 seconds. By this , I can stro开发者_如何学JAVAngly say that, I do not want to spend time on SQL indexing as the speed of SQL query is good enough.

I have two options to implement 1) Caching for MVC 2) Paging(where I should get only first ten records).

I want to go with the paging technique for performance improvement .

Now my question is how do I pass the number 10(no of records to service method) so that It brings up only ten records. And also how do I get the next 10 records when clicking on the next page.

I would post the code, but I cannot do it as it has some sensitive data.

Any example how to tackle this situation, many thanks.

If you're using SQL 2005 + you could use ROW_NUMBER() in your stored procedure:

http://msdn.microsoft.com/en-us/library/ms186734(v=SQL.90).aspx

or else if you just want to do it in LINQ try the Skip() and Take() methods.

As simple as:

int page = 2;
int pageSize = 10; 

var pagedStuff = query.Skip((page - 1) * pageSize).Take(pageSize);

You should always, always, always be limiting the amount of rows you get from the database. Unbounded reads kill applications. 30k turns into 300k and then you are just destroying your sql server.

Jfar is on the right track with .Skip and .Take. The Linq2Sql engine (and most entity frameworks) will convert this to SQL that will return a limited result set. However, this doesn't preclude caching the results as well. I recommend doing that as well. That fastest trip to SQL Server is the one you don't have to take. :) I do something like this where my controller method handles paged or un-paged results and caches whatever comes back from SQL:

    [AcceptVerbs("GET")]
    [OutputCache(Duration = 360, VaryByParam = "*")]
    public ActionResult GetRecords(int? page, int? items)
    {
        int limit = items ?? defaultItemsPerPage;
        int pageNum = page ?? 0;
        if (pageNum <= 0) { pageNum = 1; }
        ViewBag.Paged = (page != null);

        var records = null;
        if (page != null)
        {
           records = myEntities.Skip((pageNum - 1) * limit).Take(limit).ToList();
        }
        else
        {
            records = myEntities.ToList();
        }
        return View("GetRecords", records);
    }

If you call it with no params, you get the entire results set (/GetRecords). Calling it will params will get you the restricted set (/GetRecords?page=3&items=25).

You could extend this method further by adding .Contains and .StartsWith functionality.

If you do decide to go the custom stored procedure route, I'd recommend using "TOP" and "ROW_NUMBER" to restrict results rather than a temp table.

Personally I would create a custom stored procedure to do this and then call it through Linq to SQL. e.g.

CREATE PROCEDURE [dbo].[SearchData]
(
@SearchStr NVARCHAR(50),
@Page int = 1,
@RecsPerPage int = 50,
@rc int OUTPUT
)
AS
SET NOCOUNT ON
SET FMTONLY OFF

DECLARE @TempFound TABLE
(
UID int IDENTITY NOT NULL,
PersonId UNIQUEIDENTIFIER
)

INSERT  INTO @TempFound
(
PersonId
)
SELECT PersonId FROM People WHERE Surname Like '%' + SearchStr + '%'

SET @rc = @@ROWCOUNT

-- Calculate the final offset for paging --
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)


-- Final select --
SELECT p.* FROM People p INNER JOIN @TempFound tf
ON p.PersonId = tf.PersonId
WHERE (tf.UID > @FirstRec) AND (tf.UID < @LastRec)

The @rc parameter is the total number of records found.

You obviously have to model it to your own table, but it should run extremely fast..

To bind it to an object in Linq to SQL, you just have to make sure that the final selects fields match the fields of the object it is to be bound to.