How entity framework works for large number of records?

c# database-design entity-framework entity-framework-6


I've already noticed question number going on.

My inquiry is this:

Is EF really production ready for large application?

These fundamental inquiries served as the basis for the question:

  1. EF loads every record into memory before running the query. How would EF operate if the table had roughly 1000 records?
  2. I must pull the record, make the necessary changes, and then push it to the database usingSaveChanges()
1/21/2018 1:23:30 PM

Accepted Answer

A comparable circumstance I encountered included a sizable database with numerous tables containing 7–10 million records apiece. To display the data, Entity Framework was used. What I discovered can help you perform well, My Entity Framework Golden Rules, in 10 Points

  1. Recognize that database calls are only made when actual records are needed. Try to fetch just one piece of data rather than requesting a lot of information because all SQL operations are only needed to create the query. Cut the fetch size as short as you can

  2. Yes, you should use stored procedures when appropriate (in some circumstances, they are a preferable option; they are not as bad as some would have you believe). Add function imports for them and import them into your model. You can reach them by phone as well. ExecuteStoreQuery and ExecuteStoreCommand() (). The same is true for functions and views, although EF calls functions in a rather peculiar way: "SELECT dbo.blah(@id)".

  3. When EF has to populate an Entity with a deep hierarchy, it operates more slowly. Use entities with deep hierarchies with extreme caution.

  4. On occasion, while seeking records but not necessary to edit them, you should instruct EF not to keep an eye on property changes (AutoDetectChanges). As a result, record retrieval is expedited.

  5. Database indexing is beneficial, but with EF it becomes crucial. You should appropriately index the columns you need for retrieval and sorting.

  6. When a model is big, the VS2010/VS2012 Model designer goes completely bonkers. Divide your model into medium-sized models as a result. Despite possibly referencing to the same database table, there is a restriction that prevents Entities from various models from being shared.

  7. Use the same entity, make the necessary changes, and save it only once when you need to make changes to the same object at various locations. The idea is to AVOID getting the same record, changing it, and saving it more than once. (Actual performance gain advice)

  8. Try not to fetch the entire entity when you only require the information in one or two columns. You have two options: directly running your sql or using a micro entity. In your application, you might also need to cache some frequently used data.

  9. There are few transactions. Be cautious around them.

  10. Your friend is SQL Profiler or any query profiler. Run it to view what the EF sends to the database as you construct your application. In your application, whenever you conduct a join using LINQ or a Lambda expression, EF often creates a Select-Where-In-Select type query, which might not always work effectively. If you come across a situation like this, get to work by joining the database and having EF fetch the results. (I overlooked the most crucial one!)

If you bear these factors in mind, EF ought to perform at least as well as simple ADO.NET.

1/19/2015 1:33:05 PM

Popular Answer

1. EF pulls all the records into memory then performs the query operation. How EF would behave when table has around ~1000 records?

That is untrue! Only the records that are required are fetched by EF, and queries are converted into correct SQL statements. EF can locally cache things withinDataContext It won't be a problem, though, as long as you adhere to the directive to keep context open only when necessary.

2. For simple edit I have to pull the record edit it and then push to db using SaveChanges()

True, but don't bother doing that until you can clearly observe the performance issues. You will only get one entry from the database fetched before it is stored because 1. is untrue. By writing the SQL query as a string and transmitting it as a simple string, you can get around that.

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow