Entity Framework associations killing performance

Question

Here is the performance test i am looking at. I have 8 different entities that are table per type. Some of the entities contain over 100 thousand rows.

This particular application does several recursive calculations on the client so I think it may be best to preload the data instead of lazy loading.

If there are no associations I can load the entire database in about 3 seconds. As I add associations in any way the performance starts to drastically decline.

I am loading all the data the same way (just calling toList() on the entity attached to the context). I ran the test with edmx generated classes and self tracking entities and had similar results.

I am sure if I were to try and deal with the associations myself, similar to how I would in a dataset, the performance problem would go away. On the other hand I am pretty sure this is not how the entity framework was intended to being used. Any thoughts or ideas?

Answer 1

Could you post some code samples about how you are doing your queries? Which version of Entity Framework are you using? If 4, are you using lazy loading?

Answer 2

Loading entities with relationships is going to be much slower than loading entities without even if the related entities are not fetched at load time since it will need to create the complex object used to track the relationship in one case vs perhaps a simple value type like an int in the other. How much slower are you seeing it?

But ...

Preloading 100 thousand rows sounds like a really bad idea. When you do ToList() you have eliminated any chance that EF and SQL can do any kind of optimized query against your data. Are your calculations such that you always need to examine all the data? Have you tried it without preloading and examined the queries it is generating? Have you tried using .Include to just include the related objects you know you will need?

EF will be smart about caching if you give it the chance.

Answer 3

Thanks for your reply.

I had been seeing a descrease in performance by about 5x at least after adding associations.

I decided to try and change the way I had been trying to fetch my data. I got the performance to be acceptable by fetching part of the data at a time. I am now using lazy loading and prefetching data to improve performance. I now am having some issues trying to understand why I am getting some unnessesary round trips. I think I don't totally understand caching (Please see below). Thanks for your help!

http://stackoverflow.com/questions/2725149/help-me-understand-entity-framework-4-caching-for-lazy-loading