for循环注意事项:
1.for循环内部尽量少做数据库查询之类的IO代价大的操作
2.尽量控制for循环的次数,不多做无用功
3.能一次加载在内存中的,就不要通过循环来多次查询数据库,除非数据量过大。
起因:
前两天优化一个统计功能的页面,客户反映说,点击页面的查询按钮要等快十秒中数据才加载出来,然后点击导出按钮后,加载时间快翻一倍了。让我查一下看看能不能优化。
仔细看了一下代码,才发现代码问题太大了。
发现问题:
描述一下优化前大致结构:
1.从相关主表中获取所有工单信息,存储到dataset,然后通过for循环读取数据到一个工单对象的list中。
2.获取用来修理工单所用的材料信息,放在一个dataset中,然后外循环dataset,内循环工单对象list。根据关联ID值相等来判断,把相关材料信息赋值给工单对象的材料信息属性。
3.for循环工单对象的list来生成拼接统计html,循环中放了一个查询sql,生成一个dataset,用dataset中第一行第一列的值来和工单对象的一个属性来做比较,然后生成不同内容
4.在for循环结束处放了一个判断,如果小于200行,那就继续添加拼接的html。
把不相关代码删除后,附上代码结构
class="brush:csharp;gutter:true;">/// <summary> /// 查询所有符合条件的工单对象 /// </summary> /// <returns></returns> protected ArrayList SearchRecord() { OracleDataBase odb = new OracleDataBase();//封装的oracle操作类 ArrayList xlgctjArr = new ArrayList(); string sql = "select t.id xltjid,t.accepttime,tz.EXECUTIVEPEOPLE,EXECUTIVECONTENT,occurreason,resolvent, EXECUTIVEREMARK,'' as SGD,t1.kgsj,tz.EXECUTIVETIME,t.*,t1.lsxz,t1.cz,t1.lssb,t1.lmxz,t1.KJ, t.source as ISZKD,tz.executiveintime as ISINTIME from TZ_MAIN t , v_tz_xiaowxiuanddaxiu_wxzt t1, tz_main_clinfo tz where t.id=tz.main_id and tz.EXECUTIVEPEOPLE is not null and ACCEPTSTATION in (" + Session["DEPNAME"].ToString() + ") and t.id=t1.mainid(+) and (t1.FIXTYPE='0' or t1.FIXTYPE is null)"; //相关过滤判断条件已删除 sql += " order by t.accepttime asc "; if (sql != "") { DataSet ds = new DataSet(); try { ds = odb.GetDataSet(sql); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { DataRow dbrd = ds.Tables[0].Rows[i]; xlgctjBean xltj = new xlgctjBean(); xltj.xltjId = dbrd["xltjid"].ToString(); xltj.REPORTTYPE = dbrd["REPORTTYPE"].ToString();//反映类别 xltj.REPORTCONTENT = dbrd["REPORTCONTENT"].ToString();//反映内容 xlgctjArr.Add(xltj); } } } catch (Exception ex) { Loger.Debug("tjbb_xlmx.SearchRecord()==>" + sql, ex); } finally { if (ds != null) ds.Dispose(); } sql = "select t1.id,t2.xlhycl from tz_main t1,v_tz_xiaowxiuanddaxiu_cailiao t2, tz_main_clinfo tz where t1.id=tz.Main_id(+) and t1.id=t2.mainid and t1.reporttype in ('套室表','水管设备','水管问题') and tz.EXECUTIVEPEOPLE!=' ' and tz.EXECUTIVEPEOPLE is not null and ACCEPTSTATION in (" + Session["DEPNAME"].ToString() + ") "; //相关过滤判断条件已删除 sql += " order by t1.accepttime asc "; ds = odb.GetDataSet(sql); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { for (int j = 0; j < ds.Tables[0].Rows.Count; j++) { for (int i = 0; i < xlgctjArr.Count; i++) { if (((xlgctjBean)xlgctjArr[i]).xltjId == ds.Tables[0].Rows[j]["id"].ToString()) { if (((xlgctjBean)xlgctjArr[i]).XLHYCL != null && ((xlgctjBean)xlgctjArr[i]).XLHYCL != "") { ((xlgctjBean)xlgctjArr[i]).XLHYCL = ((xlgctjBean)xlgctjArr[i]).XLHYCL + ";" + ds.Tables[0].Rows[j]["xlhycl"].ToString();// } else { ((xlgctjBean)xlgctjArr[i]).XLHYCL = ds.Tables[0].Rows[j]["xlhycl"].ToString(); } } } } } } return xlgctjArr; } /// <summary> /// 通过xlgctjArr 拼接html /// </summary> /// <param name="isAll">0,表示查询,只用显示200条数据即可,1表示导出功能,导出所有数据</param> /// <param name="xlgctjArr">工单对象列表</param> /// <returns></returns> protected string ShowTable(string isAll, ArrayList xlgctjArr) { string html = ""; string htmlstr = ""; htmlExportstr = ""; //增加工时系数统计 for (int i = 0; i < xlgctjArr.Count; i++, s++) { html = ""; string jbsj = string.Empty; string xfsj = string.Empty; string kgsj = string.Empty; xlgctjBean tempxlgctjBean = (xlgctjBean)xlgctjArr[i]; if (i % 2 == 0) { } else html += "<tr>"; html += "<td>" + (s + 1) + "</td>"; if (((xlgctjBean)xlgctjArr[i]).REPORTTYPE != null && ((xlgctjBean)xlgctjArr[i]).REPORTTYPE != "") { html += "<td>" + ((xlgctjBean)xlgctjArr[i]).REPORTTYPE + "</td>"; } else { html += "<td> </td>"; } //统计类型 OracleDataBase odb1 = new OracleDataBase(); string sql = string.Format(@"select * from STATISTICALTYEP"); DataSet dtStatisticalType = odb1.GetDataSet(sql); if (dtStatisticalType != null && dtStatisticalType.Tables.Count > 0) { if (dtStatisticalType.Tables[0].Rows[0]["TYPE"].ToString() == "接报时间") { html += "<td>" + publicbean.resultValue(jbsj) + "</td>"; } else if (dtStatisticalType.Tables[0].Rows[0]["TYPE"].ToString() == "开工日期") { html += "<td>" + publicbean.resultValue(kgsj) + "</td>"; } else if (dtStatisticalType.Tables[0].Rows[0]["TYPE"].ToString() == "修复日期") { html += "<td>" + publicbean.resultValue(xfsj) + "</td>"; } } else { html += "<td>0</td>"; } html += "</tr>"; if (isAll == "") { if (i < 200) { htmlstr += html; } } htmlstr += html; htmlExportstr += html;//用于导出保存 } htmlstr += "</table>"; htmlExportstr += "</table>"; return htmlstr; }
优化后:
由于时间紧,差不多修改优化了一下,等有时间的时候把业务了解清楚,准备再次进行优化。贴上优化后部分代码
/// <summary> /// 查询所有符合条件的工单对象 /// </summary> /// <returns></returns> protected ArrayList SearchRecord() { OracleDataBase odb = new OracleDataBase(); string sql = " select t.id xltjid,t.accepttime,EXECUTIVECONTENT,occurreason,resolvent, EXECUTIVEREMARK,'' as SGD,tz.EXECUTIVETIME,t.*,t1.kgsj,t1.lsxz,t1.cz,t1.lssb,t1.lmxz , t.source as ISZKD,tz.executiveintime as ISINTIME from TZ_MAIN t ,v_tz_xiaowxiuanddaxiu_wxzt t1,tz_main_clinfo tz where t.id=tz.main_id and t.reporttype in ('套室表','水管设备','水管问题') and tz.EXECUTIVEPEOPLE is not null and ACCEPTSTATION in (" + Session["DEPNAME"].ToString() + ") and t.id=t1.mainid(+) and ( t1.FIXTYPE='1' or t1.FIXTYPE is null)"; //相关过滤判断条件已删除 sql += " order by t.accepttime asc "; ArrayList xlgctjArr = new ArrayList(); if (sql != "") { ArrayList xltjIds = new ArrayList(); DataSet ds = new DataSet(); try { ds = odb.GetDataSet(sql); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { DataRow dbrd = ds.Tables[0].Rows[i]; xlgctjBean xltj = new xlgctjBean(); xltj.xltjId = dbrd["xltjid"].ToString(); xltj.REPORTTYPE = dbrd["REPORTTYPE"].ToString();//反映类别 xltj.REPORTCONTENT = dbrd["REPORTCONTENT"].ToString();//反映内容 xlgctjArr.Add(xltj); } } } catch (Exception ex) { Loger.Debug("tjbb_gwsbyhmx.SearchRecord()1==>" + sql, ex); } finally { if (ds != null) ds.Dispose(); } sql = "select t1.id,t2.xlhycl from tz_main t1,v_tz_xiaowxiuanddaxiu_cailiao t2 ,tz_main_clinfo tz where t1.id=tz.Main_id(+) and t1.id=t2.mainid and t1.reporttype in ('套室表','水管设备','水管问题') and tz.EXECUTIVEPEOPLE is not null and ACCEPTSTATION in (" + Session["DEPNAME"].ToString() + ") "; sql += " order by t1.accepttime asc "; ds = odb.GetDataSet(sql); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { for (int j = 0; j < ds.Tables[0].Rows.Count; j++) { for (int i = 0; i < xlgctjArr.Count; i++) { if (((xlgctjBean)xlgctjArr[i]).xltjId == ds.Tables[0].Rows[j]["id"].ToString()) { if (((xlgctjBean)xlgctjArr[i]).XLHYCL != null && ((xlgctjBean)xlgctjArr[i]).XLHYCL != "") { ((xlgctjBean)xlgctjArr[i]).XLHYCL = ((xlgctjBean)xlgctjArr[i]).XLHYCL + ";" + ds.Tables[0].Rows[j]["xlhycl"].ToString();//修理耗用材料--TZ_DAXIUCLD } else { ((xlgctjBean)xlgctjArr[i]).XLHYCL = ds.Tables[0].Rows[j]["xlhycl"].ToString(); } } } } } } Session.Add("xlgctjArr", xlgctjArr); return xlgctjArr; } /// <summary> /// 通过xlgctjArr 拼接html /// </summary> /// <param name="isAll">0,表示查询,只用显示200条数据即可,1表示导出功能,导出所有数据</param> /// <param name="xlgctjArr">工单对象列表</param> /// <returns></returns> protected string ShowTable(string isAll, ArrayList xlgctjArr) { string html = ""; string htmlstr = ""; htmlExportstr = ""; ReocrdCount = xlgctjArr.Count.ToString(); int Allcount = 200; OracleDataBase odb1 = new OracleDataBase(); string sql = string.Format(@"select * from STATISTICALTYEP"); DataTable dtStatisticalType = odb1.GetDataSet(sql).Tables[0]; string dtStatisticalTypeResult = dtStatisticalType.Rows[0]["TYPE"].ToString(); int s = 0; if (isAll == "") { Allcount = xlgctjArr.Count > 200 ? 200 : xlgctjArr.Count; } else { Allcount = xlgctjArr.Count; if (isAll == "1") { s = 200; } } for (int i = 0; i < Allcount; i++, s++) { html = ""; string jbsj = string.Empty; string xfsj = string.Empty; string kgsj = string.Empty; if (i % 2 != 0) { html += "<tr>"; } html += "<td>" + (s + 1) + "</td>"; if (((xlgctjBean)xlgctjArr[i]).REPORTTYPE != null && ((xlgctjBean)xlgctjArr[i]).REPORTTYPE != "") { html += "<td>" + ((xlgctjBean)xlgctjArr[i]).REPORTTYPE + "</td>"; } else { html += "<td> </td>"; } //统计类型 if (dtStatisticalType.Rows.Count > 0) { if (dtStatisticalTypeResult == "接报时间") { html += "<td>" + publicbean.resultValue(jbsj) + "</td>"; } else if (dtStatisticalTypeResult == "开工日期") { html += "<td>" + publicbean.resultValue(kgsj) + "</td>"; } else if (dtStatisticalTypeResult == "修复日期") { html += "<td>" + publicbean.resultValue(xfsj) + "</td>"; } } else { html += "<td>0</td>"; } html += "</tr>"; //if (isAll == "") //{ // if (i < 200) // { // htmlstr += html; // } //} htmlstr += html; htmlExportstr += html;//用于导出保存 } htmlstr += "</table>"; htmlExportstr += "</table>"; //} return htmlstr; }
本次主要优化地方:
1.for循环工单对象的list来生成拼接统计html,循环中放了一个查询sql,生成一个dataset,用dataset中第一行第一列的值来和工单对象的一个属性来做比较,然后生成不同内容.=======>数据库IO代价太大,在for循环内做数据库读取操作的话,最好能放在For循环外面,多花一点内存比每次读取数据库的开销小很多
2.在for循环结束处放了一个判断,如果小于200行,那就继续添加拼接的html。=====》直接判断好数量,尽量少做循环。
3.关于导出的优化,由于业务规则是先查询然后导出。所以我把查询时封装的工单对象列表和生成的html字符串都保存在session中,等导出的时候。判断工单对象列表count小于等于200的话,直接把session中的html导出就好。如果大于200,把超过200的list调用生成html的方法ShowTable()直接生成剩下的html,然后和session中的html拼接后导出即可
注:下周有时间的话,去了解一下关于这个统计的业务,如果可能的话,把生成工单对象列表的三个for循环优化的只剩一个,这样性能才能翻翻!