How is the performance of entity framework 4 vs entity framework 3.5?

Question

I have one query on my page that takes at least a half second to execute using EF 3.5. When I used a stored procedure the speed was noticably faster. It is a very complex query. Will there be any performance improvements in the upcoming EF 4.0? And does EF 4.0 really beat out 3.5 performance wise?

Answer 1

From the ADO.NET blog:

Customizing Queries – Adding support for existing LINQ operators, recognizing a larger set of patterns with LINQ, writing model defined functions along with the ability to use these in LINQ, and a number of other ways to create and customize queries.

SQL Generation Readability Improvements – Improving the readability, along with TSQL performance optimizations, of the generated queries to make it much easier to understand what is happening

So these two points imply you could see improvements in the way it's generating your query from LINQ.

However it's unlikely that an ORM will ever be able to out-perform a query you've written from scratch as it has to cater for so many different scenarios, and usually the most common one is defaulted to. EF 3.5 seemed to produce some very efficient join SQL when I used it, probably the best I've seen from an ORM so there is hope you can ditch the SP in 4.0.

If you've got a stored procedure I'm guessing it's a big query - sending this SQL text each time to the server will cause a lot of network traffic which is one other thing you may or may not have considered. Obviously on the same server or inside the same internal network this a 'cutting your hair to lose weight' style optimisation.

Answer 2

The short answer is it's too early to tell. The .Net guys are focusing almost entirely on performance until the release on April 12th has to be finalized and localized. Also, what is meant by faster? Faster can be viewed in many ways, for example:

• Entity Framework 4.0 has new features, the object tracking improvements alone may mean huge wins since you're not doing that manual work yourself...in any case, at least the development's faster.
• If it didn't work at all before, lighter weight objects with POCO support may mean a lot less memory being shifted when dealing with lots of objects as well. No matter how small the cost of extra properties being populated when fetching from the DB, there is a cost both in instantiating and tracking them (load time and memory consumption).

In your specific case, a half second is a long time for anything but a very complex or high volume query...have you looked to see how much time is spent in the database and how much time is spent once .Net has the data? If you're spending most of your time outside of SQL then yes, the base improvements in reflections in Net 4.0 should provide you some speed improvement...however if you're spending all your time in SQL, it won't help much at all. The bulk of your performance problem may be indexing of the generated SQL and not Entity Framework hydration performance.

I would follow Kane's comment, look at the SQL it's generating for your query, is it possible for you to post this and the stored procedure that is quick so we can maybe find where the problem lies?

Answer 3

When it comes to really complex queries, I've not seen any evidence that any of L2S, NH, or EF can generate a better query plan than I can in a sproc. I love ORM's (especially NH), but there are still times when ORM execution time can get curbstomped by a well written sproc.