Tips to improve Entity Framework Performance

 

  • 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.

  • 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:

    1. NorthwindDataContext context =newNorthwindDataContext() context.tblCities.MergeOption=MergeOption.NoTracking;

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

  • 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.

  • 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.

    1. //Bad Practice
    2. var customer =
    3. (from cust in dataContext.Customers
    4. select cust).ToList();
    5. //Good Practice
    6. var customer =
    7. (from cust in dataContext.Customers
    8. select new{
    9. customer.CustomerID,
    10. customer.Name,
    11. customer.Address
    12. }).ToList();
  • 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. For learning difference among all these collection refer my articles IEnumerable VS IQueryable, IEnumerable VS IList andVar VS IEnumerable.

  • 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

    1. // create the entity object
    2. NorthwindEntities mobjentity =newNorthwindEntities();
    3. //Simple Query
    4. IQueryable lstCus = from customer in mobjentity.tblCustomers
    5. where customer.City==“Delhi”
    6. select customer;
    7. //Compiled Query
    8. Func<northwindentities, string,=”” iqueryable<customer=””>> compiledQuery
    9. <northwindentities, string,=”” iqueryable<customer=””>=CompiledQuery.Compile<northwindentities, string,=”” iqueryable<customer=””>>(
    10. <northwindentities, string,=”” iqueryable<customer=””><northwindentities, string,=”” iqueryable<customer=””>(ctx, city)=>from customer in ctx.Customers
    11. <northwindentities, string,=”” iqueryable<customer=””><northwindentities, string,=”” iqueryable<customer=””>where customer.City== city
    12. <northwindentities, string,=”” iqueryable<customer=””><northwindentities, string,=”” iqueryable<customer=””>select customer);

    In above query we are passing the string parameter city for filtering the records. For more about anonymous method.

  • 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.

    1. // create the entity object
    2. NorthwindEntities mobjentity =newNorthwindEntities();
    3. int pageSize=10,startingPageIndex=2;
    4. List lstCus = mobjentity.tblCustomers.Take(pageSize)
    5. .Skip(startingPageIndex * pageSize)
    6. .ToList();
  • Avoid using Contains

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

  • 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.

  • Debug and Optimize LINQ Query

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

    1. IQueryable lstCus = from customer in mobjentity.tblCustomers
    2. where customer.City==“Delhi”
    3. select customer;
    4. lstCus.Dump();

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

Advertisements

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