Computers, Programming, Technology, Music, Literature

.NET – Writing compiled queries for LINQ to SQL

leave a comment »

 

When writing database scripts, we prefer stored procedures over regular Sql statements for a couple of reasons. Stored procedures are precompiled, so they avoid the query compile time; on a network, passing a short string to invoke a stored procedure reduces a huge network traffic if the stored procedure had number of lines of code, and if we were using Sql statements instead of stored procedures. Other benefits are there too.

When using ORMs like LINQ to SQL, or ADO.NET Entity Framework where the Sql queries are dynamically created for you on the fly, those Sql statements have to be compiled by the Sql server every time a query is issued. And before the query is issued to the SQL server, LINQ has to parse the expression tree and spew out the SQL, which happens almost every time. In order to reduce the process of walking through the expression tree and constructing a SQL statements every time, they could be cached. To enable caching of a query, the ORMs offer a feature called compiled queries, so when we have a situation where we iterate over a query over and over again, it could be compiled and kept to improve performance.

Let’s take a look at how easy it is to write them and how to make them globally available. [The recent version Entity Framework 5.0 released with .Net framework 4.5 has compiled queries feature on by default – http://stackoverflow.com/questions/7337027/ef-4-2-impact-of-auto-compiled-linq-queries]

Below code shows you how you would normally define a DataContext and query a table.

 

        public static void PrintAllCustomers()
        {
            using (NorthwindDataContext nwc = new NorthwindDataContext())
            {
                var query = from customer in nwc.Customers
                            where customer.Country == "USA"
                            select new
                            {
                                Name = customer.ContactName,
                                Company = customer.CompanyName
                            };
                            
                foreach (var item in query)
                {
                    Console.WriteLine("Customer Name: {0}, Company: {1}", item.Name, item.Company);
                }
            }
        }

If we wanted this query to be parsed at compile time and translated to SQL statements, so LINQ to SQL or Entity Framework does not have to dynamically build the SQL query every time a query like this is executed, then we should make it a compiled query to improve performance.

The Compile method of the CompiledQuery class accepts a Func delegate with template parameters and returns a Func delegate. At the basis, what we always should know about the Compile method is, we can pass in up to 15 input parameters, and take one output parameter. If you see the query above, we are using the NorthwindDataContext variable named nwc, that we are using in our LINQ query. And the entire LINQ query returns an IQueryable of Anonymous type for us.

Below is a sample of making the same query available in a compiled form local to a method. We call the Compile method with a NorthwindDataContext as a parameter and the entire anonymous delegate (the method inside the compile method) returns an IQueryable of the an Anomynous type derived from the customer.

When we want to call the compiled query, we pass in a datacontext nwc and the rest of the code is same.


 

        public static void PrintAllCustomersWithCompiledQuery()
        {
            using (NorthwindDataContext nwc = new NorthwindDataContext())
            {
                var usaCustomers = CompiledQuery.Compile(
                    (NorthwindDataContext context) => from customer in context.Customers
                                                      where customer.Country == "USA"
                                                      select new
                                                      {
                                                          Name = customer.ContactName,
                                                          Company = customer.CompanyName
                                                      });

                foreach (var item in usaCustomers(nwc))
                {
                    Console.WriteLine("Customer Name: {0}, Company: {1}", item.Name, item.Company);
                }
            }
        }

 

This is good, most developers tend to have all the SQL queries in one cs file and refer it from there. If you follow that approach for compiled queries, and make then static instead, then the queries would be compiled once during the static variable initialization, and stays compiled untill the application’s lifetime yielding a significant boost in performance.

To demonstrate that, I am going to slightly modify the LINQ query to accept one more string paramter named country. Now the regular LINQ query we have looks like below

var customersInACountry
            = CompiledQuery.Compile((NorthwindDataContext nwc, string country) => from customer in nwc.Customers
                                                                                  where customer.Country == country
                                                                                  select customer);

 

Nothing’s changed, except that we are passing a new string parameter named country along with the NorthwindDataContext. Remember when I said the entire result of the LINQ query is an IQueryable of the returned type. In this case the type that is returned is Customer, so it is IQueryable<Customer>. And the input parameters are NorthwindDataContext  and a string. If that is clear, then what gets actually returned from the CompiledQuery.Compile method is a Func delegate. And that Func delegate in our case here, is going to accept two input parameters – one the NorthwindDataContext object, another a string; it’s output is IQueryable<Customer>.

When it was local, we used a var keyword and the type was automatically inferred to us. Now when we are talking about making a compiled query as a static member variable of a class, we don’t really have an option to use the var keyword, but to specify the exact type.

So, how would you define a Func delgate that takes NorthwindDataContext and string as input parameters, and returns an IQueryable<Customer> as an output parameter?

Func<NorthwindDataContext, string, IQueryable<Customer>>

        public static Func<NorthwindDataContext, string, IQueryable<Customer>> customersInACountry
            = CompiledQuery.Compile((NorthwindDataContext nwc, string country) => from customer in nwc.Customers
                                                                                  where customer.Country == country
                                                                                  select customer);

 

Create the above static variable in a class named CompiledQueries, then you can access it, just like we did before, but we gain performance.

        public static void PrintAllCustomersWithStaticCompiledQuery()
        {
            using (NorthwindDataContext nwc = new NorthwindDataContext())
            {
                foreach (var item in CompiledQueries.customersInACountry(nwc, "USA"))
                {
                    Console.WriteLine("Customer Name: {0}, Company: {1}", item.ContactName, item.CompanyName);
                }
            }
        }

 

Download the complete source code  – https://renouncedthoughts.files.wordpress.com/2013/02/linqtosqlcompiledqueries1.doc

The word document contains embedded zip file with the original Visual Studio solution.

Related Read: http://blogs.msdn.com/b/appfabriccat/archive/2010/08/06/potential-performance-issues-with-compiled-linq-query-re-compiles.aspx

Advertisements

Written by gmaran23

February 21, 2013 at 7:03 pm

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

%d bloggers like this: