READERS COMMENTS: performance of EF vs. LINQ to SQL

Original Comment: “EF ObjectQueries seem to create some pretty poorly performing queries compared to LINQ in my experience. 2. If you are saying Entity SQL performs really well, what are you comparing this to, ObjectQueries or other data access technologies

I did some digging on this and basically we have to recognize that each of these technologies has different strengths and weaknesses and also that as expected they both share some performance drawbacks when compared to “lower level” technologies like ADO.NET.

For instance, it is clear that LINQ to SQL performs better on things like “cold application startup time”. Metadata loading and the generation of the client-side views that EF uses to support sophisticated mapping take time. However, this is a price you pay only once in server based applications. It is also true that LINQ to SQL very often generates queries that are easier on humans (note that this does not always translate to better SQL execution but…) and in some cases even simpler for SQL Server to execute. There are cases still, in which Entity Framework is the clear winner.

The following represents pretty complex stuff that the average developer will never see and has no interest in knowing about, so risking making EF seem more complex when in reality what the developer sees is not that much more complex, here it is:

In general, the architecture of one and other product make different assumptions that affect performance. For instance, Entity Framework is optimized to have a simple provider model that enables many different database vendors to support the technology. Therefore, all queries in EF are translated into database independent canonical query trees that are composed of a relatively reduced set of operators. We do some level of optimization work in these query trees, but we also leave some of it to providers, who we assume, will know better what kind of SQL to emit that will be optimal for the target database engine. LINQ to SQL, on the other side, takes a different approach. its API is defined at a higher level and therefore there is a relatively large component in LINQ to SQL that is optimized for emitting Transact-SQL queries.

All that said, we have been continuously investing in improving the SQL generated by EF, by improving the query trees we generate and by improving the provider we own: the one that generates T-SQL for SQL Server. We have also invested substantially in fixing LINQ to SQL bugs and in improving things like how both technologies handle string parameters. Hopefully, the fruits of these investment will be apparent in the versions that we will release with .NET 4.0 and Visual Studio 2010.

In fact on April 08th we will have  a  webcast exploring the new additions and improvements made to EF in .NET 4.0. Stay tuned!

Joel Reyes

Technorati Tags: Joel Reyes,Entity Framework,LINQ to SQL