记一次代码优化
一、原数据处理
从数据库中获取数据,存放到dt(类型Datatable)中,数据量三万五千条左右,peojectID为页面传到后台的用逗号分隔并加了单引号的字符串。
1、下面的方法将数据源进行第一次过滤。(此方法运行了8秒左右)
DataTable newdt = new DataTable();
newdt = dt.Clone(); // 克隆dt 的结构,包括所有 dt 架构和约束,并无数据;
if (!String.IsNullOrEmpty(projectID)) {
DataRow[] rows = dt.Select("GroupID In (" + projectID + ")"); // 从dt 中查询符合条件的记录;
foreach (DataRow row in rows) // 将查询的结果添加到dt中;
{
newdt.Rows.Add(row.ItemArray);
}
dt = newdt;
}
2、遍历项目ID,从数据源中查出该项目的数据并处理(此方法运行了50秒左右)
string[] groups = Request["ProjectID"].Split(',');
foreach (string groupID in groups) {
if (!string.IsNullOrEmpty(groupID)) {
DataView wbsView = dt.DefaultView;
//获取项目下的数据
wbsView.RowFilter = "GroupID=" + groupID;
DealData(wbsView, newDt);
}
}
3、处理数据源,GetNodeContent方法为拼接html
public void DealData(DataView wbsView, DataTable newDt)
{
if (wbsView.Count > 0)
{
var row1 = newDt.NewRow();
DataTable dtTmp = wbsView.ToTable();
row1["CorpID"] = dtTmp.Rows[0]["CorpID"];
row1["ProjectID"] = dtTmp.Rows[0]["ProjectID"];
row1["ProjectName"] = dtTmp.Rows[0]["ProjectName"];
row1["GroupID"] = dtTmp.Rows[0]["GroupID"];
row1["GroupName"] = dtTmp.Rows[0]["GroupName"];
row1["ModID"] = dtTmp.Rows[0]["ModID"];
row1["ModName"] = dtTmp.Rows[0]["ModName"];
foreach (DataRow datarow in dtTmp.Rows)
{
//行转列
string codeNo = datarow.Field<string>("CodeNo");
if (!string.IsNullOrEmpty(codeNo))
{
var tempRows = milestoneNodes.Select("Sort = " + codeNo);
if (tempRows.Length > 0)
{
var index = tempRows[0].Field<string>("MilestoneNode");
row1[index] = GetNodeContent(datarow);
}
}
}
newDt.Rows.Add(row1);
}
}
二、优化后的数据处理
使用Linq语句分组,并对分组后的数据做双重遍历。依托Linq的强大功能,将数据处理消耗的时间缩短到1秒内。
public void DealDataNew(DataTable dt, string ProjectIDs, DataTable newDt)
{
var groups = ProjectIDs.Replace("'", "").Split(',');
//分组并过滤
var data = from tempData in dt.AsEnumerable()
join groupID in groups on tempData.Field<string>("GroupID") equals groupID
group tempData by tempData.Field<string>("GroupID");
foreach (var item in data) {
var row1 = newDt.NewRow();
var firstRow = item.FirstOrDefault();
row1["CorpID"] = firstRow["CorpID"];
row1["ProjectID"] = firstRow["ProjectID"];
row1["ProjectName"] = firstRow["ProjectName"];
row1["GroupID"] = firstRow["GroupID"];
row1["GroupName"] = firstRow["GroupName"];
row1["ModID"] = firstRow["ModID"];
row1["ModName"] = firstRow["ModName"];
foreach (var row in item) {
//行转列
string codeNo = row.Field<string>("CodeNo");
if (!string.IsNullOrEmpty(codeNo)) {
var tempRows = milestoneNodes.Select("Sort = " + codeNo);
if (tempRows.Length > 0) {
var index = tempRows[0].Field<string>("MilestoneNode");
row1[index] = GetNodeContent(row);
}
}
}
newDt.Rows.Add(row1);
}
}