These are some lessons I learned when improving query performance of an application written in C#, that uses Entity Framework Code First, LINQ, and SQL Server
Databases cache query plans—the first request may take longer, but subsequent requests are much faster.
A LINQ to SQL query is an
IQueryable, and may be reused when two queries are quite similar but differ somewhat in the
selectclause to return just the data you need. Returning whole objects, and data you don’t need, translates to a more time-consuming remote procedure call (RPC) to the database.
whereclause—at the database. If you use the
IQueryable, with a lambda expression that calls C# methods, the query will be performed on the
IEnumerablereturned from the database.
Perform sorting after filtering. This can be done using the orderby clause, but also using
IQueryablewith a lambda expression.
Restrict the number of records returned from the database to some maximum value—1000 works fine—using the
Pagination can be used to further reduce the data returned, using
IQueryableto skip records you don’t need, followed by
Takemethod to pick the records you do need.
If you want to use string value of an
Enumin a query, use
Enumobject. LINQ to SQL sends string values of Enum to the database, hence the query suffers no significant performance issues.
See Performance Considerations for more.