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 :

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 :

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

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 time) or 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

         

 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

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

In my scenario it's MusicStoreEntities.Views.tt

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 { get; set; }

        public DbSet<Genre> Genres { get; set; }

        public DbSet<Artist> Artists { get; set; }

        public DbSet<Cart> Carts { get; set; }

        public DbSet<Order> Orders { get; set; }

        public DbSet<OrderDetail> OrderDetails { get; set; }

    }

}

Step 4 : 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

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

Conclusion

• 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

How to Convert Linq Entity Query into T-SQL ?

How to Use Entity Framework SelectMany ?

How to Improve Performance of Entity Framework Query (over 400 times)?