Very poor and slow loading of large records from database

Question

Hello every one I am working on an web application using vs2015,MVC5 with E.F6 and LINQ query. When i have added 62,91,456 records in sql server database. When i Query through LINQ my application are performing very poor and slow taking too much time to load records. I have also added paging using skip() and take() LINQ functions. I am not understanding about SQL query means is it running at database side or at server side? Please help me. When i search any specific record using jquery autoload UI the errors comes server error 505 and code not work my whole system become hang and slow. Is there any solutions to make speed fast of my application?

Vincent Maverick Durano · Answer

@ Aijaz Ali Yes I would agree. Just always remember to debug your code first when you run into issues. It's the quickest way to figure things out and what went wrong :) Glad it works for you know.

Aijaz Ali · Answer

Thanks every one for your reply. I have solved the problem.actually the problem was in my LINQ query which was retrieving SELECT * instead of SELECT TOP(10) statement internally so all 6 millions records are selected at once :) when i remove tolist() method and use skip() and take() for custom paging in single LINQ IQueryable var type my application become fast.Now it can load more than 6 millions records very fast. tolist() method load all records and does not use SELECT TOP(10). I advice you don't use tolist() in single statement of your LINQ query. Use skip and take. You can use tolist() when you pass query to your Model. Always use IQueryable generic type because it is used for databases. If you need paging for large records use custom paging such as take() and skip() function. They translate into top(10) SQL query and your application performs very fast

Vincent Maverick Durano · Answer

I think the problem there is you're having an internal error 505. The server doesn't response back to your client because you are having an error at the server which causes your page to hang. You need to debug your code and figure out what is causing the error.

Aijaz Ali · Answer

My model DbContext class: public virtual DbSet address{ get ; set ;} My action method in controller class: private DbContextModeldb= new DbContextModel(); public ActionResultViewAddresses( string q int ?page int ?pno) { ViewBag.Full_address=q; page=(pno!= null )?pno-1:page; varpageSize=AppConfig.PageSize; varaddressesList=(fromaddresses in db.address where(addresses.full_address.StartsWith(q.Trim())|| q== null || addresses.full_address.EndsWith(q.Trim())) orderbyaddresses.id selectaddresses); varaddressesPerPage= new Pagination (addressesListpage??0pageSize); return View(addressesPerPage); } My pagination class: using System; using System.Collections.Generic; using System.Linq; using System.Web; namespace osms.Helpers { public class Pagination :List { public int PageIndex{ get ; private set ;} public int PageSize{ get ; private set ;} public int TotalCount{ get ; private set ;} public int TotalPages{ get ; private set ;} public Pagination(IQueryable source int pageIndex int pageSize) { PageIndex=pageIndex; PageSize=pageSize; TotalCount=source.Count(); TotalPages=( int )Math.Ceiling(TotalCount/( double )PageSize); AddRange(source.Skip(PageIndex*PageSize).Take(PageSize).ToList()); } public bool HasPreviousPage { get { return (PageIndex>0); } } public bool HasNextPage { get { return (PageIndex+1 } } } }

Akshay Phadke · Answer

Kindly attach your code so we can analyze it. There might be some network connectivity issue also check with the network team

Ehsan Sajjad · Answer

Can you show your linq query code?

Vinay Singh · Answer

Hi Please port your linq query.