Linq-to-SQL性能优化提升实践

Linq-to-SQL的性能优化，根据我的个人实践和效果降序排列，如下：

 

1. 预编译 CompiledQuery （如果执行次数不止一次的话）

/// <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, int, IEnumerable<Category>>

    GetCategoriesWithMoreThanFiveProducts

    {

      get

      {

        Func<NorthwindDataContext, IEnumerable<Category>> func =

          CompiledQuery.Compile<NorthwindDataContext,  int, IEnumerable<Category>>

          ((NorthwindDataContext context, int count) => context.Categories.

            Where<Category>(cat => cat.Products.Count > count));

        return func;

      }

    }

}

调用：

using (NorthwindDataContext context = new NorthwindDataContext())

{

  QueriesUtility.GetCategoriesWithMoreThanFiveProducts(context, 5);

}

 

2. 缓存MappingSource

把默认的DataContext用自己的代替，代码如下：

using System;

using System.Data;

using System.Data.Linq;

using System.Data.Linq.Mapping;



namespace My.Company.Depart

{

    public class MyDataContext : DataContext

    {

        /// <summary>

        /// Thread-safe

        /// Cached MappingSource

        /// </summary>

        private static MappingSource _cachedMappingSource = new AttributeMappingSource(); /* Thread-safe */

        

        public MyDataContext(IDbConnection cx)

            : base(cx, _cachedMappingSource)

        {

        }

                

        public MyDataContext(string cx, bool ojectTrackingEnabled = true, bool consoleLogging = false)

            : base(cx, _cachedMappingSource)

        {

            this.ObjectTrackingEnabled = ojectTrackingEnabled;



            if (consoleLogging)

                this.Log = Console.Out;

        }



        public Table<MyTable> MyTable

        {

            get { return this.GetTable<MyTable>(); }

        }



        //blah. blah, blah......

    }

}

然后这样调用：

using (var dataContext = new MyDataContext(ConnectionStringProvider.GetDefault()))

{

  var list = dataContext.MyTable.Where(t => t.ID > 5).ToList();

  //blah. blah, blah......

}

 

3. 查询的时候关闭ObjectTrackingEnabled

dataContext.ObjectTrackingEnabled = false;

关于ObjectTrackingEnabled更详细的解释查看这个页面。

 

4. 关联表查询的时候用 LoadOptions

关于Load,LoadWith,AssociateWith查看这个页面

 

5. 其它参考资料

• Speeding up linq-to-sql
• 10 Tips to Improve your LINQ to SQL Application Performance