10 Tips to Improve your LINQ to SQL Application Performance

Hey there, back again. In my first post about LINQ I tried to provide a brief(okay, bit detailed) introduction for those who want to get involved with LINQ to SQL. In that post I promised to write about a basic integration of WCF and LINQ to SQL working together, but this is not that post.

Since LINQ to SQL is a code generator and an ORM and it offers a lot of things, it is normal to be suspicious about performance of it. These are right up to a certain point as LINQ comes with its own penalties. But there are several benchmarks showing that DLINQ brings us up to %93 of the ADO.NET SQL DataReader performance if optimizations are done correctly.

Hence I summed up 10 important points for me that needs to be considered during tuning your LINQ to SQL’s data retrieval and data modifying process:

1 – Turn off ObjectTrackingEnabled Property of Data Context If Not Necessary

If you are trying only to retrieve data as read only, and not modifying anything, you don’t need object tracking. So turn it off using it like in the example below:

using (NorthwindDataContext context = new NorthwindDataContext())
{
  context.ObjectTrackingEnabled = false;
}

This will allow you to turn off the unnecessary identity management of the objects – hence Data Context will not have to store them because it will be sure that there will be no change statements to generate.

2 – Do NOT Dump All Your DB Objects into One Single DataContext

DataContext represents a single unit of work, not all your database. If you have several database objects that are not connected, or they are not used at all (log tables, objects used by batch processes,etc..). These objects just unnecessarily consume space in the memory hence increasing the identity management and object tracking costs in CUD engine of the DataContext.

Instead think of separating your workspace into several DataContexts where each one represents a single unit of work associated with it. You can still configure them to use the same connection via its constructors to not to loose the benefit of connection pooling.

3 – Use CompiledQuery Wherever Needed

When creating and executing your query, there are several steps for generating the appropriate SQL from the expression, just to name some important of them:

  1. Create expression tree

  2. Convert it to SQL

  3. Run the query

  4. Retrieve the data

  5. Convert it to the objects

As you may notice, when you are using the same query over and over, hence first and second steps are just wasting time. This is where this tiny class in System.Data.Linq namespace achieves a lot. With CompiledQuery, you compile your query once and store it somewhere for later usage. This is achieved by static CompiledQuery.Compile method.

Below is a Code Snippet for an example usage:

Func<NorthwindDataContext, IEnumerable<Category>> func =
   CompiledQuery.Compile<NorthwindDataContext, IEnumerable<Category>>
   ((NorthwindDataContext context) => context.Categories.
      Where<Category>(cat => cat.Products.Count > 5));


And now, “func” is my compiled query. It will only be compiled once when it is first run. We can now store it in a static utility class as follows :

/// <summary>
/// Utility class to store compiled queries
/// </summary>
public static class QueriesUtility
{
  /// <summary>
  /// Gets the query that returns categories with more than five products.
  /// </summary>
  /// <value>The query containing categories with more than five products.</value>
  public static Func<NorthwindDataContext, IEnumerable<Category>>
    GetCategoriesWithMoreThanFiveProducts
    {
      get
      {
        Func<NorthwindDataContext, IEnumerable<Category>> func =
          CompiledQuery.Compile<NorthwindDataContext, IEnumerable<Category>>
          ((NorthwindDataContext context) => context.Categories.
            Where<Category>(cat => cat.Products.Count > 5));
        return func;
      }
    }
}

And we can use this compiled query (since it is now a nothing but a strongly typed function for us) very easily as follows:

using (NorthwindDataContext context = new NorthwindDataContext())
{
  QueriesUtility.GetCategoriesWithMoreThanFiveProducts(context);
}

Storing and using it in this way also reduces the cost of doing a virtual call that’s done each time you access the collection – actually it is decreased to 1 call. If you don’t call the query don’t worry about compilation too, since it will be compiled whenever the query is first executed.

4 – Filter Data Down to What You Need Using DataLoadOptions.AssociateWith

When we retrieve data with Load or LoadWith we are assuming that we want to retrieve all the associated data those are bound with the primary key (and object id). But in most cases we likely need additional filtering to this. Here is where DataLoadOptions.AssociateWith generic method comes very handy. This method takes the criteria to load the data as a parameter and applies it to the query – so you get only the data that you need.

The following code below associates and retrieves the categories only with continuing products:

using (NorthwindDataContext context = new NorthwindDataContext())
{
  DataLoadOptions options = new DataLoadOptions();
  options.AssociateWith<Category>(cat=> cat.Products.Where<Product>(prod => !prod.Discontinued));
  context.LoadOptions = options;
}

5 – Turn Optimistic Concurrency Off Unless You Need It

LINQ to SQL comes with out of the box Optimistic Concurrency support with SQL timestamp columns which are mapped to Binary type. You can turn this feature on and off in both mapping file and attributes for the properties. If your application can afford running on “last update wins” basis, then doing an extra update check is just a waste.

UpdateCheck.Never is used to turn optimistic concurrency off in LINQ to SQL.

Here is an example of turning optimistic concurrency off implemented as attribute level mapping:

[Column(Storage=“_Description”, DbType=“NText”,
            UpdateCheck=UpdateCheck.Never)]
public string Description
{
  get
  {
    return this._Description;
  }
  set
  {
    if ((this._Description != value))
    {
      this.OnDescriptionChanging(value);
      this.SendPropertyChanging();
      this._Description = value;
      this.SendPropertyChanged(“Description”);
      this.OnDescriptionChanged();
    }
  }
}

6 – Constantly Monitor Queries Generated by the DataContext and Analyze the Data You Retrieve

As your query is generated on the fly, there is this possibility that you may not be aware of additional columns or extra data that is retrieved behind the scenes. Use Data Context’s Log property to be able to see what SQL are being run by the Data Context. An example is as follows:

using (NorthwindDataContext context = new NorthwindDataContext())
{
  context.Log = Console.Out;
}


Using this snippet while debugging you can see the generated SQL statements in the Output Window in Visual Studio and spot performance leaks by analyzing them. Don’t forget to comment that line out for production systems as it may create a bit of an overhead. (Wouldn’t it be great if this was configurable in the config file?)

To see your DLINQ expressions in a SQL statement manner one can use SQL Query Visualizer which needs to be installed separately from Visual Studio 2008.

7 – Avoid Unnecessary Attaches to Tables in the Context

Since Object Tracking is a great mechanism, nothing comes for free. When you  Attach an object to your context, you mean that this object was disconnected for a while and now you now want to get it back in the game. DataContext then marks it as an object that potentially will change - and this is just fine when you really intent to do that.

But there might be some circumstances that aren’t very obvious, and may lead you to attach objects that arent changed. One of such cases is doing an AttachAll for collections and not checking if the object is changed or not. For a better performance, you should check that if you are attaching ONLY the objects in the collection those are changed.

I will provide a sample code for this soon.

8 – Be Careful of Entity Identity Management Overhead

During working with a non-read only context, the objects are still being tracked – so be aware that non intuitive scenarios this can cause while you proceed. Consider the following DLINQ code:

using (NorthwindDataContext context = new NorthwindDataContext())
{
  var a = from c in context.Categories
  select c;
}

Very plain, basic DLINQ isn’t it? That’s true; there doesn’t seem any bad thing in the above code. Now let’s see the code below:

using (NorthwindDataContext context = new NorthwindDataContext())
{
  var a = from c in context.Categories
  select new Category
  {
    CategoryID = c.CategoryID,
    CategoryName = c.CategoryName,
    Description = c.Description
  };
}

The intuition is to expect that the second query will work slower than the first one, which is WRONG. It is actually much faster than the first one.

The reason for this is in the first query, for each row the objects need to be stored, since there is a possibility that you still can change them. But in the 2nd one, you are throwing that object away and creating a new one, which is more efficient.

9 – Retrieve Only the Number of Records You Need

When you are binding to a data grid, and doing paging – consider the easy to use methods that LINQ to SQL provides. These are mainly Take and Skip methods. The code snippet involves a method which retrieves enough products for a ListView with paging enabled:

/// <summary>
/// Gets the products page by page.
/// </summary>
/// <param name=”startingPageIndex”>Index of the starting page.</param>
/// <param name=”pageSize”>Size of the page.</param>
/// <returns>The list of products in the specified page</returns>
private IList<Product> GetProducts(int startingPageIndex, int pageSize)
{
  using (NorthwindDataContext context = new NorthwindDataContext())
  {
    return context.Products
           .Take<Product>(pageSize)
           .Skip<Product>(startingPageIndex * pageSize)
           .ToList<Product>();
   }
}

10 – Don’t Misuse CompiledQuery

I can hear you saying “What? Are you kiddin’ me? How can such a class like this be misused?”

Well, as it applies to all optimization LINQ to SQL is no exception:

“Premature optimization is root all of evil” – Donald Knuth

If you are using CompiledQuery make sure that you are using it more than once as it is more costly than normal querying for the first time. But why?

That’s because the resulting function coming as a CompiledQuery is an object, having the SQL statement and the delegate to apply it. It is not compiled like the way regular expressions are compiled. And your delegate has the ability to replace the variables (or parameters) in the resulting query.

That’s the end folks, I hope you’ll enjoy these tips while programming with LINQ to SQL. Any comments or questions via sidarok at sidarok dot com or here to this post are welcome.

kick it on DotNetKicks.com

Technorati Tags: LINQ,SQL,Performance,.NET 3.5


Share it on: These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Mixx
  • Google
  • Blogosphere News
  • e-mail
  • YahooMyWeb
  • DotNetKicks
  • DZone

70 Responses so far »

  1. 1

    Javier Crespo said,

    May 2, 2008 @ 3:47 pm

    Awesome dude! Great tips, this will surely become a reference for Linq to sql programming. The tip that I liked the most is the 8th though… Very subtle, I’d never have imagined!

  2. 2

    Visual Studio Links #23 : Visual Studio Hacks said,

    May 2, 2008 @ 5:21 pm

    […] Sidar Ok posted 10 Tips to Improve your LINQ to SQL Application Performance […]

  3. 3

    Luke Foust said,

    May 2, 2008 @ 8:19 pm

    Great Article. Thanks. Just so you know, your site showed up as a “Suspicious Website” in IE7 for me. Do you know why this is?

  4. 4

    Chris Miller said,

    May 2, 2008 @ 10:39 pm

    Thanks for the great tips!

  5. 5

    Sidar Ok said,

    May 2, 2008 @ 10:53 pm

    Hey Luke, thanks indeed. It never happened to me, I sent you a facebook message to get more info to dig into the problem more.

    Thanks Chris, hope they will work good for you !

  6. 6

    Interesting Finds: 2008.05.03 - gOODiDEA.NET said,

    May 3, 2008 @ 7:54 am

    […] 10 Tips to Improve your LINQ to SQL Application Performance […]

  7. 7

    Guy Harwood said,

    May 3, 2008 @ 11:18 am

    excellent article.

    i shall be mentioning this in my next blog post!

  8. 8

    freek said,

    May 3, 2008 @ 12:22 pm

    Great read. One thing to note actually, is that when you turn DeferredLoadingEnabled off, you will actually get better performance, since it will NOT eager load child objects. All loading will have to be configured by setting the DataLoadOptions.

  9. 9

    Jason Alexandrian said,

    May 3, 2008 @ 6:28 pm

    Just to add on to tip #6, what I do is use a compiler directive as so:

    using (NorthwindDataContext context = new NorthwindDataContext())
    {
    #if DEBUG
    context.Log = Console.Out;
    #endif
    }

    And that way I don’t have to comment out the log line.

    Thanks for the good info.

  10. 10

    Dew Drop - May 3, 2008 | Alvin Ashcraft's Morning Dew said,

    May 3, 2008 @ 6:49 pm

    […] 10 Tips to Improve Your LINQ to SQL Application Performance (Sidar Ok) […]

  11. 11

    Sidar Ok said,

    May 4, 2008 @ 4:04 pm

    Hi Guy, thanks a lot, I will be looking forward to your post !

    Hey freek : good spot you have a point there. There is no hidden eager loading,though I had set up some benchmark tests to see if turning that on/off makes a difference - no luck so far. But I will dig into this one more and can call that tip off, so please bear with me :)

  12. 12

    Sidar Ok said,

    May 4, 2008 @ 4:10 pm

    Hi Jason, good one thanks ! If we start optimizing, I think rather than macro values or doing it for development, the more suited one would be turning it on and off depending on an application configuration value. That would be better if we want to see the queries in release mode (which is the widely usual mode that actual performance tests are performed) and even during the app runs. This of course assumes that output is done somewhere we can see rather than console ;)

    But even a better solution would be injecting the dependency of the Log property - that would be the part making the best sense, because you would be able to turn off, on and change the output stream to what you want without compiling. imho DataContext should have been more configurable - and with dependency injection we can achieve it. I am willing to write a post to cover this issue soon.

  13. 13

    Improve LINQ to SQL performance « Perry Stathopoulos said,

    May 5, 2008 @ 3:11 pm

    […] following blog post describes 10 tips from improving LINQ to SQL performance, highly recommended for anyone using LINQ to […]

  14. 14

    Ryan Davis said,

    May 5, 2008 @ 10:00 pm

    Honest, I did not know how I raised my kids properly before this article. Who wants an Entity Framework-flavored yogurt drink?

  15. 15

    Wöchentliche Rundablage: ASP.NET MVC, Live Mesh, Silverlight, .NET… | Code-Inside Blog said,

    May 5, 2008 @ 11:16 pm

    […] 10 Tips to Improve your LINQ to SQL Application Performance […]

  16. 16

    Sidar Ok said,

    May 6, 2008 @ 2:31 am

    Hey Freek, back again. I called the second one off - thanks very much for the feedback !

    Hey Ryan, yes this is deadly. But wait until I publish LINQ 2 Kids and then let’s see ! :)

    imagine this:

    kid => {kid.GoToBed();kid.Drink(new YoghurtDrink(DrinkType.EntityFramework));}

  17. 17

    Ashrafur Rahaman said,

    June 10, 2008 @ 12:12 pm

    Nice one. Thanks a lot to share it.
    Help to improve many things of one of my application.

  18. 18

    Sidar Ok said,

    June 10, 2008 @ 4:49 pm

    Good to see that they worked out well. Thanks for reading it.

  19. 19

    Singleton said,

    June 26, 2008 @ 7:44 pm

    Nice article. Your point #8 is invalid with VS 2008, as explicit construction in query is not allowed.

  20. 20

    The 20 most kicked “top-10 stories” for Developers « .Net Braindrops said,

    June 27, 2008 @ 8:45 am

    […] 10 Tips to improve your LINQ TO SQL Application Performance (36 kicks) DNK: Sidar gives performance tips on developing linq 2 SQL […]

  21. 21

    Sidar Ok said,

    June 27, 2008 @ 2:45 pm

    Hi Singleton,

    it is valid indeed and works in my machine, as the reference states it works in many of the others. What is the error you are getting while trying to run it, could you post it here or email to sidarok@sidarok.com ?

    Thanks.

    Sidar

  22. 22

    RambleJam » New to LINQ? said,

    June 28, 2008 @ 2:58 pm

    […] Just found this really useful collection of byte sized tidbits to improve the performance of your usage of the data context.

  23. 23

    Localizing Linq to SQL Entities | Sidar Ok Technical Blog ! said,

    August 18, 2008 @ 2:00 am

    […] original post, Ayende uses filters in NHibernate. In Linq to SQL we don’t have filters but as mentioned before, we have Load Options to give a criteria and reduce the amount of data we […]

  24. 24

    log.itto.be » Blog Archive » links for 2008-08-22 said,

    August 22, 2008 @ 8:51 pm

    […] 10 Tips to Improve your LINQ to SQL Application Performance | Sidar Ok Technical Blog ! (tags: development performance linq) […]

  25. 25

    Rajasekhar Bathula said,

    October 10, 2008 @ 5:36 pm

    Excellent!!!!
    Very very useful.

  26. 26

    Panfila said,

    October 23, 2008 @ 2:58 pm

    Well said.

  27. 27

    Jignesh said,

    December 3, 2008 @ 4:29 pm

    I am using LinqDataSource and binding it with dropdownbox.

    how to i use this command on it –> “context.ObjectTrackingEnabled = false; ”

    Regards

  28. 28

    Sidar Ok said,

    December 4, 2008 @ 1:57 pm

    @Jignesh,

    As far as I can recall in your mark up if you say EnableUpdate=”false” and EnableDelete=”false” then it should do that automatically for you.

    Another possible solution is to do that in the definition of your data context which is bound to L2S Data Source control (By Context name property), but beware that will effect everywhere that uses this context.

  29. 29

    Guy Noir said,

    February 12, 2009 @ 2:15 am

    Hi, am new to .Net/LINQ, but loving what I see, especially having handrolled DB classes in C++. My question is: how can you use the DataLoadOptions w/ a compiled query?

    eg:
    =======================
    NorthwindDataContext db = new NorthwindDataContext();

    DataLoadOptions options = new DataLoadOptions();//
    options.LoadWith( c => c.Orders );//also load the orders
    db.DeferredLoadingEnabled = false;
    db.LoadOptions = options;

    var q = from c in db.Customers
    where c.City == “Happy Town”
    select c;
    ================

    Basically, would like to turn the above into a compiled query in the style of Recommendation #3, w/ a parameter containing “happy town” or whatever.

  30. 30

    Guy Noir said,

    February 16, 2009 @ 4:05 am

    I was able to answer my own question & will post it here for others such as myself:
    Basically make the DataLoadOptions a private static member w/ an init func, make a private static Func as shown in #3 above, then make a public static wrapper func.

    public static class QueryUtilities{

    private static DataLoadOptions _options = (new Func(() =>{ //var opts = new DataLoadOptions, call opts.LoadWith,etc; return opts; }))();

    };

    private static Func> _CustomerByCity = CompiledQuery.Compile(…);

    public static IQueryable CustomerByCity(MyDataContext dc, string city)
    {
    dc.LoadOptions = _options;
    return _CustomerByCity(dc, city);
    }
    };//end QueryUtilities

    Then, somewhere in a secure non-disclosed location:

    MyDataContext dc = new MyDataContext();
    var q = QueryUtilities.CustomerByCity(dc, “Happy Town”);

    or something like that..

  31. 31

    Sean said,

    February 18, 2009 @ 7:46 am

    Any chance to do the same Tips for Entity Framework?

  32. 32

    Sidar Ok said,

    February 19, 2009 @ 12:05 am

    @Guy

    Thanks a lot for sharing :)

    @Sean

    My experience with EF has been hectic, and I currently don’t encourage usage of it for several reasons (see vote of no confidence for more on this). But if you or anybody else want to do it, and want to take this list as a starting point, that’s fine by me.

  33. 33

    murat said,

    April 15, 2009 @ 12:17 pm

    L2S is dead

  34. 34

    Jomit said,

    April 17, 2009 @ 7:35 am

    Great Post !!!

    Just marked this for the link of month list,

    Cheers,
    Jomit

  35. 35

    Kyo said,

    May 31, 2009 @ 5:32 pm

    Very useful !
    But it cause some problem in Tip #8

    var a = from c in context.Categories
    select new Category
    {
    CategoryID = c.CategoryID,
    CategoryName = c.CategoryName,
    Description = c.Description
    };
    Raise an exception Explicit construction of entity type ‘###’ in query is not allowed . Because ‘Category’ is a Entity was generated by LinqToSQL class. Can someone solved it ?
    Thanks again !

  36. 36

    Justin said,

    June 17, 2009 @ 6:59 pm

    I can’t get Tip#8 to work either, i’m having the same problem as Kyo.

  37. 37

    Martin said,

    July 21, 2009 @ 8:33 am

    In Item 3, surely GetCategoriesWithMoreThanFiveProducts is still compiled every time you call it? If you really want it to return the same instance every time, you’d need to use a Singleton pattern.
    That is, unless the CompiledQuery.Compile has the responsibility of storing and retrieving the Compiled instances. I can’t find any documentation that seems to suggest that’s what it does.

    It’s the equivalent of creating a Compiled Regex, but not caching it.

  38. 38

    Carlos Porras (El Salvador) said,

    November 26, 2009 @ 4:50 pm

    Hi,

    I’ve been reading about Dynamic Data websites and I have found that you can’t use more than a single DataContext in the global.asax file.

    So, if you have decided to use as many LINQ to SQL classes as the best alternative for not overloading your system with a huge file (my project has more than a 140 tables until now) as it is recommended in your blog by means of making shorter pieces of code, avoiding the problems you could have by instantiating unnecessary classes when they are not required.

    As I have tried, you can’t have both. You can’t have many DataContexts but not inside a Dynamic Project.

    My question is: How can you combine both techniques as to produce the initial and valuable scaffolding CRUD design provided by the ASP.Net 3.5 that had help me a lot in debugging and building a proper ERD with the right primary and foreign keys. I have been using extensively an ERD based upon the use of referential integrity at its best as to produce all filtering that is required by the Dynamic Data design.

    On my side: If I were to have more than one DataContext inside a DynamicData website, how can I configure the global.aspx files as to use any of them depending on what DataContext I am intereste to use (different DataContext wouldn’t elt you instantiate teh same object into different contexts. It will give you an error message at compile time)

    As I said: it could be very convenient having those two techniques used at once but if you have one you can’t have the other.

    That’s my point.

    I’m I correct

  39. 39

    Florim Maxhuni said,

    December 9, 2009 @ 9:15 am

    What about Singel DataContext for request in WebProject i thing this will also improve performance.

  40. 40

    Oleg Bessonov said,

    December 19, 2009 @ 10:20 pm

    I have my two cents as a small fix of 9th paging tip. Just about order Skip and Take calls. The right order is:

    return context.Products
    .Skip(startingPageIndex * pageSize)
    .Take(pageSize)
    .ToList();

    Thanks for your tips.

  41. 41

    Rik said,

    December 23, 2009 @ 5:41 pm

    Regarding #5 (turning off Optimistic Concurrency). If the SQL CE database is only being used in a read-only scenario would you still need to set UpdateCheck to Never? I would think since there are no UPDATEs or INSERTs that this attribute wouldn’t really factor into performance. Am I correct about this?

  42. 42

    Dev Links « Blogosphere said,

    December 27, 2009 @ 1:20 am

    […] 10 Tips to Improve your LINQ to SQL Application Performance […]

  43. 43

    tvPrasad said,

    August 11, 2010 @ 3:55 pm

    Nice Article with Good list.

  44. 44

    Juan Palma said,

    August 24, 2010 @ 1:18 pm

    Hello Sidar Ok,

    This should be the 11 tips:

    11) Implement INotifyChanging and INotifyChanged to better the update’s performance.

    LINQ to SQL comes with out of the box Optimistic Concurrency support with SQL timestamp columns which are mapped to Binary type. You can turn this feature on and off in both mapping file and attributes for the properties. If your application can afford running on “last update wins” basis, then doing an extra update check is just a waste.

    I hope that help

  45. 45

    Experts Comment said,

    September 5, 2010 @ 6:39 am

    Good information..
    In the search process i found a link that provides a good summary of the best practice that one should follow while working with LINQ to SQL

    http://www.a2zmenu.com/LINQ/LINQ%20to%20SQL%20Best%20Practice.aspx

  46. 46

    Experts Comment said,

    September 11, 2010 @ 2:05 pm

    LINQ to SQL CompiledQuery is good option, but things that needs to be taken into consideration while using the same is that first time when it is called it takes more time than a routine query would take. Refer to below link…

    http://www.a2zmenu.com/LINQ/LINQ%20to%20SQL%20CompiledQuery.aspx

  47. 47

    Sidar Ok said,

    September 21, 2010 @ 9:07 am

    @Juan

    Thanks for the 11th tip !

    @Experts

    I don’t reccommend the usage of CompiledQuery if a query isn’t foreseen to be used more than once.

  48. 48

    Properties without UpdateCheck=”Always” have no value when accessed through LinqDataSource event handler | DeveloperQuestion.com said,

    October 5, 2010 @ 12:19 pm

    […] Tags: event, optimistic concurrency, stack overflow, Value I have a practise of setting UpdateCheck=”Never” for most plateau properties in the DBML file. This is because I don’t want the extra overhead from concurrency checking (see Turn Optimistic Concurrency Off Unless You Need It) […]

  49. 49

    Mark Andrew said,

    November 3, 2010 @ 10:55 am

    Really helpful ideas! I used a combination of the tricks to help improve the performance of 6 LINQ queries I was working on.

    One of the biggest improvements came from adding a “.ToList()” to the end of each of my LINQ queries before continuing to process them! I think it give me a performance increase of AT LEAST 50% which was excellent!

    Thanks again,

    Mark

  50. 50

    Agame said,

    November 11, 2010 @ 7:20 pm

    Great list of Linq tips - thank you!

  51. 51

    Sidar Ok said,

    January 8, 2011 @ 1:54 pm

    Very happy to see that this article is still useful to some after 3 years that it has been written and the product is nearly completely castrated by Microsoft to support EF.

  52. 52

    Sunil P said,

    May 25, 2011 @ 4:38 am

    Excellent article… some tips really helped to improve performance of my app

  53. 53

    Hugo said,

    July 13, 2011 @ 7:27 am

    Awesomeness ! Bookmark that!

  54. 54

    mojtaba pourmirzaei said,

    August 11, 2011 @ 7:53 am

    thanks item 4 and 7 solve my problems
    Iran

  55. 55

    mak said,

    September 1, 2011 @ 2:40 pm

    great dude..
    thanks ….
    it help great…..

  56. 56

    Sunil K Patil,Indore said,

    September 22, 2011 @ 3:43 pm

    Excellent job done.

  57. 57

    Kevin Wils said,

    January 5, 2012 @ 9:58 am

    Hi,

    Is it a good thing to make use a singleton for a linq context?
    Or is this otiose?
    For example:

    mmbMainDataContext mainCtx;

    private mmbMainDataContext MainCtx
    {
    get
    {
    if (mainCtx == null)
    mainCtx = new mmbMainDataContext();

    return mainCtx;
    }
    }

    protected void btn_generateSiteMaps_Click(object sender, EventArgs e)
    {
    using (MainCtx)
    {
    foreach (var item in MainCtx.tbl_websites)
    {
    generateSitemap(item.dealerCode, “http://” + item.dnsName, item.languages);
    }
    }
    }

    Thanks for the interesting tips!

  58. 58

    Sidar Ok said,

    January 24, 2012 @ 3:34 pm

    No it is not a good practice to use singleton for Linq2SqL because it is not very expensive to create and dispose the data context yet the data context has to be thought as a unit of work - do what you have to do and dispose it.

  59. 59

    Develepoment | Pearltrees said,

    March 19, 2012 @ 5:23 pm

    […] 10 Tips to Improve your LINQ to SQL Application Performance | Sidar Ok Technical Blog ! 7 – Avoid Unnecessary Attaches to Tables in the Context Since Object Tracking is a great mechanism, nothing comes for free. When you Attach an object to your context, you mean that this object was disconnected for a while and now you now want to get it back in the game. DataContext then marks it as an object that potentially will change - and this is just fine when you really intent to do that. To see your DLINQ expressions in a SQL statement manner one can use SQL Query Visualizer which needs to be installed separately from Visual Studio 2008. But there might be some circumstances that aren’t very obvious, and may lead you to attach objects that arent changed. […]

  60. 60

    Tối ưu hóa LinQ | If you smile with the life, it will smile with you! said,

    April 6, 2012 @ 5:37 am

    […] http://www.sidarok.com/web/blog/content/2008/05/02/10-tips-to-improve-your-linq-to-sql-application-p… […]

  61. 61

    Bulk INSERT to SQL Azure using LINQ to SQL | Matt McCormick said,

    May 28, 2012 @ 11:41 pm

    […] I even tried batching my queries, eliminating the transaction, and increasing the timeout window, among other things. This didn’t work and I needed another […]

  62. 62

    Geoff Hirst said,

    June 8, 2012 @ 12:35 pm

    Hi, nice examples. Just one thing, CompiledQuery.Compile, do you have an example of it working in VB.NET? I have no problem with it in C# but VB.NET just throws out the arguments.

    thanks

  63. 63

    Sidar Ok said,

    June 8, 2012 @ 1:21 pm

    Hello Geoff, no I don’t have them in VB.NET but you can convert them easily , the languages don’t do mistakes people do :)

  64. 64

    Houman Mas said,

    August 5, 2012 @ 11:22 pm

    thanks a lot dude! ;)

  65. 65

    narasimha said,

    December 22, 2012 @ 11:35 am

    Thanks a lot dude nice article.

  66. 66

    Mickey Ngo said,

    January 10, 2013 @ 5:47 am

    Thank you very much, Sidar Ok!
    your article is very great.
    I have 2 questions:
    1. DataLoadOptions.AssociateWith: it would be good if we replace where clause with this.
    ex:
    DataLoadOptions options = new DataLoadOptions();
    options.AssociateWith(m=> m.IsDeleted == false);
    context.LoadOptions = options;
    from u in context.Users select u;
    instead of
    context.User.where(m=>m.IsDeleted == false)

    2. Be Careful of Entity Identity Management Overhead: if i set context.ObjectTrackingEnabled = false; is his problem resolved?

  67. 67

    Sidar Ok said,

    January 10, 2013 @ 8:37 am

    Hello Mickey,

    Thanks for the nice words.

    1 - Where is not the same as asscoiatewith. AssocitatWith will load only the assoicated data for all the context where “where” will filter only for that context.

    2 - Yes, but only if you get the data read-only.

  68. 68

    Candy Chiu said,

    May 6, 2013 @ 1:58 pm

    Hi,

    Not sure if anyone resolved #8 “Explicit construction of entity type ### in query is not allowed”. According to this link (http://stackoverflow.com/questions/2952856/explicit-construction-of-entity-type-myclass-in-query-is-not-allowed), #8 is not supposed to work.

    Candy

  69. 69

    Vishwajeet said,

    May 20, 2013 @ 8:47 am

    The concepts and explanation are good.

    Just one question…..

    The point no 8 about Entity Identity Management Overhead….can you relate this with the String Vs StringBuilder?
    The StringBuilder is considered as the optimized way for text handling operations.

  70. 70

    Sidar Ok said,

    May 20, 2013 @ 9:01 am

    @Candy

    You can create an anonymous type instead of explicitly typing the type name. The principle is the same.

    @Vishwajeet

    SB is a different class, how is it going to help LINQ Performance? There is no text operation in point #8 ?

Comment RSS · TrackBack URI

Say your words

You must be logged in to post a comment.