Home > Entity Framework > Tips to improve Entity Framework Performance

Tips to improve Entity Framework Performance

LINQ to Entity is a great ORM for querying and managing database. It offers a lot of things, so it is mandatory to know about performance of it. These are right up to a certain point as LINQ comes with its own penalties. There are some tips and tricks that we should keep in mind while designing and query database using entity framework ORM. Here is a list of some tips that I would like to share with you.

  1. Avoid to put all the DB Objects into One Single Entity Model

    Entity Model specifies a single unit of work, not all our database. If we have many database objects that are not connected to one another or these(log tables, objects used by batch processes etc.) are not used at all. Hence these objects are consuming space in the memory and cause performance degrades. So try to make separate entity models of related database objects.

  2. Disable change tracking for entity if not needed

    Whenever you retrieve the data only for reading purpose, not for modification then there is no need of object tracking. So disable object tracking by using MergeOption as below:

    NorthwindDataContext context = new NorthwindDataContext();

    context.tblCities.MergeOption = MergeOption.NoTracking;

    This option allows us to turn off the object cache and unnecessary identity management of the objects.

  3. Use Pre-Generating Views to reduce response time for first request

    When the object of ObjectContext is created first time in the application, the entity framework creates a set of classes that is required to access the database. This set of classes is called view and if your data model is large then creating the view may delay the web application response to the first request for a page. We can reduce this response time by creating view at compile time by using T4 template or EdmGen.exe command-line tool.

  4. Avoid fetching all the fields if not required

    Avoid fetching not required fields from the database. Suppose I have table of Customer with 20 fields and I am interested only in three fields – CustomerID, Name, Address then fetch only these three fields instead of fetching all the fields of the Customer table.

    //Bad Practice

    var customer =

    (from cust in dataContext.Customers

    select cust).ToList();

    //Good Practice

    var customerLite =

    (from cust in dataContext.Customers

    select new {

    customer. CustomerID,

    customer.Name,

    customer.Address

    }). ToList ();

  5. Choose appropriate Collection for data manipulation

    In linq we have Var, IEnumerable, IQueryable, IList type collection for data manipulation. Each collection has its importance and performance impact on the query, so beware of using all these collection for data manipulation.

  6. Use Compiled Query wherever needed

    Make a query to compiled query if it is frequently used to fetch records from the database. This query is slow in first time but after that it boost the performance significantly. We use Compile method of CompiledQuery class for making compiled query.

    Suppose you required to retrieve customers details again and again based on city then make this query to compiled query like as

    // create the entity object

    NorthwindEntities mobjentity = new NorthwindEntities();

    //Simple Query

    IQueryable lstCus = from customer in mobjentity.tblCustomers

    where customer.City == “Delhi”

    select customer;

    //Compiled Query

    Func> compiledQuery

    = CompiledQuery.Compile>(

    (ctx, city) =>from customer in ctx.Customers

    where customer.City == city

    select customer);

    In above query we are passing the string parameter city for filtering the records.

  7. Retrieve only required number of records

    When we are binding data to grid or doing paging, retrieve only required no of records to improve performance. This can achieved by using Take,While and Skip methods.

    // create the entity object

    NorthwindEntities mobjentity = new NorthwindEntities();

    int pageSize=10,startingPageIndex=2;

    List lstCus = mobjentity.tblCustomers.Take(pageSize)

    .Skip(startingPageIndex * pageSize)

    .ToList();

  8. Avoid using Contains

    In LINQ, we use contains method for checking existence. It is converted to “WHERE IN” in SQL which cause performance degrades.

  9. Avoid using Views

    Views degrade the LINQ query performance costly. These are slow in performance and impact the performance greatly. So avoid using views in LINQ to Entities.

  10. Debug and Optimize LINQ Query

    If you want to debug and optimize your query then LINQ Pad is a great tool for this purpose. I am a big fan of LINQ Pad. It is very useful for query construction, debugging and optimization.

    IQueryable lstCus = from customer in mobjentity.tblCustomers

    where customer.City == “Delhi”

    select customer;

    lstCus.Dump();

    Dump method of LINQ Pad give the result of above query in the result window.


About these ads
  1. March 14, 2013 at 3:21 am | #1

    Hi

    great article! I’m wondering if I’m doing a query like this:

    SqlParameter param = new SqlParameter(“id”, SqlDbType.Int32);
    param.Value = 10;

    Customer c = context.Customers.SqlQuery(“select * from Customers where Id = @id”, param).First();

    is this similar to compiled query?

    Also I’m wondering if I create a new record in a table by calling ctx.Customers.Add(Customer);
    ctx.SaveChanges();

    how can I get the ID of the just created record, so that i can pass it to following logic?

    • Armand
      March 14, 2013 at 6:21 am | #2

      after savechanges you should be able to get the id just by calling Customer again, i.e.

      ctx.Customers.Add(Customer);
      ctx.SaveChanges();

      //Get the id from the just added customer//
      var id = Customer.CustomerID;

      • March 14, 2013 at 6:57 am | #3

        Yes,you can get CustomerID by using above code. Whatever value provided to this property will return and stored to your variable id and you can then use that id for further code.

  2. March 14, 2013 at 4:50 am | #4

    Hello Lubomir,

    Thanks!

    No it’s not similar to compiled query,

    The CompiledQuery class provides compilation and caching of queries for reuse. Conceptually, this class contains a CompiledQuery’s Compile method with several overloads. Call the Compile method to create a new delegate to represent the compiled query. The Compile methods, provided with a ObjectContext and parameter values, return a delegate that produces some result (such as an IQueryable instance).Please refer below link to get more ideas regarding complied query

    http://msdn.microsoft.com/en-us/library/bb896297.aspx

    You can write simple logic to get ID of newly created record as following

    using (TestEntities ctx = new TestEntities ())
    {
    ctx.Customers.Add(Customer);
    ctx.SaveChanges();
    id = Customer.ID;
    }

    Hope it helps !

    Regards,
    Rahul

  3. March 14, 2013 at 6:27 am | #5

    Nicely done on the article. I would suggest, though, that you may want to update or add a new article discussing the n+1 problem when fetching related parent/child data. My understanding is that this is where EF and most other ORMs suffer the most abuse/misuse.

    • Bill
      March 14, 2013 at 12:47 pm | #6

      I strongly recommend the free MiniProfiler and MVCMiniProfiler tools available on Nuget for inspecting your queries in dev. It lets you easily identify N+1 issues with minimal effort.

  4. March 14, 2013 at 10:34 am | #7

    Great article. In regards to #8, what do you recommend using instead of contains?

  5. March 14, 2013 at 1:34 pm | #8

    Thanks for the article Rahul. I’d like to add a few suggestions. It’s great to do projections (limit columns returned) but warn your readers that this will result in anonymous types that can’t be passed around and won’t be change tracked. That’s often desirable but sometimes a surprise to the new learners.

    To Lubomir. there’s never any reason to write code like that with entity framework! You can just write a simple LINQ query or in that particular case even take advantage of the DbSet.Find method.

    Also, if you are using .NET 4.5 (or deploying to .NET 4.5), there’s no longer a need to use pre-compiled queries. EF in .NET 4.5 automatically caches the relevant SQL for linq queries. Precompiled queries won’t work with DbContext.

    I’m a big fan of notracking queries. If your readers are using the DbContext, there is a simpler way to do this using DbSet’s AsNoTracking method. But for ObjectCOntext, your way is the only possibility.

    I’m very curious about your suggestion about the views. I use them a lot and have not realized they cause a performance problem. Have you experienced this?

    And finally…yes super +1 on linqpad! :)
    Thanks again

    Julie

  6. Shawn Mclean
    March 14, 2013 at 3:34 pm | #9

    Some of these are kinda hard to do when using the repository pattern. Any thoughts on how to do this? (a new blog post or a repository example maybe?)

  7. Mauricio Aviles
    March 18, 2013 at 10:33 pm | #10

    Good article. Thanks for the tips. What are your suggestions for avoiding/replacing “contains” when it is needed to check for existence?

  8. June 8, 2013 at 1:23 pm | #11

    Since the admin of this site is working, no uncertainty very shortly it will be well-known, due to
    its feature contents.

  9. Burak TARHANLI
    December 4, 2013 at 6:18 am | #12

    Great article! Thank you so much. This is very useful.

  10. Ajay
    December 6, 2013 at 9:35 pm | #13

    Great Article! I have one question about your point number 9. Avoid using Views ~ I am assuming we cannot use Database views in Entity Framework Code First. if yes, then why we cannot use Dbviews in code first.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 25 other followers

%d bloggers like this: