Is the ADO.NET Entity Framework known for generating poorly performing queries?

Question

This question is inspired by the comment posted to the latest ServerFault blog post:

Are you guys still using LINQ to SQL?

I know I can use SQL Server Profiler and/or the ToTraceString method to see the generated queries and analyze them myself. However, I am looking for opinions from people with hands-on experience with administering databases accessed by applications utilizing the Entity Framework.

Are Entity Framework queries a common cause of performance problems?
Can LINQ queries be optimized in such cases or is raw Transact-SQL the only solution?

Answer 1

Individual queries are ok

One of the biggest performance 'issues' with ORM tools (Entity Framework, Linq, LLBLGen, NHibernate, etc...) is not so much the performance of the individual queries that are executed (most are just CRUD calls which are retrieving a single record back based on a primary key).

Incorrect use of Lazy Loading = Bad Performance

It is the incorrect use of lazy loading, where if you have not setup your prefetch attributes correctly you can end up with a significant number of executes against the database (something that could have been retrieved in a single call to the database, becomes 200 individual executes, with all of the network and IO overhead associated with them)

from LINQ to SQL, Lazy Loading and Prefetching

It turns out that each access to the ProjectEntity and CustomerEntity object causes a separate query to the server (shown) for the first time a particular Project or Entity is referenced. This means for each project in the application there will be two additional queries hitting the server so if I show 20 projects I’ll hit the database 40 extra times. That’s lazy loading for you and it’s usually a problem in list based displays like this. It can be much worse if EVERY row you display requires one or more related entities.

Key is knowing what you're accessing

The real difference between using an ORM and handcranking it yourself is that when you code it yourself you are forced to think about how the SQL should be structured and you can easily go in and tweak it for the particular scenario you're trying to solve.

Whereas when using an ORM you are limited by the options and customization that the ORM tool gives you, and a lot of developers just leave it to the ORM tool itself (assuming/hoping it will come up with best plan), which is why ORM tools are widely regarded as being good for CRUD but not for more complex operations...

Note: Lazy loading can be a really good thing, it's when it's used inappropriately that's the issue...

Answer 2

It depends on what the queries are. ORMs are usually really good at CRUD, as they are usually simple. The more complex the query, the greater the chance of a bad query. You can tweak the generated queries by tweaking the LINQ statements. Sooner or later though, you'll get tired of fighting and use SQL queries or stored procedures for anything that is complex.

Answer 3

Linq to SQL and Linq to Entities (Entity Framework) are some different implementations. The first one is older and more lightweight ORM then the second one. Somebody likes using the first one because of performance. Here is some links to find out differences between these technologies:

Entity Framework vs LINQ to SQL LINQ to SQL vs ADO.NET Entity Framework

This article explains why EF is so slow and how to increase performance (if it is possible :)):

EF Query Tuning

The main problem: the first call to database (entities creation, mapping...) is too slow. So I still prefer old good stored procedures for critical queries (applications). As a variant it possible to use EF (it's my opinion) with application cache with preloading.