Improving query performance in Entity Framework
These are some lessons I learned when improving query performance of an application written in C#, that uses Entity Framework Code First with SQL Server. A LINQ to SQL query is an IQueryable
, and can be reused when two queries are quite similar but differ somewhat in the where
, group
, orderby
, or select
clauses.
-
Databases cache query plans—the first request may take longer, but subsequent requests are much faster.
-
Use the
select
clause 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. -
Perform filtering—using
where
clause—at the database. If you use theWhere
method ofIQueryable
, with a lambda expression that calls C# methods, the query will be performed on theIEnumerable
returned from the database. -
Perform sorting after filtering. This can be done using the orderby clause, but also using
OrderBy
method ofIQueryable
with a lambda expression. -
Restrict the number of records returned from the database to some maximum value—1000 works fine—using the
Take
method ofIQueryable
. -
Pagination can be used to further reduce the data returned, using
Skip
method ofIQueryable
to skip records you don’t need, followed byTake
method to pick the records you do need. -
If you want to use string value of an
Enum
in a query, useToString()
method onEnum
object. LINQ to SQL sends string values of Enum to the database, hence the query suffers no significant performance issues.
Also see Performance Considerations for additional insights.