EntityFramework taking excessive time to return records for a simple SQL query

Question

I have already combed through this old article: Why is Entity Framework taking 30 seconds to load records when the generated query only takes 1/2 of a second? but no success.

I have tested the query:

• without lazy loading (not using .Include of related entities) and
• without merge tracking (using AsNoTracking)

I do not think I can easily switch to compiled queries in general due to the complexity of queries and using a Code First model, but let me know if you experience otherwise...

Setup

• Entity Framework '4.4' (.Net 4.0 with EF 5 install)
• Code First model and DbContext
• Testing directly on the SQL Server 2008 machine hosting the database

Query - It's just returning simple fields from one table:

SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[Active] AS [Active], 
[Extent1].[ChangeUrl] AS [ChangeUrl], 
[Extent1].[MatchValueSetId] AS [MatchValueSetId], 
[Extent1].[ConfigValueSetId] AS [ConfigValueSetId], 
[Extent1].[HashValue] AS [HashValue], 
[Extent1].[Creator] AS [Creator], 
[Extent1].[CreationDate] AS [CreationDate]
FROM [dbo].[MatchActivations] AS [Extent1]

• The MatchActivations table has relationships with other tables, but for this purpose using explicit loading of related entities as needed.

Results (from SQL Server Profiler)

• For Microsoft SQL Server Management Studio Query: CPU = 78 msec., Duration = 587 msec.
• For EntityFrameworkMUE: CPU = 31 msec., Duration = 8216 msec.!

Does anyone know, besides suggesting the use of compiled queries if there is anything else to be aware of when using Entity Framework for such a simple query?

Answer 1

A number of people have run into problems where cached query execution plans due to parameter sniffing cause SQL Server to produce a very inefficient execution plan when running a query through ADO.NET, while running the exact same query directly from SQL Server Management Studio uses a different execution plan because some flags on the query are set differently by default.

Some people have reported success in forcing a refresh of the query execution plans by running one or both of the following commands:

 DBCC DROPCLEANBUFFERS
 DBCC FREEPROCCACHE

But a more long-term, targeted solution to this problem would be to use Query Hints like OPTIMIZE FOR and OPTION(Recompile), as described in this article, to help ensure that good execution plans are chosen more consistently in the first place.