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;
}
}
}
/// 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);
}
{
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 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......
}
{
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. 其它参考资料
