Techniques for optimizing the performance of the linq to SQL Series

As a code generator and ORM tool, linq to SQL automatically performs many tasks for us, which can easily cause us to doubt its performance. However, there are also several tests that show that the performance of linq to SQL can be improved to 93% of the performance of ado.net datareader with good optimization.

Therefore, I have summarized the 10 Performance Improvement points of linq to SQL to optimize its query and modification performance.

1. Disable the ObjectTrackingEnabled attribute of DataContext when you do not need it.

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

Disabling this attribute will stop the management of object identification and change tracking by linq to SQL. However, it is worth noting that disabling ObjectTrackingEnabled also means setting the DeferredLoadingEnabled attribute to false. when accessing the relevant table, null is returned.

2. Do not drag all data objects to a DataContext.

A DataContext represents a unit of work, not the whole database. If there is no relationship between several database objects, or database objects that cannot be used in the program (such as log tables and batch operation tables ), it is completely unnecessary to make these objects consume memory space and the DataContext Object Tracking Service.

We recommend that you distribute all database objects to several DataContext instances based on the unit of work. You can also configure the DataContext using the same database connection through the constructor, which can also be used by the database connection pool.

3. CompiledQuery-use it if necessary

Several key steps are required to create a linq to SQL expression and convert it to the corresponding SQL statement execution process.

1) Create an Expression Tree
2) convert to SQL
3) run the SQL statement
4) retrieve data
5) convert data into objects

Obviously, when we execute the same query over and over again, the preceding steps 1) and 2) are a waste of time. You can avoid this waste by using the CompiledQuery class Compile method in the System. Data. Linq namespace.

See the following example:

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

The func variable is now a compiled query. It can be used repeatedly only once during the first runtime. Now we store it in a static class, as shown below:

/// <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;      }    }}

For how to use it, see the following code snippet:

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

4. Use DataLoadOptions. AssociateWith to set to retrieve only the required data from the database

See http://www.cnblogs.com/yukaizhao/archive/2010/05/17/linq_to_ SQL _DeferredLoadingEnabled_dataloadoption_loadwith_associatewith.html
5. enable active concurrency control only when necessary. In other words, if you do not need to, set the UpdateCheck attribute of the column to UpdateCheck. Never. This reduces unnecessary condition judgment during update and deletion.

[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();    }  }}

See the third point in the http://www.cnblogs.com/yukaizhao/archive/2010/05/13/linq_to_ SQL _1.html article.

6. Always set DataContext. Log to view the SQL statements used during the execution of linq to SQL, and analyze whether the retrieved data is enough.

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

7. Use the Attach method only when necessary.

Object appending is a very useful mechanism in linq to SQL, but nothing is free of charge. When you Attach an object in DataContext, it means that you will use this object for a while, and DataContext will mark this object as "Modify now.

But sometimes Attach is not necessary. For example, if AttachAll is used to append a set, the elements in this set do not change.

8. Pay attention to the overhead of Object ID management.

In a non-read-only DataContext, the object will be tracked all the time. Therefore, you must know that the non-intuitive situation may also cause DataContext to perform object tracking. Please refer to the following code.

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

It is very simple. It is the most basic linq query. Let's look at another statement.

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

Which of the two statements is faster? It turns out that the second statement is much faster than the first one. http://blogs.msdn.com/ricom/archive/2007/06/29/dlinq-linq-to-sql-performance-part-3.aspx

Why? Because every object queried in the first query needs to be stored in DataContext and tracked for changes that may occur to them, in the second query, if you have a new object, you do not need to trace it again. Therefore, the second statement is more efficient.

9. The Take and Skip methods are provided for Linq to SQL to use them to retrieve the required records. Do not retrieve the records in the entire table.

10. Do not abuse the CompiledQuery method. If you confirm that a query will only be executed once, do not use CompiledQuery.To use it, you also need to pay the price.

Finally, I hope these skills will be useful to you. Welcome to comments.

This article is a translation article;

See http://www.sidarok.com/web/blog/content/2008/05/02/10-tips-to-improve-your-linq-to-sql-application-performance.html

 

Additional notes related to linq to SQL:

1. Starting from CUD, how to insert, modify, and delete data using LINQ to SQL

2. query simple queries using LINQ to SQL

3. query delayed loading and immediate loading, using LoadWith and AssociateWith

4. query inner join and left outer join

5. aggregate grouping having in Linq to SQL

6. Do I have to worry about performance when optimizing the query of LINQ to SQL?