Monday, December 10, 2012

How to Improve Performance of Entity Framework Query ?

What is an Entity Framework ( EF ) ?
  • Entity Framework is an Object Relational Mapper (ORM)
  • It basically generates business objects and entities according to the database tables
  • Performing basic CRUD (Create, Read, Update, Delete) operations
  • Easily managing "1 to 1", "1 to many", and "many to many" relationships
  • Ability to have Inheritance relationships between entities

Entity Framework Architecture as below :

Entity Framework Architecture

Cold vs. Warm Query Execution

What is a Cold Query Execution ?
  • The very first time any query is made against a given model ,
  • The Entity Framework does a lot of work behind the scenes to load and validate the model
  • We frequently refer to this first query as a "Cold" query

First Query Execution ( Cold query ) Performance is Like below :

First Query Execution ( Cold query )

Second Query Execution (Warm query ) Performance is Like below :

Second Query Execution (Warm query )

How to Improve Performance of First Query Execution ?
  • For that We have to Remove Cost of View Generation

What is View Generation ?
  • In order to understand what view generation is, we must first understand what “Mapping Views” are :

            What are Mapping Views ?

                     # Mapping Views are executable representations of the transformations
                         specified in the  mapping for each entity set and association

            There are 2 types of Mapping Views exist
                1. Query Views
                         - These represent the Transformation necessary to go from the
                            database schema to the  conceptual schema

               2. Update Views
                        - These represent the Transformation necessary to go from the
                           conceptual schema to the database schema
  • The process of computing these views based on the specification of the mapping is what we call View Generation
  • View Generation can either take place dynamically when a model is loaded (run timeor at build time (compile time)
  • Default is a Run Time (that's why first query is very slow)
  • When Views are Generated, they are also Validated
  • From a performance standpoint, the Vast Majority of the cost of View Generation is actually the Validation of the Views
  • Which ensures that the connections between the Entities make sense and have the correct Cardinality for all the supported operations

What are the Factors that Affect View Generation Performance ?
  • Model size -  Referring to the number of entities and the amount of associations between these entities
  • Model complexity - Specifically inheritance involving a large number of types
  • Using Independent Associations, instead of Foreign Key Associations (will explain this in a separate blog post)

How to Use Pre-Generated Views to Decrease Model Load Time ?
  • We can use T4 Templates for Create Pre-Generated Views
  • Then View Generation is happened in Compile Time

What is a T4 ?
  • Text Template Transformation Toolkit
  • T4 is a general-purpose Templating Engine you can use to generate C# code, Visual Basic code, XML, HTML, or text of any kind

How to Use T4 For Generate Views ?

Here I am using VS 2010,C# and EF 4.1 with Code First Approach as Technologies

Step 1 : First you need to Download the Templates
              # Right click on your project's Models Folder (where, DbContext derived class exist)
              # Select Add -> New Item Like below
New Item

 Step 2 : # In the Add New Item dialog go to “Online Templates”
              # Then Give "EF Views" as Search Condition
              # After that Select "EF CodeFirst View Generation T4 Template for C#" Like below

EF CodeFirst View Generation T4 Template for C#

Step 3 : # In the above Step 2 Change the Name of the file at the bottom to {Context}
             # Where {Context} is the Name of the Class Derived from DbContext you want to
                 create Pre-Generated Views for. 

In my scenario it's

B'cos my DbContext derived class in Models Folder as below

using System.Data.Entity;

namespace MvcMusicStore.Models
    public class MusicStoreEntities : DbContext
        public DbSet<Album> Albums { getset; }
        public DbSet<Genre> Genres { getset; }
        public DbSet<Artist> Artists { getset; }
        public DbSet<Cart> Carts { getset; }
        public DbSet<Order> Orders { getset; }
        public DbSet<OrderDetail> OrderDetails { getset; }

Step 4 : Install Template for First Time

Install Template for First Time

Step 5 : Then Finish the Template Installation by clicking OK Button


What If You have already installed "EF CodeFirst View Generation T4 Template for C#" ?

  • Then You can Bypass Step 2,Step 4 & Step 5's Screens with below one
Bypass Step 2,Step 4 & Step 5's Screens

What is a Final Look of  T4 - Generated Views Inside a Project ?

Final Look of  T4 - Generated Views

  • By Creating Pre-Generated Views You can get More Than 4 ,5 Times Performance Boost for Your First Query Execution
  • This is a Big Difference when You're considering a Large Domain Models
  • So Try This and Enjoy the Performance Boost

I hope this helps to You.Comments and feedback greatly appreciated.

Happy Coding

Entity Framework Articles


  1. OK I will try it this my application.



    1. Hi SANJAY,

      If you need any help,please let me know.

  2. planning to implement in upcoming project.

    1. Hi devintonet,

      OK Sure.
      If you need any help,please let me know.

  3. Hi,

    Nice article.

    Unfortunately my current project back end is Oracle. So we are not using Entity framework. But I used lot of Linq queries for querying in object. So in my future projects I will take care of this.

    Thanks to author.

    Mukesh Selvaraj

    1. Hi Mukesh,

      Thanks for your feedback.
      But If you want you can use entity framework with oracle also.
      Check below link.

  4. like this.

    -Peter Ernst-

  5. It is interesting, but I would like to see the performance difference in numbers. An improvement in just the first call could not be worth in a back-end application if it involves create mess of files in the solution. I would use it when the solution is finished and only if it is really solving a problem.

    I think that the most critical thing that needs to be understood, it is the difference between eager loading, lazy loading and the data multiplication effect, in order to get the right balance in our queries:

    Of course, not to mention the common SQL query optimization approaches that we have been using long before ORMs.


    PS: It is funny to read about performance in a web site that is using 100% of one of my CPUs with that snow javascript effect! :D

    -Valeriano Tórtola Luis-

    1. Hi Valeriano,

      Thanks for your feedback.

      Actually here cannot give performance difference in numbers.B'cos it's depend on the situation.Such as model size,There relationship types, inheritance, whether you have vertical keys vs. independent associations and how interconnected your model is like wise.

      Yes your right.This thing will happen first query only.Then it's cached and served from that cache.But this is very useful in cloud environment.B'cos application domain gets changed quite regularly (Restart IIS or change virtual machine like wise).So then first query is also very important thing (need good performances).

      You can get more about this using below links:

      Discussion with @pawel who is the creator of Pre-Generated views (see comment section)

      Very descriptive one about above issue

      I Hope this will help.

    2. I will definitely give it a try. What happens if you change your data model during development? do you have to generate the templates again?

      Regarding IIS, so far I have been using "warm-up" techniques, like the module before and since .net4 the "autostart" feature:

      You can define custom warm-up code that initialize whatever you want.


      -Valeriano Tórtola Luis-

    3. Hi Valeriano,

      Yes you need to recreate views each time your model changes otherwise you will get an exception.(i.e. Remove existing one and create new pre-generated views with latest changes)

      I Hope this will help to you.

  6. Agree sampath.

    However try run the SQL SERVER Pro-filer and trace the query and analyze if its take more time then optimize Linq query accordingly.

    -Adnan Samuel-

    1. Hi Adnan,

      Actually your telling here about cold query scenario (second time query execution).
      I have mentioned about first query execution. That is unique for Entity framework.So if you need more information about that, please read the comment I put on Valeriano (And the links mentioned).

  7. I really like your tips, especially with creating pre-generated views using text 4 templates (tt) using code-first approach. I'll definitely be adding this feature - as I am quite new to EF.

    -Gladys yelland-

    1. Hi Gladys,

      Yes.Go ahead and if you're having any problem about T4 templates please let me know.

  8. When created the {Context} file it gives two errors.

    Error 29 Compiling transformation: 'System.Data.Entity.Design.EntityFrameworkVersions' does not contain a definition for 'Version3'

    Error 28 Compiling transformation: The name 'EdmxWriter' does not exist in the current context

    trying to solve this. If Sampath has any comment, really appreciated.

    -Dinesh Nadun-

    1. Hi Dinesh,

      You may have get more information about above issues by using below mentioned links.

      Author of T4 Template.

      Other links:

      I hope this will help to you.

  9. Sampath

    This is even better than Entity Framework:

    1. Hi Asava,

      Thanks for the useful link about another ORM API.
      Keep in Touch.

  10. Sampath Lokuge: I was just reading comments above, and someone had questioned about performance gain via per-generated view, keeping in mind that its only going to improve for 1st time , i think that keeping code maintenance in mind this doesn't seems to be a good option.
    I am trying to optimized performance of our web application which is using EF5 , and your artical on using max 2 includes in single query seems helpful, but is there is any thing we can do improve performance creating db context on every request. Any idea which is the limit of max db connection ?

    1. Hi Anshual,

      Thanks for your feedback. :)

      Performance Considerations for Entity Framework 5 :

  11. i try your sample. but having some error. so can i have sample code for Generate Views ..


Thanks for your Feedback.