We have some of the dashboards in application which has 10,000s of records, of course we have pagination - but EF is very poor in terms of performance and all those Get Queries for Bulk retrieval are very slow.
I was looking into few options to replace those queries with either Dapper or ORMLite kind of Micro-ORM but unfortunately, they do not support iqueryable which is a must have for our dashboards/grids because we have lots of things going on like, filtering-search-sort etc.
The question i would like to put forward is, is there anyone who has come across similar situation? What path did you opt for?
First, make sure you disable tracking in your EF queries (
AsNoTracking()), this should give you first boost.
Then, tune the database, reindex, move to a faster machine etc.
If things are still too slow, consider trying the old good Linq2SQL, it is faster at materializing.
If still not satisfied, forget the
IQueryable, have a parametrized view/stored proc and use Dapper to call the server with all necessary params.