今天在项目上遇到一个问题,代码执行的效率太低,最终分析得知,是出在这段代码上
先看一下这段代码
for (int i = 0; i < powerList.Count; i++)
{
Guid powerId = powerList[i].VGUID;//权限ID
DataRow dr = dt.NewRow();
dr[0] = powerList[i].FuncName;
dr[1] = powerList[i].IsParent;
dr[2] = powerList[i].VGUID;
for (int j = 3; j < dt.Columns.Count; j++)
{
Guid roleId = roleList[j - 3].VGUID;
if (_db.CS_Power_Role.Where(c => c.PowerID == powerId && c.RoleID == roleId).Count() != 0)
{
dr[j] = false;
}
else
{
dr[j] = true;
}
}
dt.Rows.Add(dr);
}
结果:执行时间3.28s,大概执行了三百多条SQL语句
优化处理之后:
List<CS_Power_Role> powerRoleList = _db.CS_Power_Role.ToList();
for (int i = 0; i < powerList.Count; i++)
{
Guid powerId = powerList[i].VGUID;//权限ID
DataRow dr = dt.NewRow();
dr[0] = powerList[i].FuncName;
dr[1] = powerList[i].IsParent;
dr[2] = powerList[i].VGUID;
for (int j = 3; j < dt.Columns.Count; j++)
{
Guid roleId = roleList[j - 3].VGUID;
dr[j] = !powerRoleList.Any(c => c.PowerID == powerId && c.RoleID == roleId);
}
dt.Rows.Add(dr);
}
执行时间00:00:00.0090005s,只需要执行一次SQL
原来是在方法里面执行了太多SQL导致运算结果变慢了,其实问题很简单,如果对一个不经常发生变化的数据源进行操作,可以先把它从数据库读入内存,遍历内存可比执行SQL快多了