Java中使用SQL的效率分析例子

前年給地圖技術審查中心做了一個培訓的報名系統，功能需求比較簡單，由於當時剛開始摸索java就只能照葫蘆畫瓢的使用JDBC寫的程序。這是中百信公司的遺留項目，我接手過來的，仿照以前的代碼寫得。

出現問題的代碼：

1. <div id="content">  
2.             <div class="locbackground">  
3.             </div>  
4.             <div class="text">  
5.             <center>  
6.                 <h1>  
7.                     查看培訓（會議）報名信息  
8.                 </h1>  
9.                 <div><input type="button" value="導出會議（培訓）報名信息 " onclick="location.href='/dtsc/pxbm/outRegisterByConference.jsp?cid=<%=request.getParameter("cid")%>'"/></div>  
10.                 <br/><table class="listCases" width="100%" border="0"  
11.                     style="width: 100%; FONT-SIZE: 18px; word-break: break-all"  
12.                     cellspacing="1" cellpadding="5" bgcolor=#455ca2>  
13.                     <thead>  
14.                         <tr height="40px">  
15.                             <th width="70" bgcolor=#dcdcdc align="center" class=""  
16.                                 style="font-size: 14px; FONT-WEIGHT: bold">  
17.                                 操作  
18.                             </th>  
19.                             <th width="30" bgcolor=#dcdcdc align="center" class=""  
20.                                 style="font-size: 14px; FONT-WEIGHT: bold">  
21.                                 序號  
22.                             </th>  
23.                             <th width="30" bgcolor=#dcdcdc align="center" class=""  
24.                                 style="font-size: 14px; FONT-WEIGHT: bold">  
25.                                 姓名  
26.                             </th>  
27.                             <th bgcolor=#dcdcdc align="center" class=""  
28.                                 style="font-size: 14px; FONT-WEIGHT: bold">  
29.                                 性別  
30.                             </th>  
31.                             <th bgcolor=#dcdcdc align="center" class=""  
32.                                 style="font-size: 14px; FONT-WEIGHT: bold">  
33.                                 民族  
34.                             </th>  
35.                             <th bgcolor=#dcdcdc align="center" class=""  
36.                                 style="font-size: 14px; FONT-WEIGHT: bold">  
37.                                 身份證號  
38.                             </th>  
39.                             <th bgcolor=#dcdcdc align="center" class=""  
40.                                 style="font-size: 14px; FONT-WEIGHT: bold">  
41.                                 工作單位  
42.                             </th>  
43.                             <th bgcolor=#dcdcdc align="center" class=""  
44.                                 style="font-size: 14px; FONT-WEIGHT: bold">  
45.                                 職務（職稱）  
46.                             </th>  
47.                             <th bgcolor=#dcdcdc align="center" class=""  
48.                                 style="font-size: 14px; FONT-WEIGHT: bold">  
49.                                 手機號碼  
50.                             </th>  
51.                             <th bgcolor=#dcdcdc align="center" class=""  
52.                                 style="font-size: 14px; FONT-WEIGHT: bold">  
53.                                 乘坐何種 交通工具（班次）  
54.                             </th>  
55.                             <th bgcolor=#dcdcdc align="center" class=""  
56.                                 style="font-size: 14px; FONT-WEIGHT: bold">  
57.                                 到達時間  
58.                             </th>  
59.                             <th bgcolor=#dcdcdc align="center" class=""  
60.                                 style="font-size: 14px; FONT-WEIGHT: bold">  
61.                                 注冊時間  
62.                             </th>  
63.                             <th bgcolor=#dcdcdc align="center" class=""  
64.                                 style="font-size: 14px; FONT-WEIGHT: bold">  
65.                                 ip地址  
66.                             </th>  
67.                         </tr>  
68.                     </thead>  
69.                     <%  
70.                         Collection registerId = conferenceRegisterAssignOp.getRegisterIdByConferenceId(Integer.parseInt(request.getParameter("cid")));  
71.                         Iterator it = registerId.iterator();  
72.                         int index = 1;  
73.                         while (it.hasNext()) {  
74.                             Integer integer = (Integer)it.next();  
75.                             int rid = integer.intValue();  
76.                             Register temp = registerOp.getCaseInfo(rid);  
77.                     %>  
78.                     <tr>  
79.                         <td class="listContent">  
80.                             <a href="dtsc/pxbm/editRegister.jsp?registerId=<%=temp.getId()%>">編輯</a>  
81.                             <br />  
82.                             <br />  
83.                             <a href="dtsc/pxbm/deleteRegister.jsp?registerId=<%=temp.getId()%>">刪除</a>  
84.                             <br />  
85.                             <br />  
86.                             <a href="dtsc/pxbm/editRegister.jsp?registerId=<%=temp.getId()%>">查看詳細</a>  
87.                         </td>  
88.                         <td class="listContent"><%=index%></td>  
89.                         <td class="listContent"><%=temp.getName() %></td>  
90.                         <td class="listContent"><%=CodeSet.getSex(temp.getSex())%></td>  
91.                         <td class="listContent"><%=temp.getRace()%></td>  
92.                         <td class="listContent"><%=temp.getIdentificationId() %></td>  
93.                         <td class="listContent"><%=temp.getDepartment()%></td>  
94.                         <td class="listContent"><%=temp.getPositionOrIdentity()%></td>  
95.                         <td class="listContent"><%=temp.getMobilephone()%></td>  
96.                         <td class="listContent"><%=temp.getDeparture()%></td>  
97.                         <td class="listContent"><%=temp.getArrivingTime()%></td>  
98.                         <td class="listContent"><%=temp.getRegisterTime()%></td>  
99.                         <td class="listContent"><%=temp.getIp()%></td>  
100.                     </tr>  
101.                     <%  
102.                         ++index;  
103.                         }  
104.                     %>  
105.                 </table>  
106.                 </center>  
107.             </div>  
108.         </div>  

因為是嵌在JSP頁面的中代碼，在java代碼後面跟的是一些顯示用的HTML代碼。

注意這一段代碼：

1. Collection registerId = conferenceRegisterAssignOp.getRegisterIdByConferenceId(Integer.parseInt(request.getParameter("cid")));  
2.                     Iterator it = registerId.iterator();  
3.                     int index = 1;  
4.                     while (it.hasNext()) {  
5.                         Integer integer = (Integer)it.next();  
6.                         int rid = integer.intValue();  
7.                         Register temp = registerOp.getCaseInfo(rid);  

這裡邊先根據cid（培訓會議表id）取到了參加本次會議的注冊人員的id列表。

1. Collection registerId = conferenceRegisterAssignOp.getRegisterIdByConferenceId(Integer.parseInt(request.getParameter("cid")));  

然後再遍歷這個列表，對每一個注冊人員id，都取出這個人員的詳細信息。

1. Register temp = registerOp.getCaseInfo(rid);  

這樣在報名的數量達到200條的時候運行起來的時間大約為40s到70秒s。由於這個頁面的table中的行tr是在後台逐條生成的，再Collection遍歷是陸陸續續發送到浏覽器的，這樣會使浏覽器等待頁面的時間太長，導致浏覽器與後台Web Server的連接超過TimeOut時長，斷開連接。反應到前端浏覽器的現象就是頁面的前半部分加載完成，列表沒有全部加載出來。