Considerations for optimizing the for loop and the for Loop

Considerations for optimizing the for loop and the for Loop

For Loop considerations:

1. The for loop should minimize IO-expensive operations such as database queries.

2. Control the number of for loops as much as possible.

3. If the database can be loaded in memory at one time, do not query the database multiple times through loops unless the data volume is too large.

Cause:

The customer reported that clicking the query button on the page had to wait 10 seconds for the data to be loaded. Then, after clicking the export button, the loading time doubled. Let me check whether it can be optimized.

After carefully reading the code, I found that the code is too problematic.

Problems found:

Describe the general structure before optimization:

1. obtain all the ticket information from the relevant master table, store the information to dataset, and then read the data to the list of a ticket object through the for loop.

2. Obtain the material information used to repair the work order, put it in a dataset, then the external cyclic dataset, the inner cyclic work order Object list. Based on the equal correlation ID value, the relevant material information is assigned to the material information attribute of the ticket object.

3. for loop, the list of tickets object is used to generate spliced statistics html. in the loop, a query SQL statement is placed to generate a dataset, compare the values in the first column of the First row with a property of the ticket object in dataset, and then generate different contents.

4. Put a judgment at the end of the for loop. If there are less than 200 rows, add the spliced html.

After removing irrelevant code, attach the code structure

/// <Summary> /// query all eligible ticket objects /// </summary> /// <returns> </returns> protected ArrayList SearchRecord () {OracleDataBase odb = new OracleDataBase (); // encapsulated oracle operation class 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.exe cutiveintime 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) "; // The related filter criteria have been deleted. 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 (); // reflect category xltj. REPORTCONTENT = dbrd ["REPORTCONTENT"]. toString (); // reflect the content 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 ('Chamber table ', 'Water Pipe Equipment', 'Water pipe problem ') and tz. EXECUTIVEPEOPLE! = ''And tz. EXECUTIVEPEOPLE is not null and ACCEPTSTATION in ("+ Session [" DEPNAME "]. toString () + ")"; // The related filter criteria have been deleted. 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> /// concatenate html with xlgctjArr /// </summary> /// <param name = "isAll"> 0, indicating query, only display 200 data records. 1 indicates the export function. Export all data </param> /// <param name = "xlgctjArr"> Image list </param> /// <returns> </returns> protected string ShowTable (string isAll, ArrayList xlgctjArr) {string html = ""; string htmlstr = ""; htmlExportstr = ""; // increase the time coefficient 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>" ;}// statistics type 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 () = "receiving time") {html + = "<td>" + publicbean. resultValue (jbsj) + "</td>";} else if (dtStatisticalType. tables [0]. rows [0] ["TYPE"]. toString () = "start date") {html + = "<td>" + publicbean. resultValue (kgsj) + "</td>";} else if (dtStatisticalType. tables [0]. rows [0] ["TYPE"]. toString () = "repair date") {html + = "<td>" + publicbean. resultValue (xfsj) + "</td>" ;}} else {html + = "<td> 0 </td> ";} html + = "</tr>"; if (isAll = "") {if (I <200) {htmlstr + = html ;}} htmlstr + = html; htmlExportstr + = html; // used to export and save} htmlstr + = "</table>"; htmlExportstr + = "</table>"; return htmlstr ;}

After optimization:

Due to the tight schedule, the modification was optimized. When there was time, the business was clearly understood and the optimization was ready again. Post the optimized code

/// <Summary> /// query all eligible ticket objects /// </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.exe cutiveintime as ISINTIME from TZ_MAIN t, v_tz_xiaowx Iuanddaxiu_wxzt t1, tz_main_clinfo tz where t. id = tz. main_id and t. reporttype in ('casing table ', 'plumbing equipment', 'plumbing problem ') 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) "; // The related filter criteria have been deleted. 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 (); // reflect category xltj. REPORTCONTENT = dbrd ["REPORTCONTENT"]. toString (); // reflect the content 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 ('Chamber table ', 'Water Pipe Equipment', 'Water pipe problem ') 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 (); // repair consumable materials -- TZ_DAXIUCLD} else {(xlgctjBean) xlgctjArr [I]). XLHYCL = ds. tables [0]. rows [j] ["xlhycl"]. toString () ;}}}} Session. add ("xlgctjArr", xlgctjArr); return xlgctjArr ;} /// <summary> /// concatenate html with xlgctjArr /// </summary> /// <param name = "isAll"> 0, indicating query, display only 200 data records Yes. 1 indicates the export function, export all data </param> /// <param name = "xlgctjArr"> ticket Object List </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 dtStati SticalType = 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>" ;}// statistical type if (dtStatisticalType. rows. count> 0) {if (dtStatisticalTypeResult = "") {html + = "<td>" + publicbean. resultValue (jbsj) + "</td>";} else if (dtStatisticalTypeResult = "start date") {html + = "<td>" + publicbean. resultValue (kgsj) + "</td>";} else if (dtStatisticalTypeResult = "repair date") {html + = "<td>" + publicbean. resultValue (xfsj) + "</td>" ;}} else {html + = "<td> 0 </td> ";} html + = "</tr>"; // if (isAll = "") // {// if (I <200) // {// htmlstr + = html; //} htmlstr + = html; htmlExportstr + = html; // for export and save} htmlstr + = "</table>"; htmlExportstr + = "</table>"; //} return htmlstr ;}

The main optimization:

1. for loop, the list of tickets object is used to generate spliced statistics html. in the loop, a query SQL statement is placed to generate a dataset, compare the values in the first column of the First row with a property of the ticket object in dataset, and generate different contents. ======> the database IO cost is too high. If you perform database read operations in the for loop, it is best to put it outside the For loop, A little more memory is much less costly than reading the database each time.

2. Put a judgment at the end of the for loop. If there are less than 200 rows, add the spliced html. ==== "Determine the number directly and do as few cycles as possible.

3. For export optimization, the business rule is to first query and then export. Therefore, I saved the list of tickets encapsulated during query and the generated html string in the session. If the ticket Object List count is less than or equal to 200, export the html in the session directly. If the value is greater than 200, call the list that exceeds 200 to generate the html method ShowTable () and directly generate the remaining html. Then, splice it with the html in the session and export it.

 

Note: If you have time next week, let's take a look at the business related to this statistics. If possible, only one of the three for loop optimizations that generate the ticket Object List will be available, in this way, the performance can be doubled!