IQueryable Count method takes longer to execute.

Question

With a WCF built on top of a DB containing around 200 tables and Entity Framework, it takes lot of time (around 2 mins) to perform login the first time after building the WCF.

Stepping into the code revealed the IQueryable.Count method to be the culprit.

This happens only the first time after building the WCF code. Consecutive execution of the Count method is fast as expected.

What could be the reason? Is entities doing some kind of a background caching of sort after rebuilding the code?

Please share your thoughts!

UPDATED:

@Craig: Thanks for the Pre-Generation of views link

Also, this link has lot of performance improvement suggestions for EF

Also, check out Lazy Loading for EF library.

Answer 1

This is a known problem that will be resolved with .NET 4.0.

When you first run a web based application, the code must be cached. From then on, it runs at full speed. The article shows current methods of avoiding this initial slowdown by pre-running the code before your first user hits the service.

Answer 2

The Lame Duck's answer is helpful (up-voted), but it does not tell the full story. The first time you execute an Entity Framework query, several things happen. One is view generation, where SQL is compiled for common queries, like loading entity sets and loading individual entities. But view generation can also be done at compile time, which saves the first, unlucky, person to run a query the performance overhead of this step. This step is repeated whenever a new ObjectContext is initialized, so the small overhead of doing view generation at compile time pays off in a big way at runtime. The second is compilation of an IQueryable into a canonical command tree, which can be optimized with CompiledQuery. You may be facing one or both of these issues, so before you write this off as a .NET 3.5 SP 1 problem, it is worth checking them out.