又一次Linq查询优化
项目是老项目的维护，已经三年的东西了，虽然这期间维护的时候尝试过修改查询方式以提高效率，但是领导说怕出问题，所以一直搁置了，这次，领导打算全部进行优化，做一次大的调整，我就顺便记录一下之前尝试的优化方式与这一次优化的总结。
2009年刚出现LINQ TO SQL的时候，我们这个采购的项目就采用了这个东西，当时感觉，这个东西太好用了，而且也因为之前一直做JAVA，算是.Net的初学者，所以感觉微软的东西很容易上手，好方便啊。所以边学边就写了那个项目，做完后一直用着，有过几次修改，但没大大变动，直到后来服务器升级，我又看了一次，发现查询的时候是在是慢的可以，一个结果只有1000+的数据，查询到显示竟然用了15秒以上，这真的是难以忍受。
此时对LINQ TO SQL也有了一定的了解，也知道了一些方法如何优化，但是，受众们没提出我们也懒的修改，也怕修改出问题，就放着不动，现在他们也说太慢了，我们也觉得，是时候修改一下了，毕竟那套东西实在是难看之极，不是说界面，而是后台实现的代码，这次先拿一个页面开刀。
这个页面是采购单独页面，关联了一个项目表和一个价格表，查询的时候用到了项目表用来显示项目名称，当年用了这样的查询方式：
1 var matches = from t in db.tRequestInfo
2 where
3 (pmbool ? t.tProjectInfo.PMUserID == pmid : true) &&
4 (ProjectNameText.Text != "" && ProjectNameText.Text != null ? t.tProjectInfo.ProjectName.Contains(ProjectNameText.Text) : true) &&
5 (RequestNoText.Text != "" && RequestNoText.Text != null ? t.RequestNo.Contains(RequestNoText.Text) : true) &&
6 (FillDateChk.Checked ? (Convert.ToDateTime(t.FillDate.Trim()).CompareTo(Convert.ToDateTime(FillDateStart.Text.Trim())) >= 0 && Convert.ToDateTime(t.FillDate.Trim()).CompareTo(Convert.ToDateTime(FillDateEnd.Text.Trim())) <= 0) : true) &&
7 (CheckStatChk.Checked ? t.State == false : true)
8 group t by new
9 {
10 t.RequestNo
11 }
12 into p
13 select new
14 {
15 OrderID = p.First().ID,
16 RequestNo = p.Key.RequestNo,
17 Url = HttpUtility.UrlEncode(p.Key.RequestNo),
18 ProjectName = p.First().tProjectInfo.ProjectName,
19 FillDate = p.First().FillDate,
20 State = p.First().State ? "<font color=grey>未生成价格确认单</font>" : "<font color=green>已生成价格确认单</font>"
21 };
22 if (matches.Count() > 0)
23 {
24 Msg.Text = "总共有【<font color=\"red\">" + matches.Count() + "</font>】条记录。";
25 }
26 else
27 {
28 Msg.Text = "未查询到任何数据，请确认查询条件是否正确。";
29 }
30 RequestData.DataSource = matches.OrderByDescending(t => t.OrderID);
31 RequestData.DataBind();
上面让我觉得最BT的是时间比较多方式，因为那个时候用了第三方的控件，而且当时的领导把数据库中日期字段设计成了nVarchar类型的，所以，我就找出了那么个比较大方式，当时就这样实现了查询功能。其实就连上面的语句都还是后来修改过了的。在绑定的时候使用OrderByDescending方式排序是因为在上上面LINQ语句中的排序会因为GroupBy的关系失效。
上面的查询我保存成了一个方法ShowRequestList()来调用，protected void Page_Load(object sender, EventArgs e)中调用了此方法用来默认显示。前台使用的GridView显示的数据，因为是.Net3.5的项目，所以我想，大多数都是使用这样的方式吧。
后来发现慢的要死，就找问题出在哪，后来了解到，是因为matches.Count()方法的调用，因为每调用一次，就到数据库中查询一次，所以上面的语句，调用了两次的Count方法，下面还排序然后绑定了一次，所以，查询了三次。
这样的话肯定会影响速度啊。领导前天告诉我们，使用微软的性能测试工具发现慢的问题还出现在调用DataBind方法的时候，我查了一下，因为调用DataBind方法其实后是将结果集使用IEnumeable方式循环，调用MoveNext来绑定到前台的GridView。没调用一次MoveNext也会进行一次查询。但是这个不好控制。所以，经过这样的查询方式，慢是必须的了。
那么我进行了第一次的优化，众所周知，SQL中Group BY语句的效率是非常低的，LINQ中也一样，所以能不用尽量不要用，那么我就不用；我想到了去重，虽然字段很多，但是我只用到了RequestNo、ProjectName、FillDate、和State这几个字段，OrderID是用来排序的，前台的数据显示并没有用到，所以去掉OrderID，只剩下这四个字段，那么我就是用去重功能，经过验证，确实可行：
1 using (PMSDBDataContext db = new PMSDBDataContext())
2 {
3 var matches = (from t in db.tRequestInfo
4 where
5 (pmbool ? t.tProjectInfo.PMUserID == pmid : true) &&
6 (ProjectNameText.Text != "" && ProjectNameText.Text != null ? t.tProjectInfo.ProjectName.Contains(ProjectNameText.Text) : true) &&
7 (RequestNoText.Text != "" && RequestNoText.Text != null ? t.RequestNo.Contains(RequestNoText.Text) : true) &&
8 (FillDateChk.Checked ? (Convert.ToDateTime(t.FillDate.Trim()).CompareTo(Convert.ToDateTime(FillDateStart.Text.Trim())) >= 0 && Convert.ToDateTime(t.FillDate.Trim()).CompareTo(Convert.ToDateTime(FillDateEnd.Text.Trim())) <= 0) : true) &&
9 (CheckStatChk.Checked ? t.State == false : true)
10 select new
11 {
12 RequestNo = t.RequestNo,
13 Url = HttpUtility.UrlEncode(t.RequestNo),
14 ProjectName = t.tProjectInfo.ProjectName,
15 FillDate = t.FillDate,
16 State = t.State ? "<font color=grey>未生成价格确认单</font>" : "<font color=green>已生成价格确认单</font>"
17 }).Distinct();
18 int? count = matches.Count();
19 if (count.HasValue)
20 {
21 Msg.Text = "总共有【<font color=\"red\">" + count.Value + "</font>】条记录。";
22 RequestData.DataSource = matches;
23 RequestData.DataBind();
24 }
25 else
26 {
27 Msg.Text = "未查询到任何数据，请确认查询条件是否正确。";
28 }
29 }
将Count方法的值使用一个变量保存，避免再次调用，速度快了很多，但是我想，应该还可以继续优化掉一些。我有想到DataBind会影响到性能，因为我使用的是GridView自带的分页，所以有一个事件，要写成如下形式：
1 protected void RequestData_PageIndexChanging(object sender, GridViewPageEventArgs e)
2 {
3 RequestData.PageIndex = e.NewPageIndex;
4 RequestData.DataBind();
5 }
这里面还做了一次绑定，因为如果不写，那么前台点分页要点两次才会分页，有说把Page_Load方法中的ShowRequtstList放到Pre_Render中好像是这么写的，但我觉得应该不是这样的。于是又各种查资料，无果，很郁闷，因为都说是使用这样的方法：将新页面给空间，然后重新绑定，更有甚者会在这重新调用一次ShowRequestList方法来进行绑定，本来效率就慢，在增加一套查询，难怪会慢死。所以我打算去掉RequestData.DataBind()。于是我就去掉了，不可否认的是，分页的确掉了，但是也要点两次，无意中我想到，当时为了页面不刷新，我使用过.Net自带的Ajax来进行局部刷新，可是后来我发现GridView自带无刷新分页。我就想如果使用自带的无刷新分页会不会好些？于是咱就来尝试吧，到aspx页面将GridView的EnableSortingAndPagingCallbacks属性设置为True。果然，问题解决了，这是一个偶然，将EnableSortingAndPagingCallbacks设置为True就能解决GridView点两次分页的问题。这样，分页方法中就不需要DataBind了：
1 protected void RequestData_PageIndexChanging(object sender, GridViewPageEventArgs e)
2 {
3 RequestData.PageIndex = e.NewPageIndex;
4 }
性能稍微又提升了一些。还能进行什么样的提升呢。我想到了搜索按钮，搜索按钮的Click时间中也调用了一次ShowRequestList方法，这样会不会造成，页面刷新调用的一次ShowRequestList方法，Click中也调用了ShowRequestList，等于调用了两次，那么就吧Click中的ShowRequestList删掉，什么都不写，一试，嗯，同样可以查询，一点都不影响。
但是还是觉得别扭，因为这句：
(FillDateChk.Checked ? (Convert.ToDateTime(t.FillDate.Trim()).CompareTo(Convert.ToDateTime(FillDateStart.Text.Trim())) >= 0 && Convert.ToDateTime(t.FillDate.Trim()).CompareTo(Convert.ToDateTime(FillDateEnd.Text.Trim())) <= 0) : true) &&
这个是我最纠结的。不过也解决了，改成了这样：
(Convert.ToDateTime(t.FillDate) >= FillDateStart.Value) && (Convert.ToDateTime(t.FillDate) <= FillDateEnd.Value)
只是按照时间搜索的话很少，而且页面上FillDateChk而True的时候才使用时间查询，但是写在LINQ语句中，肯定是会被转换成SQL语句的。使用db.GetCommand(matches).CommandText查看生成的SQL语句就会发现，这个GetCommand是我昨天刚学来的。显示LINQ转换的SQL语句。
那么我想能不能动态的拼接SQL语句，找了很多这样的内容。但是都说用到了反射，性能会下降，能不用尽量不用。可是微软不会有了LINQ连这样动态的创建LINQ都不支持吧，性能还要下降。于是去翻CSDN和MSDN，不错，终于让我在一个角落里找到了一个方式。LINQTOSQL是延迟加载的。所以LINQ是可以拼接的，于是就改成了这样的写法：
using (PMSDBDataContext db = new PMSDBDataContext())
{
var query = from t in db.tRequestInfo select t;
do
{
//判断是否是项目经理
if (pmbool)
{
query = query.Where(t => t.tProjectInfo.PMUserID == pmid);
}
//判断是否要通过项目名称查询
if (!string.IsNullOrEmpty(ProjectNameText.Text))
{
query = query.Where(t => t.tProjectInfo.ProjectName.Contains(ProjectNameText.Text));
}
//判断是否要通过单号查询
if (!string.IsNullOrEmpty(RequestNoText.Text))
{
query = query.Where(t => t.RequestNo.Contains(RequestNoText.Text));
}
//判断是否勾选已生成价格确认单的采购单
if (CheckStatChk.Checked)
{
query = query.Where(t => t.State == false);
}
//判断是否要通过日期查询
if (FillDateChk.Checked)
{
query = query.Where(t => (Convert.ToDateTime(t.FillDate) >= FillDateStart.Value) && (Convert.ToDateTime(t.FillDate) <= FillDateEnd.Value));
}
} while (false);
//查询并生成新的匿名方法并去重并排序
var matches = query.Select(t => new
{
RequestNo = t.RequestNo,
Url = HttpUtility.UrlEncode(t.RequestNo),
ProjectName = t.tProjectInfo.ProjectName,
FillDate = t.FillDate,
State = t.State ? "<font color=grey>未生成价格确认单</font>" : "<font color=green>已生成价格确认单</font>"
}).Distinct().
OrderByDescending(t => Convert.ToDateTime(t.FillDate));
//并判断结果集中是否有数据
int? count = matches.Count();
if (count.HasValue)
{
Msg.Text = "总共有【<font color=\"red\">" + count.Value + "</font>】条记录。";
RequestData.DataSource = matches;
RequestData.DataBind();
}
else
{
Msg.Text = "未查询到任何数据，请确认查询条件是否正确。";
}
}
太好了！速度又提升了，很兴奋。可是这个时候还是没解决MoveNext的时候一直在查询的问题，我知道其实延迟加载的技术更好，但是我觉得这个地方并不需要，现在需要的是速度！越快越好。所以，手动的去掉延迟加载，很容易，只加一个地方：
1 //查询并生成新的匿名方法并去重并排序
2 var matches = query.Select(t => new
3 {
4 RequestNo = t.RequestNo,
5 Url = HttpUtility.UrlEncode(t.RequestNo),
6 ProjectName = t.tProjectInfo.ProjectName,
7 FillDate = t.FillDate,
8 State = t.State ? "<font color=grey>未生成价格确认单</font>" : "<font color=green>已生成价格确认单</font>"
9 }).Distinct().
10 OrderByDescending(t => Convert.ToDateTime(t.FillDate)).ToArray();//在此处ToList()会提升很大的查询速度，是否有其他影响未知，也是取消了延迟加载
在这个地方ToArray就已经将结果集查出来了，所以MoveNext的时候就不用一直查询了，速度也就上去了。我也尝试了ToList，但是使用性能测试工具得出的结果是ToArray会更快，这个我就不清楚为什么了，如果哪位知道，请赐教，谢谢。
20111216，今天又做了一下小的调整，这两天回复的不少，提了很多方法，很感谢你们，只是有时候好的方法会因为项目上的限制无法发挥，就我上面这样的修改，领导也认为修改太大，但是今天我又稍微的修了了一下，将LINQTOSQL的查询时间控制在100毫秒以内。我的机器小于70，很稳定的速度了，原来写的查询慢的原因，一个是因为使用了GroupBy，还有今天也发现Distinct也慢，即使是使用存储过程的方式。这次就将代码调整成为这样：
1 using (PMSDBDataContext db = new PMSDBDataContext())
2 {
3 var query = db.tRequestInfo.Select(t => new { t.RequestNo, Url = HttpUtility.UrlEncode(t.RequestNo), PMUserID = t.tProjectInfo.PMUserID, ProjectName = t.tProjectInfo.ProjectName, t.State, GenerateState = t.State ? "<font color=grey>未生成价格确认单</font>" : "<font color=green>已生成价格确认单</font>", t.FillDate });
4 do
5 {
6 //判断是否是项目经理
7 if (pmbool)
8 {
9 query = query.Where(t => t.PMUserID == pmid);
10 }
11 //判断是否要通过项目名称查询
12 if (!string.IsNullOrEmpty(ProjectNameText.Text))
13 {
14 query = query.Where(t => t.ProjectName.Contains(ProjectNameText.Text));
15 }
16 //判断是否要通过单号查询
17 if (!string.IsNullOrEmpty(RequestNoText.Text))
18 {
19 query = query.Where(t => t.RequestNo.Contains(RequestNoText.Text));
20 }
21 //判断是否勾选已生成价格确认单的采购单
22 if (CheckStatChk.Checked)
23 {
24 query = query.Where(t => t.State == false);
25 }
26 //判断是否要通过日期查询
27 if (FillDateChk.Checked)
28 {
29 query = query.Where(t => (Convert.ToDateTime(t.FillDate) >= FillDateStart.Value) && (Convert.ToDateTime(t.FillDate) <= FillDateEnd.Value));
30 }
31 } while (false);
32 var matches = query.ToList().Distinct().OrderByDescending(t => Convert.ToDateTime(t.FillDate)).ToArray();
33 int? count = matches.Count();
34 if (count.HasValue){...}
35 }
主要是第32行当地方，先进行ToArray在进行Distinct和OrderByDescending，由于会出现“数据源不支持服务端的数据分页”所以在后面还要加一个ToArray。
这个就这样了，要说方式好，按照下面回复的那些前辈来说，并不好，但是，对于那些只使用LINQTOSQL方式的人来说，能给你们一点借鉴的东西，就行了；
我想，虽然不是好的方案，但是还是对人们有点帮助的吧。
更多内容请访问：http://luacloud.com/2011/another-linq-query-optimization.html
