Continuing on the topic of application performance tuning, let’s turn our attention specifically to the EF and take a look at some of what’s going on under the covers with EF queries.
The first thing to think about in broad terms is where is the time really going. Often it’s relatively easy to introduce a few counters or make some other small hack to find the worst offender in your application. My first bit of advice to you on this front is: Don’t be afraid to make temporary changes to your code. You can sometimes get great results by making a fresh enlistment in your source control system, checking out a bunch of files and hacking on the code in ways that you would never ship but which help to isolate things so you can figure out what’s really going on.
My next suggestion is: Get a profiler and figure out how to use it. Visual studio has a great profiler if you have a sufficiently advanced version, and that’s what I use, but there are other products on the market. There are even some profilers specifically aimed at working with applications built on top of the EF. In any case, if you care about perf, a profiler is hugely important. I’m no expert on any of these, but you can find some great resources on the web. Some of the the articles I’ve found useful for working with the VS profiler are: http://msdn.microsoft.com/en-us/magazine/cc337887.aspx and http://www.wintellect.com/CS/blogs/jrobbins/archive/2009/10/19/vs-2010-beta-2-sampling-and-instrumentation-profiling-in-depth-first-look.aspx.
However you decide to measure things, the first level of investigation which I’d recommend when you are looking at an EF-based application is to determine where your biggest bottleneck is:
If you can figure this out, then you can much more quickly home in on where to investigate further and what experiments you might want to try to improve performance.
In order to get to the next level of your investigation, it can help a lot to have a general idea of what all is happening and in what order when you execute an EF query. To that end, here’s a rough list:
OK. Now you know what goes on under the covers, and you’ve found something that seems to be taking more time than necessary. That brings us to…
Here’s a general rule-of-thumb checklist for EF query perf tuning in the order I would tend to investigate (since this is usually the order of highest bang-for-the-buck).
DANGER! DANGER! DANGER! If you read my post about perf tuning philosophy, all kinds of bells should be going off in your head about now. You should be saying something like, “I thought perf was a science and I should measure what exactly is the issue, create my own prioritization and then do a series of carefully controlled experiments.” Of course all those things are true. You SHOULD measure the reality in your particular situation. That said, I get asked these kinds of questions quite often, and some of these issues show up frequently enough that it if it’s not too expensive to try one of these fixes, you might just try them and measure the results—this could be cheaper to try than deep profiling or the like.
Thanks; this is a great list.
Regarding eager loading, I've found it doesn't tend to help performance, unless you absolutely need every column in the related entities. Eager loading can result in very complex SQL, due to the sheer volume of data retrieved. Worse, your pregenerated views no longer help, and query compilation can take a long time with lots of encludes. A precompiled projection is generally the fastest, when it works. When you must eager load, precompilation is absolutely essential. Without it, explicit loading is frequently much faster, despite the extra round trips.
Thanks for such a great post and the review, I am totally impressed! Keep stuff like this coming.
Great Post!!! Thanks.
I am adding a sql logging system to my programs. I want to open a new connection in every form with connection string below with only changing "Application Name" for example Application Name=FocusEngine-Invoice , Application Name=FocusEngine-Order. It makes all first query very slow for all new connection(10s). I know first initialization very slow for entity but if not change string there is no problem. How can I solve this problem any bodyhave an idea?
metadata=res://*/FocusLocalDB.csdl|res://*/FocusLocalDB.ssdl|res://*/FocusLocalDB.msl;provider=System.Data.SqlClient;provider connection string=
"Data Source=localhost;Initial Catalog=FocusLocalDB;User ID=sa;Password=as;pooling=true; max pool size=10000;Application Name=FocusEngine-Invoice;MultipleActiveResultSets=True";
"Data Source=localhost;Initial Catalog=FocusLocalDB;User ID=sa;Password=as;pooling=true; max pool size=10000;Application Name=FocusEngine-Order;MultipleActiveResultSets=True";