Increase LINQ query perfromance by compling it
Join the DZone community and get the full member experience.Join For Free
Each time when we fire any Linq to SQL or to any other data-source using
Entity Framework query run-time convert query to expression tree and
than into t-SQL statement. So if the query get fired number of time in
application it get converted in the expression tree to t-SQL statement
by run-time this conversion increase execution time which in turn affect
performance of the application. To avoid this cost Microsoft .net
framework introduce concept of the complied queries which allows
compilation and caching of queries for reuse.
Now, there is shopping website which list of the product by the category basically it allows filtering of product by the category. So if I have 100 no of user who logged in to system and do the filter the product by category they basically firequery to get the result they want. So this will increase the execution cost as this query get fire no of time and get converted in the expression tree and in turn get the result.
from p in db.Products where p.Category == category select p
With the help of CompiledQuery class of .net framework I can rewrite my code and it's like as below
public static Func<DataLoadTestDataContext, string, IQueryable<Product>> ProductByCategory = CompiledQuery.Compile((DataLoadTestDataContext db, string category) => from p in db.Products where p.Category == category select p);Static Variable
Static variable is used to store the so its not thread safe and global to all. Because of static variable compilation will only occur once per AppDomain and will remain cached through the rest of the application's lifetime. If you don't use the static variable query get complied each time which increase the cost and decrease the performance of the application.
Constrain and Use
Cannot use to store the queries which returns Anonymous type, because the anonymous type doesn't have any type to assign generic argument of function.
Useful when query is used heavily and you want to reuse the query, by using this way increase the performance of the application.
Where to include the code ?
Better place to include above code is partial class, its extended partial class to the partial class generated by ORM tool.More:Extenend ORM generated class
Published at DZone with permission of Pranay Rana, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.