關(guān)于優(yōu)化for循環(huán)的注意的事項

for循環(huán)注意事項：

1.for循環(huán)內(nèi)部盡量少做數(shù)據(jù)庫查詢之類的IO代價大的操作

2.盡量控制for循環(huán)的次數(shù)，不多做無用功

3.能一次加載在內(nèi)存中的，就不要通過循環(huán)來多次查詢數(shù)據(jù)庫，除非數(shù)據(jù)量過大。

4.for循環(huán)內(nèi)部盡可能少創(chuàng)建對象，會耗費大量內(nèi)存資源

起因：

前兩天優(yōu)化一個統(tǒng)計功能的頁面，客戶反映說，點擊頁面的查詢按鈕要等快十秒中數(shù)據(jù)才加載出來，然后點擊導(dǎo)出按鈕后，加載時間快翻一倍了。讓我查一下看看能不能優(yōu)化。

仔細(xì)看了一下代碼，才發(fā)現(xiàn)代碼問題太大了。

發(fā)現(xiàn)問題：

描述一下優(yōu)化前大致結(jié)構(gòu)：

1.從相關(guān)主表中獲取所有工單信息，存儲到dataset，然后通過for循環(huán)讀取數(shù)據(jù)到一個工單對象的list中。

2.獲取用來修理工單所用的材料信息，放在一個dataset中，然后外循環(huán)dataset，內(nèi)循環(huán)工單對象list。根據(jù)關(guān)聯(lián)ID值相等來判斷，把相關(guān)材料信息賦值給工單對象的材料信息屬性。

3.for循環(huán)工單對象的list來生成拼接統(tǒng)計html，循環(huán)中放了一個查詢sql，生成一個dataset，用dataset中第一行第一列的值來和工單對象的一個屬性來做比較，然后生成不同內(nèi)容

4.在for循環(huán)結(jié)束處放了一個判斷，如果小于200行，那就繼續(xù)添加拼接的html。

把不相關(guān)代碼刪除后，附上代碼結(jié)構(gòu)

/// <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)";        //相關(guān)過濾判斷條件已刪除        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();//反映內(nèi)容                        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 ('套室表','水管設(shè)備','水管問題') and tz.EXECUTIVEPEOPLE!=' ' and tz.EXECUTIVEPEOPLE is not null and ACCEPTSTATION in (" + Session["DEPNAME"].ToString() + ") ";            //相關(guān)過濾判斷條件已刪除            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條數(shù)據(jù)即可，1表示導(dǎo)出功能，導(dǎo)出所有數(shù)據(jù)</param>    /// <param name="xlgctjArr">工單對象列表</param>    /// <returns></returns>    protected string ShowTable(string isAll, ArrayList xlgctjArr)    {        string html = "";        string htmlstr = "";        htmlExportstr = "";        //增加工時系數(shù)統(tǒng)計        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>";            }            //統(tǒng)計類型            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() == "修復(fù)日期")                {                    html += "<td>" + publicbean.resultValue(xfsj) + "</td>";                }            }            else            {                html += "<td>0</td>";            }            html += "</tr>";            if (isAll == "")            {                if (i < 200)                {                    htmlstr += html;                }            }            htmlstr += html;            htmlExportstr += html;//用于導(dǎo)出保存        }        htmlstr += "</table>";        htmlExportstr += "</table>";        return htmlstr;    }

優(yōu)化后：

由于時間緊，差不多修改優(yōu)化了一下，等有時間的時候把業(yè)務(wù)了解清楚，準(zhǔn)備再次進(jìn)行優(yōu)化。貼上優(yōu)化后部分代碼

 /// <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 ('套室表','水管設(shè)備','水管問題')   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)";        //相關(guān)過濾判斷條件已刪除        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 xlgctjBe