Jun 30 2008
Linq compiled queries (2 of 2)
so in my last post, I wrote about the Linq compiled queries syntax, before diving into its use. this post is about the usage of compiled queries.
every time, a Linq Query is executed (accessed), the expressions tree/Linq query is translated into its equivalent SQL/data source query and the results are fetched. for static data and data, changing on parameter values, the repeated translation is redundant.
Assume we have the following methods:
GetAllProductsAuthorizedToBeSoldInIndia() { // Linq query to join Products and Country table on key for // India and return the expected products. }
now if we access this method N times, the LINQ query will have N SQL translations, for the same result that it will fetch. clearly this is a performance bottleneck.
also consider a parameterized method:
GetAllProductsAuthorizedToBeSoldInACountry(int countryId) { // Linq query to join Products and Country table on // CountryId and return the expected products. }
Except for the parameter value, the generated SQL query has to be re-generated every time.
To solve this, we use Linq Compiled Queries. They facilitate one time translation of the Linq query, and re-use of the translated query across multiple calls.
public class MyCompiledQueries { private static Func<MyDatabaseContext, IQueryable<Product>> ProductsAuthorizedToBeSoldInACountry = CompiledQuery.Compile( (MyDatabaseContext db, int countryId) => db.Products(p=>p.CountryId=countryId)); public static List<Product> GetAllProductsAuthorizedToBeSoldInACountry (MyDatabaseContext db, int countryId) { return MyCompiledQueries. ProductsAuthorizedToBeSoldInACountry( db, countryId).ToList<Product>(); } }
the queries are translated once, and the parameter values are substituted at runtime.
- The CompileQuery.Compile method returns us a delegate, which can be invoked repeatedly.
- The appropriate parameters need to be passed to the delegate during the invocation. (data context + query parameters)
- Because we need to reuse the delegate to actually have any performance benefit, static variables/method wrappers are preferred.
- It is a good practice to put all of your Compiled Queries into a separate class, at one place, and possibly have wrapper methods which make more business sense than entity names.
- on my box, performance increased by 220%, when I changed my queries to Compiled Queries. I was working on a test domain data application using Linq. this was just slightly less than the data reader performance, we get.
the syntax for compiled queries makes it a little tough to maintain, but the benefits are sweet n totally worth the effort.
What if I wanted to turn this into a Compiled Query
var q = from c in db.Customers
join o in db.Orders on c.CustomerID = o.CustomerID into do
from o in do.DefatultIfEmtpy()
select new { c, o }
Can that be turned into a compiled query? How do I pass an anonymous type to the Func declaration?
Thanks
yes it can be turned into a compiled query.. as follows;
var CustomerOrderCompiledQuery
= CompiledQuery.Compile((MyDataContext db) =>
from c in db.Customers
join o in db.Orders
on c.CustomerID equals o.CustomerID into do
from o in do.DefaultIfEmpty()
select new { c, o });
using (MyDataContext db = new MyDataContext())
{
foreach (var customerOrder in CustomerOrderCompiledQuery(db))
{
Console.WriteLine(customerOrder);
}
}
note that we have to declare the ‘var CustomerOrderCompiledQuery’ variable within the scope of its usage for it to remain anonymous..
this is because, we cannot pass an anonymous type to the Func declaration, since we don’t know the exact type name at compile time, and Compiled Queries are a compile time thing.
Hence we cannot delare a non-anonymous Expression Type for the Compiled Query variable in a utility class, and use it all across. We need to keep the variable to the scope of its usage.
This is the best set of posts on CompiledQueries I’ve found. Its also better than any LINQ book’s explanation. Thank you! Thank you! Thank you! Thank you! Raja!
I have a question about this sentence:
“3. Because we need to reuse the delegate to actually have any performance benefit, static variables/method wrappers are preferred.”
This does not seem logical to me. A non-static delegate can be reused too, and a developer will get the same performance benefit.
MyCompiledQueries mcq = new MyCompiledQueries;
//Non-static delegates are created on the preceding line
mcq.GetAllProductsAuthorizedToBeSoldInACountry(db, 10);
mcq.GetAllProductsAuthorizedToBeSoldInACountry(db, 31);
mcq.GetAllProductsAuthorizedToBeSoldInACountry(db, 7);
The performance benefit is present for all the invocations GetAllProductsAuthorizedToBeSoldInACountry EVEN IF IT WAS NOT DECLARED STATIC.
The “static” keyword just determines what constructs the delegate variable, when it is constructed, and how long the variable lives. If the delegate is static, then the class’s static constructor creates it the first time the class is referenced and the variable will live until the AppDomain dies. If the delegate is not static then the class’s object constructor will create it when an object (e.g. mcq above) is created and it will live until the object is disposed.
So whether or not you should declare the delegate as static depends on how you plan to use the class and exactly where and when in your code you anticipate reusing the delegate. But the mere fact that you plan to reuse the delegate is not itself a reason to make the delegate static.
It is the presence of absence of CompiledQueries.Compile() that determines whether you get a performance benefit when you reuse the delegate, not the presence or absence of “static”.
thanks for your comments Rick.
i totally agree with your explanation for Point #3. The reason i mentioned about static is that:
i prefer all my Delegates variables in a single class, which can be called multiple times across assemblies.
the reason i make it static, is solely because it will be initialized just once, for the lifetime of the application.
as you explained correctly, if we declare the variables as non-static, then all my consumers need to instantiate the object and then invoke the delegate. And there is just one translation done, till the lifetime of that object. But if 2 different classes consume my delegate, the same translation will be effectively happening twice, when they instantiate the object.
and the above explanation is just as per what you said, “declare the delegate as static depends on how you plan to use the class and exactly where and when in your code you anticipate reusing the delegate.”
i normally use the delegate across my assemblies, and hence prefer Static ones.
Buy:Benicar.Seroquel.Advair.Amoxicillin.Acomplia.Lasix.Cozaar.Nymphomax.Zetia.SleepWell.Aricept.Lipitor.Ventolin.Female Cialis.Buspar.Prozac.Lipothin.Female Pink Viagra.Zocor.Wellbutrin SR….
Buy:Arimidex.Lumigan.Zovirax.Nexium.Mega Hoodia.Human Growth Hormone.Petcam (Metacam) Oral Suspension.Retin-A.Prevacid.Zyban.Synthroid.Accutane.Prednisolone.100% Pure Okinawan Coral Calcium.Actos.Valtrex….