Speed up linq query without where clauses

Quick LINQ performance question.

I have a database with many many records and it's used for a webshop.

All query logic and paging is done with LINQ, and it performs quite well. This is, because the usual search for products contains one or more where clause, and that shortens my result set to a couple of hundred results at max. But.. there is an option to list all products (when no search criteria is provided), a开发者_JAVA技巧nd that query is slow.. real slow. Even though i'm just asking for a single page with .Skip(20).Take(10), it's still slow because the total result is something like 140000 products. Is there a way to limit this (or all) query, so that the speed of the whole thing is kept okay? I don't want to force my customers to provide one or more criteria.. but on the other hand i have no problem with telling them that they can never find more than 2000 products.

Thanks for helping! Tys

Why don't you limit the number of records on the sql side as described in this post http://www.sqlservercurry.com/2009/06/skip-and-take-n-number-of-records-in.html

Watch out for any "premature" enumerations when you pass down queries/results in your code!

There are also several LINQ visualizers available, which can help to see what the LINQ expressions actually translate to. Or you can play around with expressions in LINQPad before integrating in your code…

What you can do is to have Linq use stored procedure from the database.

In that case, it will be faster because it is the database engine who will do the work and return it to Linq; the database engine is made for that, and it is closer to data than Linq.

I suggest you give it a try and give us feedback

You can check what indexes has the table and what PK is. It could be the table has no index at all so records compared by field values. Also you can catch the query in the SqlProfiler, run it separately and analyse its query plan.