Paging on large amounts of data in MVC using custom logic

Question

0.00/5 (No votes)

See more:

Hi,
I want to implement Paging, Searching, Sorting on large amount of data (say DB has 25 to 30 Lakhs of records) in MVC. While using default paging option in MVC (using

PagedList.Mvc

,

@Html.PagedListPager

all records are fetched for DB & only 100 records are displayed on page. Thats Time Consuming & used more Memory.
How can I implement Custom Logic in MVC that only retrieves exact 100 records per page. Currently I am trying both approaches (using EF and SP).
Any Suggestion.

What I have tried:

I have already developed sample applications using EF & SP and implement default paging. But both approaches retrieves all records from DB & then apply paging on client side. Thats too much time consuming.

Posted 25-Aug-16 20:11pm

Umesh AP

Updated 26-Aug-16 4:09am

v2

Add a Solution

Comments

Maciej Los 26-Aug-16 7:38am

Check this: Sorting, Filtering, and Paging with the Entity Framework in an ASP.NET MVC Application | The ASP.NET Site[^]

Umesh AP 26-Aug-16 7:53am

@Maciej Los - As I already told I made sample applications using EF & SP with default paging but drawback of this is that every time I moved to next page whole set of data retrieves from DB & then paging is performed on client. I don't want whole data every time, but i want only 100 records to be fetched from DB for that page & when user changes page, only respective 100 records needs to retrieve from DB & displayed on view.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2016-08-26T02:42:00

Solution 1

Sorry, but you didn't provide information about method you use. So...

I'd suggest to read this:
Paging of Large Resultsets in ASP.NET[^].
Dynamic paging in Entity Framework[^]
Efficient paging using SQL script[^]

Posted 26-Aug-16 2:42am

Maciej Los

Comments

Umesh AP 26-Aug-16 10:05am

@Maciej - Thanks for ur support.

Maciej Los 28-Aug-16 7:03am

You're very welcome.

Umesh AP · Answer 2 · 2016-08-26T04:09:00

Using EF, previously my action method was -

C#

public ActionResult Index(int? page)
{
      return View(db.TestUploadData2.ToList().ToPagedList(page ?? 1, 100));            
}

But problem is that .ToList() pulls everything from DB into C# memory and then getting required 100 records which increases execution time & memory consumption.
Using following code solves my problem (using EF approach)

C#

public ActionResult Index(int? page)
{
      return View(db.TestUploadData2.OrderBy(x => x.SrNo).ToPagedList(page ?? 1, 100));
}

With SP approach I wrote SP which takes PageNo as parameter & returns total records from DB & 100 records for that page.
But here my problem is that how to display pager control in View.

Any suggestion for SP approach.....