Today, I stumbled upon a great article on Entity Framework performance gotchas and ways to make stuff faster. The article is from Ben Emmet, a technical project manager at Redgate  (http://www.red-gate.com/).

This blog post has two parts. Part 1, provides an index-style summary of two great articles on EF performance optimizations and part 2 introduces two lightweight tools that I often use when looking at EF performance issues, ExpressProfiler and LINQPad.

Part 1: Articles

“Entity Framework Performance and What You Can Do About It” by Ben Emmet (Redgate)

The article was published in the “technical journal and community hub from Redgate” which is called Simple Talk and prospers at https://www.simple-talk.com/. Check it out, it is a huge library of deep technical but easy to understand articles around Sql Server, .NET, Azure and SysAdmin duties, written by industry experts.

The article is called “Entity Framework Performance and What You Can Do About It” and can be found here:

https://www.simple-talk.com/dotnet/.net-tools/entity-framework-performance-and-what-you-can-do-about-it/

It is a rather long piece of work and I like how it starts with the more obvious pitfalls such as messed up LINQ queries (loading too much data, filtering in memory) and then goes into deep details on indexes, execution plans and execution plan caching. The article shows lots of screenshots from ANTS Performance Profiler (http://www.red-gate.com/products/dotnet-development/ants-performance-profiler) that help you in identifying the problem in the first place.

I provide an index-style list. Dive in if you discover not-yet-known topics. It’s worth it!

• Database access
• Being too greedy with rows
• The “N + 1 Select” Problem
• Being too greedy with columns
• Mismatched data types
• Missing indexes
• Overly-generic queries
• Bloating the plan cache
• Inserting data
• Extra work in the client
• Detecting Changes
• Change Tracking
• Startup Performance
• Precompiled Views
• Giant Contexts
• NGen everything
• Unnecessary Queries
• Other Tips
• Disposing
• Multiple result sets
• Caching
• Consider using upgrades
• Test with realistic data
• Occasionally, EF ins’t the answer

“Performance Considerations for Entity Framework 4, 5, and 6” by David Obando, Eric Dettinger and others

The article is published in the MSDN network and contains the following chapters:

• Cold vs. Warm Query Execution
• Caching in the Entity Framework
• Autocompiled Queries
• NoTracking Requests
• Query Execution Options
• Desing Time Performnance Considerations
• Loading Related Entities
• Other Considerations
• Investigating Performance

The article tackles the issues listed above and analyzes their performance impact for versions 4, 5 and 6 of Entity Framework.

https://msdn.microsoft.com/en-us/data/hh949853.aspx

Part 2: Tooling

While I am talking about EF, I would like to mention some tools that can be very helpful when analyzing EF performance. The obvious tools are a .NET performance profiler and/or a profiler for Sql Server queries.

Express Profiler

Very often, you are facing a challenging performance problem related to Sql Server query performance and you are missing the tooling. Either because you are not allowed to install anything on the machine or you don’t have a profiling tool available. If you are interested in the SQL queries that are sent to the server, ExpressProfiler comes to the rescue. It is a super lightweight application that lets you record the sql statements sent to an Sql Server database. And by super lightweight, I mean SUPER lightweight. It is as small as around 150 kilobytes and does not need any installation. Go get it now, it’s free and it’s great!

https://expressprofiler.codeplex.com

LINQPad

LINQPad (http://www.linqpad.net/) is another great tool that I use very often. It is a scratch pad for LINQ statements. Start it, connect to a DB, write a LINQ statement and immediately get the results. You cannot be any faster than this. It is lightweight (8MB), fast and the trial is free. However, I recommend to get the full version in order to have complete Visual Studio like intellisense. Paid versions are between USD 45 and USD 85 for a single user.

I often use LINQPad to fiddle around until I find the perfect LINQ statement to solve my problem, before I add it to my Visual Studio Project. LINQPad will show you the results, the execution time and the entire SQL statement.