How to Improve LINQ to SQL Performance with Complied Query

No.of Views3742
Bookmarked0 times
Downloads 
Votes1
By  Dhananjay Kumar   On  08 Jan 2011 01:01:15
Tag : LINQ , Application Performance Optimization
In this article I will explain how to improve LINQ to SQL query performance with Complied Query. There may be scenario where we need to execute a particular query many times and repeatedly. LINQ allows us to make this task very easy by enabling us to create a query and make it complied always. We call this type of query as complied query.
emailbookmarkadd commentsprint

Images in this article missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at info@codegain.com

 

Introduction

In this article I will explain how to improve LINQ to SQL query performance with Complied Query. There may be scenario where we need to execute a particular query many times and repeatedly. LINQ allows us to make this task very easy by enabling us to create a query and make it complied always.  We call this type of query as complied query.

Benefit of Compiled Query

1.    Query does need to compile each time so execution of query is fast.
2.    Query is compiled once and can be used any number of time.
3.    Query does need to be recompiled even if the parameter of query is being changed.

Steps to create Complied Query

1.    Create a static class
2.    Add namespace System.Data.Linq.
3.    Use CompliedQuery class to create complied LINQ query.

Let us say we want to create a compiled query to retrieve the entire Person from School database.

1.    Create Static class 

Image Loading

2.    Define static query in the class 

Image Loading

We can pass as many parameters in Func

1.    First parameter is always name of the DataContext class created by LINQ to SQL. In our example name of DataContext class is DataClass1DataContext.
2.    Last parameter is the result parameter. This says the return type of the result of the query.  This is always a generic IQueryable.  In our example it is IQueryable<Person>
3.    In between first and last parameter we can have parameter to apply against the query.

Image Loading

4.    Keyword to create complied query is

Image Loading

Let us write a complied query to fetch all the Persons from DataContext

Image Loading

And we will call this compiled query as below; MyCompliedQueries is name of the static class.

Image Loading

Program.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq ; 
 
namespace ConsoleApplication5
  {
    class Program
        {
            static void Main(string[] args)
                {
                    DataClasses1DataContext context = new DataClasses1DataContext();
                    var result = MyCompliedQueries.CompliedQueryForPesron(context);
                    foreach (var r in result)
                    {
                        Console.WriteLine(r.FirstName + r.LastName);
                    }
 
                    Console.ReadKey(true);
                    
                }
        }
 
    static class MyCompliedQueries
        {
               public static Func<DataClasses1DataContext ,IQueryable<Person>>
               CompliedQueryForPesron = CompiledQuery.Compile(
                                          (DataClasses1DataContext context)=>
                                              from c in context.Persons select c );
 
         
        }
}

 

Output 

Image Loading

Now if we want to pass some parameter in compiled query

Image Loading

And we will call this query as below,

Image Loading

Program.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq ; 
 
namespace ConsoleApplication5
  {
    class Program
        {
            static void Main(string[] args)
                {
                    DataClasses1DataContext context = new DataClasses1DataContext();
                    
                    var result1 = MyCompliedQueries.CompiledQueryWithwhere(context, 9);
                    foreach (var r in result1)
                    {
                        Console.WriteLine(r.FirstName + r.LastName);
                    }
 
                    Console.ReadKey(true);
                }
        }
 
    static class MyCompliedQueries
        {
               
               public static Func<DataClasses1DataContext , int , IQueryable<Person>>
                 CompiledQueryWithwhere = CompiledQuery.Compile(
                                           (DataClasses1DataContext context,
                                               int PersonId) =>
                                               from c in context.Persons
                                               where c.PersonID == PersonId select c
                                              );
 
         
        }
}

Output 

Image Loading

Conclusion

 In this article we have learned how to improve LINQ to SQL performance by multiple times than normal query executing time in LINQ. To improve the performance we have used Complied Query to reduce parse query again and again and run directly without re generate again. Hopes help and thank you for reading.

 
Sign Up to vote for this article
 
About Author
 
Dhananjay Kumar
Occupation-Software Engineer
Company-Infosys Technolgies,Pune
Member Type-Gold
Location-India
Joined date-20 Jul 2009
Home Page-http://dhananjaykumar.net/
Blog Page-http://dhananjaykumar.net/
Dhananjay Kumar is Microsoft MVP on connected system. He blogs at http://dhananjaykumar.net/ . You can follow him http://twitter.com/debugmode_/ and reach him at dhananjay.25july@gmail.com
 
 
Other popularSectionarticles
Comments
By:DarshanDate Of Posted:7/21/2011 12:56:09 AM
Func<>()
can you please explain brief in func<> here used. ?? thank you
Leave a Reply
Title:
Display Name:
Email:
(not display in page for the security purphase)
Website:
Message:
Please refresh your screen using Ctrl+F5
If you can't read this number refresh your screen
Please input the anti-spam code that you can read in the image.
^ Scroll to Top