Do you ever try to optimize the performance of your Entity Framework queries? If so, there are a couple of things you can do to gain performance without a lot of gymnastics.
Having Database Statistics on columns from which you filter result sets can be a big boost, if you are connected to an SQL Server database. Having those statistics in place can allow Entity Framework to push the work of the filter into the WHERE clause of the underlying SQL statement, resulting in better performance. This is especially true for larger tables. By default, SQL Server is configured to automatically create and maintain statistics on columns. Sometimes you need to turn this off in high volume databases. However, it is likely you won’t be using Entity Framework for high volume database connectivity.
Having indexes for your SQL Server tables on columns used for filtering can have an added boost. These are not automatically created by SQL Server. Indexing is a kind of art. Too many and your database slows for creating and modifying data. Too few, and your database slows because it has to thrash through data. The more data, the more these two needs conflict. Most likely you should have indexes for columns that are foreign keys, especially if you include them as collections in the parent objects. For example, an OrderDetails table should have an index on the OrderId column used to join them to the parent Orders table.
When possible, avoid the syntax that evaluate your query as n IEnumerable. You want to have your query resolved as an IQueryable. Use Expressions instead of Func delegates, because Expressions pass the filter to SQL Server with a dynamic where clause. Using a Func lambda expression causes the database to return all rows, and the filter is implemented on the client side. Depending on the size of the table, your performance may suffer using one over the other. Here is a link to Jamie King’s video demonstrating the difference between these two method.
Do you have a tip for techniques you use to optimize your Entity Framework performance? Great, leave us a comment for the benefit of us all.
Cheers,
Ben