performance issue while binding gridview with huge data

Problem: i am trying to fetch huge number of entries from the database and binding it to the gridview which is making my site performance very low. what i have used: i have indexed the parameters which are mainly involved while searching. Also i am stroing the database returned queries in session and using it while paging, instead of hitting the database again.

What i want? is there any way we can j开发者_如何学运维ust retrieve entries from the database for the page size of the grid. my grid page size is 10. So 10 entries would higly enhance the performance of the site. Since there are numerous fields in the datagrid and fetching them takes time. So is there any solution like this?

code for the query:

R1.DBLinqRDataContext objDB = new R1.DBLinqRDataContext();
return ( from p in (from a in objDB.table1
        orderby a.date descending
        join i in objDB.table2
        on a.ID equals i.ID
                    where ((SqlMethods.Like(a.Location, "%" + loc + "%")) && (Category != String.Empty ? (Category == "1" ? a.Func < 50 : a.Func > 50) : (SqlMethods.Like(a.loc, "%" + loc + "%"))))
        select a) join r3 in objDB.table3 on p.ID equals r3.CompanyID select p).Distinct().ToList();

Any assist?

if any more details please ask....thank you

To the other answer's point, doing a [query].Skip(x).Take(y) works great, but check when you make the skip and take calls that you're calling the IQueriable version and not the IEnumerable version.

If you call the IQueriable version then it gets incorporated into the query. IEnumerable will execute the query and then run the paging in memory and you loose all your performance gains.

Try reading through this