Monday, October 17, 2011

Increase Linq query performance by Compling it

Shout it kick it on DotNetKicks.com
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 fire
from p in db.Products where p.Category == category select p
query 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.

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

7 comments:

  1. hey there and thank you for your information – I have certainly picked up anything new
    from right here. I did however expertise some technical issues using this website, as I experienced
    to reload the site a lot of times previous
    to I could get it to load correctly. I had been wondering if your web hosting
    is OK? Not that I am complaining, but sluggish loading instances
    times will sometimes affect your placement in google and could damage
    your quality score if ads and marketing with Adwords.

    Anyway I am adding this RSS to my email and can look out for much more
    of your respective interesting content. Ensure
    that you update this again very soon.

    my web page; Buy Natural Cleanse

    ReplyDelete
  2. Thanks for sharing such a nice thinking, piece of writing is pleasant, thats why i have read it fully

    my site; 365 day loan

    ReplyDelete
  3. Hello, all is going fine here and ofcourse every one is sharing facts, that's actually excellent, keep up writing.

    Stop by my homepage; Green Coffee Cleanse Reviews

    ReplyDelete
  4. Hi there, I check your blogs regularly. Your story-telling style
    is witty, keep it up!

    My blog; Buy no2 maximus

    ReplyDelete
  5. hello there and thank you for your information – I have definitely picked up something new from right here.
    I did however expertise a few technical issues using this website, since I experienced to reload the web site a lot
    of times previous to I could get it to load properly.
    I had been wondering if your web host is OK? Not that I am complaining, but sluggish loading instances
    times will often affect your placement in google and could damage
    your high-quality score if ads and marketing with Adwords.
    Well I am adding this RSS to my e-mail and can look out for much more
    of your respective exciting content. Ensure that you update this again very soon.


    Also visit my website :: Wegith Loss Products

    ReplyDelete
  6. If you desire to get a great deal from this post then you have to apply
    such strategies to your won webpage.


    muscle building facts

    ReplyDelete
  7. I think that is one of the most significant info for me.
    And i'm glad reading your article. But should remark on few general issues, The site style is great, the articles is really great : D. Just right task, cheers

    Also visit my web-site garcinia cambogia reviews

    ReplyDelete