近日在一个大型Web项目中,采用Linq to Sql替换原来的sqlcommand/sqldatareader方式来获取数据,上线后刚开始一切正常,但是随着访问量的增加,网站明显慢了很多,监测服务器CPU占用率/内存使用情况等性能指标却发现均在正常范围内,无意中在SqlServer Profier中跟踪数据库执行的sql语句时,发现有大量语句直接将整个表的数据全部提取出来了,而非仅返回分页中的当前页数据!
而这些SQL都是Linq自动翻译并最终提交到数据库的,查看了相关的代码,明明写着Skip(n).Take(m)类似的语句,为何还会生成这么“傻”的sql呢?
于是写了以下测试代码[测试环境:vs.net2008 + sqlsever2005 + win2003],最终发现是Where<TSource>(this IEnumerable<TSource> source, Func<TSource, bool> predicate);使用后,导致这个问题的产生
1.测试表T_Test:
CREATE
TABLE
[
dbo
]
.
[
T_Test
]
(
[
F_ID
]
[
int
]
IDENTITY
(
1
,
1
)
NOT
NULL
,
[
F_Name
]
[
nvarchar
]
(
50
) COLLATE Chinese_PRC_CI_AS
NULL
,
[
F_Age
]
[
int
]
NULL
,
CONSTRAINT
[
PK_T_Test
]
PRIMARY
KEY
CLUSTERED
(
[
F_ID
]
ASC
)
WITH
(IGNORE_DUP_KEY
=
OFF
)
ON
[
PRIMARY
]
)
ON
[
PRIMARY
]
录入了几条测试数据:
F_ID F_Name F_Age
15 Jimmy 20
16 Mary 14
17 Jack 30
18 张三 35
19 李四 24
2.新建一个"控制台应用程序",把T_Test拖到dbml中,Program.cs文件中输入如下代码:
1
using
System;
2
using
System.Collections.Generic;
3
using
System.Linq;
4
using
System.Linq.Expressions;
5
using
CNTVS.LINQ;
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
namespace
TestLinq
8
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
9
class Program
10![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
11
static void Main(string[] args)
12![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
13![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
14
//Expression<Func<T_Test, bool>> _Expression = PredicateBuilder.True<T_Test>();
15
//_Expression = _Expression.And(t => t.F_Age >= 20).And(t => t.F_Name.Contains("J"));
16
//var Data = GetData(_Expression, 1, 1);
17![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
18
var Data = GetData(1, 1);
19![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
20
ShowData(Data);
21
22
23
}
24![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
25![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
26
/// 用Where<T>(Expression)方式获取数据
27
/// </summary>
28
/// <param name="ExpWhere"></param>
29
/// <param name="PageSize"></param>
30
/// <param name="CurrentPageIndex"></param>
31
/// <returns></returns>
32
static List<T_Test> GetData(Expression<Func<T_Test,bool>> ExpWhere,int PageSize,int CurrentPageIndex)
33![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
34
List<T_Test> _Result = null;
35
using (DBDataContext db = new DBDataContext())
36![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
37
try
38![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
39
var query = db.T_Test.Where<T_Test>(ExpWhere.Compile()).Skip((CurrentPageIndex - 1) * PageSize).Take(PageSize);
40
_Result = query.ToList();
41
}
42![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
finally
{ db.Connection.Close(); }
43
}
44
return _Result;
45
}
46![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
47![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
48![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
49
/// 用Where(Lambda)方式获取数据
50
/// </summary>
51
/// <param name="PageSize"></param>
52
/// <param name="CurrentPageIndex"></param>
53
/// <returns></returns>
54
static List<T_Test> GetData(int PageSize, int CurrentPageIndex)
55![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
56
List<T_Test> _Result = null;
57
using (DBDataContext db = new DBDataContext())
58![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
59
try
60![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
61
var query = db.T_Test.Where(t => t.F_Age >= 20 && t.F_Name.Contains("J")).Skip((CurrentPageIndex - 1) * PageSize).Take(PageSize);
62
_Result = query.ToList();
63
}
64![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
finally
{ db.Connection.Close(); }
65
}
66
return _Result;
67
}
68
69![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
70![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
71
/// 显示数据
72
/// </summary>
73
/// <param name="Data"></param>
74
static void ShowData(List<T_Test> Data)
75![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
76
foreach (var item in Data)
77![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
78
Console.WriteLine("Name:{0}\t,Age:{1}", item.F_Name, item.F_Age.ToString());
79
}
80
Console.ReadKey();
81
}
82
}
83
}
代码很简单,找出F_Name中包含字母"J",F_Age大于20的记录,并且跳过第一个后,仅获取一条记录
注:PredicateBuilder是一个老外写的用于动态构造Expression表达式的工具类,在查询条件不确定,需要动态创建时,非常有用,完整代码如下:
Code
1
using System;
2
using System.Linq;
3
using System.Linq.Expressions;
4![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
namespace CNTVS.LINQ
6![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
{
7![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
8
public static class PredicateBuilder
9![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
10
public static Expression<Func<T, bool>> True<T> ()
11![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
12
return f => true;
13
}
14![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
15
public static Expression<Func<T, bool>> False<T> ()
16![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
17
return f => false;
18
}
19
20
public static Expression<Func<T, bool>> Or<T> (this Expression<Func<T, bool>> expr1,
21
Expression<Func<T, bool>> expr2)
22![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
23
var invokedExpr = Expression.Invoke (expr2, expr1.Parameters.Cast<Expression> ());
24
return Expression.Lambda<Func<T, bool>>
25
(Expression.Or (expr1.Body, invokedExpr), expr1.Parameters);
26
}
27
28
public static Expression<Func<T, bool>> And<T> (this Expression<Func<T, bool>> expr1,
29
Expression<Func<T, bool>> expr2)
30![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
31
var invokedExpr = Expression.Invoke (expr2, expr1.Parameters.Cast<Expression> ());
32
return Expression.Lambda<Func<T, bool>>
33
(Expression.And (expr1.Body, invokedExpr), expr1.Parameters);
34
}
35
}
36
}
以下是输出结果:
Name:Jimmy ,Age:20
用Sql Server Profiler跟踪提交到数据库的语句为:
exec
sp_executesql N
'
SELECT [t1].[F_ID], [t1].[F_Name], [t1].[F_Age]
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [t0].[F_ID], [t0].[F_Name], [t0].[F_Age]) AS [ROW_NUMBER], [t0].[F_ID], [t0].[F_Name], [t0].[F_Age]
FROM [dbo].[T_Test] AS [t0]
WHERE ([t0].[F_Age] >= @p0) AND ([t0].[F_Name] LIKE @p1)
) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p2 + 1 AND @p2 + @p3
ORDER BY [t1].[ROW_NUMBER]
'
,N
'
@p0 int,@p1 nvarchar(3),@p2 int,@p3 int
'
,
@p0
=
20
,
@p1
=
N
'
%J%
'
,
@p2
=
1
,
@p3
=
1
一切都很完美,跟我们想象的一样仅取了一条记录
3.但是,我们稍微把代码改一下:
把Main方法中的前三行注释去掉,同时把var Data = GetData(1, 1);注释掉,即
1
static
void
Main(
string
[] args)
2
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
3
Expression<Func<T_Test, bool>> _Expression = PredicateBuilder.True<T_Test>();
4
_Expression = _Expression.And(t => t.F_Age >= 20).And(t => t.F_Name.Contains("J"));
5
var Data = GetData(_Expression, 1, 1);
6![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
7
//var Data = GetData(1, 2);
8![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
9
ShowData(Data);
10
}
修改的用意在换一种方法(即Where<T>(Expression))取数据,运行后输出结果跟上一种方式完全相同,而且这种方式可以在调用方法前动态创建需要的查询条件表达式,用法更灵活,但是我跟踪到的sql语句却是:
SELECT
[
t0
]
.
[
F_ID
]
,
[
t0
]
.
[
F_Name
]
,
[
t0
]
.
[
F_Age
]
FROM
[
dbo
]
.
[
T_Test
]
AS
[
t0
]
即采用Where<T>(Expression)方式取数据时,居然先把所有数据取回来,再利用Expression来进行结果筛选以及Skip/Take操作,真是令人大跌眼镜!(或许仅仅是我水平有限,理解不了而已),这样的方式,在单表数据量很大时,性能当然极低。
恳请园子里的哪位linq达人,能解释一二?
知道了最终结果,处理方法自然也就明朗了,当时为了快速解决问题,只能把这类操作回归到最原始的SqlCommand/SqlDataReader方式读取,也许有更好的办法,欢迎大家指点。