ASP執行得到結果要40秒，各項能調整的已調整，請問還有辦法改善效能嗎？

asp 執行時間效能優化

b7307024 2018-12-24 19:17:23 ‧ 2282 瀏覽

分享至

各位前輩好，依照之前詢問關於SQL效能的問題，我已經做了如下調整：
1.建立Index。
2.SQL語法修改，測試跑SQL，大約 0.02秒可以完成。

但是在跑ASP網頁程式，我要的結果，需要40秒才會跑出來。請問還有辦法修正嗎，

程式架構如下：

<!-- #include file="../top.asp" -->
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="100" height="600" valign="top" bgcolor="#CCCCCC"><!-- #include file="left.asp" --></td>
<td width="100%" valign="top">
<%
	if session("Account") = "" then
		response.Write"<script>"
		response.Write"alert('請重新登入！');"
		response.Write"parent.mainFrame.location='main.asp';parent.leftFrame.location='left.asp';"
		response.Write"</script>"
	else
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=big5">
<title>總表</title>
</head>
<body><br>
<center>
<form name="form1" method="post" action="drbonus.asp">
	<b>歷史紀錄</b>
	<select name="saveym">
		<option value="">目前紀錄</option>
<%
		ymsql = "SELECT DISTINCT saveym FROM BONUS_Pmaitain_History ORDER BY saveym DESC"
		set ymrs = Sql05Conn.execute(ymsql)
		do while not ymrs.eof
			response.Write("<option value="&ymrs("saveym")&" "&getSelectedString(request("saveym"),ymrs("saveym"))&">"&left(ymrs("saveym"),4)&"年"&right(ymrs("saveym"),2)&"月</option>")
			ymrs.movenext
		loop
%>	
	</select>
	<strong>請選擇員工類別：</strong>
	<select name="kind">
		<option value="EP1" <%=getSelectedString(request("kind"),"EP1")%>>EP1</option>
		<option value="EP2" <%=getSelectedString(request("kind"),"EP2")%>>EP2</option>
	</select>
	 
	<input type="submit" name="search" value="查詢">
</form>
</center>											
<%		
		dealer = array("00001","00002","00003","00004","00005","00006","00007","00008","00009")
		dealername = array("據點1","據點2","據點3","據點4","據點5","據點6","據點7","據點8","據點9") 
		'若是有增減據點只要在這裡修改資料(代號+中文名)，下面的程式皆不需修改。
		dealernum = ubound(dealer) 
		fieldtotal = dealernum + 1 
		
		if request("search") = "查詢" then
		
			if request("kind") = "EP1" then  
				epkind = "EP1" 'EP1
				filed = "A" 
			else 
				epkind = "EP2" 'EP2
				filed = "B"
			end if
			
			if request("saveym") = "" then 
				table1 = "BONUS_XXX"
				table2 = "BONUS_DocMt"
			else
				table1 = "BONUS_XXX_History"
				table2 = "BONUS_DocMt_History"
				datestr = "WHERE saveym="&request("saveym")&""
			end if
			
			sql = "SELECT a.DepCode AS Dept,a."&epkind&"ID AS ECode,a."&epkind&" AS EName,b.PromCase AS MCase,b.PromClass AS MClass,SUM(a.Amount) AS Mcount "
			sql= sql + "FROM (SELECT DocNum,DepCode,"&epkind&","&epkind&"ID,Amount FROM "&table1&" "&datestr&") AS a "
			sql= sql + "INNER JOIN (SELECT DocNum,PromClass,PromCase FROM "&table2&" "&datestr&") AS b "
			sql= sql + "ON a.DocNum = b.DocNum "
			sql= sql + "WHERE a."&epkind&" <> '其他' "
			sql= sql + "GROUP BY a.DepCode,a."&epkind&"ID,a."&epkind&",b.PromCase,b.PromClass"
			set rs = Sql05Conn.execute(sql)
			
			'先清空暫存區
			Sql05Conn.execute("DELETE FROM BONUS_TEMP WHERE Creater = '"&session("Employeecode")&"' AND Salary IS NULL")
			
			do while not rs.eof
				TEMPBONUS = 0
			 	Dept = rs("Dept") '據點
				Ecode = rs("Ecode") '工號
				EName = rs("EName") '姓名
				MCase = rs("MCase") '促銷案代號
				MClass = rs("MClass") '類別代號
				MCount = rs("MCount") '總計數量
				
				'判斷區間、獎罰
				if request("saveym") = "" then 
					table1 = "BONUS_promClass"
					table2 = "BONUS_promCase"
				else
					table1 = "BONUS_promClass_History"
					table2 = "BONUS_promCase_History"
					datestr = "AND a.saveym="&request("saveym")&" AND b.saveym="&request("saveym")&" "
				end if
				
				sql= "SELECT "&filed&"level1,"&filed&"level2,"&filed&"level3,"&filed&"level4,"&filed&"level5,"
				sql = sql + ""&filed&"bonus1,"&filed&"bonus2,"&filed&"bonus3,"&filed&"bonus4,"&filed&"bonus5 "
				sql = sql + "FROM "&table1&" AS a "
				sql = sql + "INNER JOIN "&table2&" AS b "
				sql = sql + "ON LEFT(a.ClassID,1) = b.CaseID "&datestr&" "
				sql = sql + "WHERE a.ClassID = '"&MClass&"' "
				set crs = Sql05Conn.execute(sql)
				
				LEVEL1 = cdbl(crs(0))
				LEVEL2 = cdbl(crs(1))
				LEVEL3 = cdbl(crs(2))
				LEVEL4 = cdbl(crs(3))
				LEVEL5 = cdbl(crs(4))
				BONUS1 = cdbl(crs(5))
				BONUS2 = cdbl(crs(6))
				BONUS3 = cdbl(crs(7))
				BONUS4 = cdbl(crs(8))
				BONUS5 = cdbl(crs(9))
				
				'開始判斷區間和獎金
				if MCount >= LEVEL1 and MCount <= LEVEL2 then 
					TEMPBONUS = MCount*BONUS1
				elseif MCount > LEVEL2 and MCount <= LEVEL3 then 
					TEMPBONUS = MCount*BONUS2
				elseif MCount > LEVEL3 and MCount <= LEVEL4 then 
					TEMPBONUS = MCount*BONUS3
				elseif MCount > LEVEL4 and MCount <= LEVEL5 then 
					TEMPBONUS = MCount*BONUS4
				elseif MCount > LEVEL5 then
					TEMPBONUS = MCount*BONUS5
				end if
				
				TEMPBONUS = cdbl(formatnumber(TEMPBONUS,0,-1,0,0))
				'新增資料
				sql="INSERT INTO BONUS_TEMP(Dept,ECode,EName,MCase,MClass,MCount,MMoney,Creater) "
				sql=sql+"VALUES('"&Dept&"','"&ECode&"','"&Ename&"','"&MCase&"','"&MClass&"',"&MCount&","&TEMPBONUS&",'"&session("Employeecode")&"')"
				Sql05Conn.execute sql
				rs.movenext
			loop
		
%>
[<a href="drbonusExcel.asp?kind=<%=request("kind")%>&saveym=<%=request("saveym")%>">匯出檔案</a>]
<table width="100%" border="1" align="center" cellpadding="3" cellspacing="0" bordercolorlight="#cccccc" bordercolordark="#FFFFFF">
	<tr>
		<td align="center" colspan="22"><%=getym(request("saveym"))%><%=getEptypeChtName(request("kind"))%>獎金總表</td>
	</tr>
	<tr align="center">
		<td colspan="2" bgcolor="#CCCCCC">據點</td>
		<%	for i=0 to ubound(dealername) %>
			<td bgcolor="#CCCCCC" colspan="2"><%=dealername(i)%></td>
		<%	next %>
		<td colspan="2" bgcolor="#CCCCCC">總計</td>
	</tr>
	<tr align="center">
		<td colspan="2" bgcolor="#CCCCCC">項目</td>
		<%	for i=0 to ubound(dealername)+1 %>
			<td bgcolor="#CCCCCC">數量</td>
			<td bgcolor="#CCCCCC">金額</td>
		<%	next %>
	</tr>
	<%
			if request("saveym") = "" then 
				table = "BONUS_promClass" 
			else 
				table = "BONUS_promClass_History"
				datestr = "AND saveym = "&request("saveym")&" "
			end if 

			sql="SELECT DISTINCT MClass,ClassName "
			sql=sql+"FROM BONUS_TEMP "
			sql=sql+"INNER JOIN "&table&" ON MClass = ClassID "&datestr&""
			sql=sql+"WHERE Creater = '"&session("Employeecode")&"' AND Salary IS NULL "
			sql=sql+"ORDER BY ClassName "
			set rs2 = server.CreateObject("adodb.recordset")
			rs2.open sql,Sql05Conn,1,1
			
			kindcount = rs2.recordcount '項目的數量
			redim sumcount(kindcount,fieldtotal) '單項數量欄總計(廠別數量總計)
			redim summoney(kindcount,fieldtotal) '單項獎金欄總計(廠別獎金總計)
			redim Totalcount(fieldtotal) '據點單項數量欄總計(廠別數量總計)
			redim Totalmoney(fieldtotal) '據點單項獎金欄總計(廠別獎金總計)
			
			i=0
			do while not rs2.eof
				searchClass = rs2("MClass")
				response.Write("<tr>")
				response.Write("<td>"&i+1&"</td>")
				response.Write("<td>"&rs2("classname")&"</td>")
				
				'據點的數量、金額
				for j=0 to dealernum
					sql = "SELECT ISNULL(SUM(MCount),0) AS MC,ISNULL(SUM(MMoney),0) AS MM "
					sql = sql + "FROM BONUS_TEMP "
					sql = sql + "WHERE Dept = '"&dealer(j)&"' AND MClass = '"&searchClass&"' AND Creater ='"&session("Employeecode")&"' AND Salary IS NULL "
					set rs3 = Sql05Conn.execute(sql)
					
					if not rs3.eof then
						sumcount(i,j) = rs3("MC") '數量
						summoney(i,j) = rs3("MM") '金額
					else
						sumcount(i,j) = 0 '數量
						summoney(i,j) = 0 '金額
					end if
					
					response.Write("<td align=right>"&sumcount(i,j)&"</td>")
					response.Write("<td align=right>"&summoney(i,j)&"</td>")
					
					sumcount(i,fieldtotal) = sumcount(i,fieldtotal) + sumcount(i,j)
					summoney(i,fieldtotal) = summoney(i,fieldtotal) + summoney(i,j)
					
					Totalcount(j) = Totalcount(j) + sumcount(i,j)
					Totalmoney(j) = Totalmoney(j) + summoney(i,j)
				next
				response.Write("<td align=right>"&sumcount(i,fieldtotal)&"</td>") '單項總計數量
				response.Write("<td align=right>"&summoney(i,fieldtotal)&"</td>") '單項總計金額
				response.Write("</tr>")
				i = i + 1
				rs2.movenext 
			loop
		
	%>
	<tr>
		<td colspan="2" align="center">總計</td>
		<%
			sumallcount = 0
			sumallmoney = 0
			for j=0 to dealernum 
				response.Write("<td align=right>"&Totalcount(j)&"</td>") '單項總計數量
				response.Write("<td align=right>"&Totalmoney(j)&"</td>") '單項總計金額
				sumallcount = sumallcount + Totalcount(j)
				sumallmoney = sumallmoney + Totalmoney(j)
			next 
		%>
		<td align="right"><%=sumallcount%></td>
		<td align="right"><%=sumallmoney%></td>
	</tr>
	<tr>
		<td colspan="22" align="right">總發放金額：<%=sumallmoney%></td>
	</tr>
</table>
<%
			rs2.close
			set rs2 = nothing
		
		end if
%>
</body>
</html>
<%
		Sql05Conn.close
		set Sql05Conn = nothing
	end if
%>
</td>
</tr>
</table>
<!-- #include file="../down.asp" -->

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

3 個回答

純真的人

iT邦大師 1 級 ‧ 2018-12-24 19:52:52

改善簡單~

請不要用程式的do及for迴圈~

直接在SQL裡面新增資料~以及產生td跟tr就好了~

全寫成預存程序就解決了~

回應 2
分享
檢舉

b7307024 iT邦新手 3 級 ‧ 2018-12-25 14:29:43 檢舉

純真大

我去Google了一下，是有查到ASP如何呼叫Stored Procedure及使用，但首要問題是Stored Procedure我就不熟了，如何撰寫我也不清楚，所以你可以協助我一步步學起嗎? 感激不盡！

純真的人 iT邦大師 1 級 ‧ 2018-12-25 14:43:43 檢舉

那你先要練習Procedure，如何建立以及傳入參數~
然後你要用T-SQL方式去呼叫及傳值
最後才是把select的資料秀出來~
這部分 for迴圈可以用SQL的for xml path('')的方式解決
因為你是顯示在table
所以要把tr跟td寫在SQL裡面去組合字串一次秀出來~

Declare @Tmp table(
	Str1 nvarchar(50)
	,Str2 nvarchar(50)
)
insert into @Tmp
values('123','456')
,('789','111')
,('222','333')

select '<table>' + Convert(nvarchar(max),Replace(Replace((
	select '<tr><td>' + isNull(Str1,'') + '</td>'
	,'<td>' + isNull(Str2,'') + '</td></tr>'
	from @Tmp
	for xml path('')
),'&lt;','<'),'&gt;','>')) + '</table>' as SQLStr

就像這樣..

登入發表回應

Luke

iT邦研究生 5 級 ‧ 2018-12-24 19:54:23

看了您的寫的CODE
loop 內還有 Sql05Conn.execute(sql)
這樣快不了

查詢出來有 10筆
每一筆，在查2次SQL

如果一次SQL要1秒
第一次10筆(1秒)
loop 10筆(20秒)
....算下來，就很久了

您去算，您跟資料庫一個頁面，execute 幾次 SQL
資料庫都會有紀錄

回應 1
分享
檢舉

b7307024 iT邦新手 3 級 ‧ 2018-12-25 15:24:20 檢舉

Luke大，那請問可以提供解決方式嗎？感謝!

登入發表回應

rogeryao

iT邦超人 7 級 ‧ 2018-12-24 21:09:48

i=0
do while not rs2.eof
	for j=0 to dealernum
		
	next
	i = i + 1
	rs2.movenext 
loop

這有兩層迴圈 ,作甚麼用 ?
rs2 多少筆 ? dealernum 多大 ?
耗費多少時間 ?

sql = ""
set rs = Sql05Conn.execute(sql)
...
do while not rs.eof	
	sql= ""
	set crs = Sql05Conn.execute(sql)
	...	
	'新增資料
	sql="INSERT INTO XXXXX() "
	sql=sql+"VALUES()"
	Sql05Conn.execute sql
	rs.movenext
loop

重組 SQL 判斷是否可改為下式,就不用跑迴圈做 INSERT

sql= INSERT INTO "表格1" ("欄位1", "欄位2", ...)
SELECT "欄位A", "欄位B", ...
FROM "表格2"
Sql05Conn.execute sql

回應 5
分享
檢舉

看更多先前的回應...收起先前的回應...

b7307024 iT邦新手 3 級 ‧ 2018-12-25 16:14:14 檢舉

rogeryao大，抱歉，我修改了完整的程式，避免有看不懂的情況發生。

1.這個是要找出剛剛存入CPTBONUS_TEMP的資料中，獨立的類別代號及中文名稱 "DISTINCT MClass,ClassName"，在利用據點代號dealer(j)、剛剛找出的類別代號，回到CPTBONUS_TEMP去SUM數量及金額並存入陣列
rs2是20筆資料 dealernum是8

2.有這段語法

'開始判斷區間和獎金
				if MCount >= LEVEL1 and MCount <= LEVEL2 then 
					TEMPBONUS = MCount*BONUS1
				elseif MCount > LEVEL2 and MCount <= LEVEL3 then 
					TEMPBONUS = MCount*BONUS2
				elseif MCount > LEVEL3 and MCount <= LEVEL4 then 
					TEMPBONUS = MCount*BONUS3
				elseif MCount > LEVEL4 and MCount <= LEVEL5 then 
					TEMPBONUS = MCount*BONUS4
				elseif MCount > LEVEL5 then
					TEMPBONUS = MCount*BONUS5
				end if

所以我想應該是沒有辦法用INSERT INTO ... SELECT FROM 來加快速度的吧?? ^^

rogeryao iT邦超人 7 級 ‧ 2018-12-25 16:35:45 檢舉

INSERT INTO "表格1" ("欄位1", "欄位2",TEMPBONUS,...)
SELECT "欄位A", "欄位B", 
case when MCount >= LEVEL1 and MCount <= LEVEL2 then 
	MCount*BONUS1
when MCount > LEVEL2 and MCount <= LEVEL3 then 
	MCount*BONUS2
when MCount > LEVEL3 and MCount <= LEVEL4 then 
	MCount*BONUS3
when MCount > LEVEL4 and MCount <= LEVEL5 then 
	MCount*BONUS4
else MCount > LEVEL5 then
	MCount*BONUS5
end,... 
FROM "表格2"

b7307024 iT邦新手 3 級 ‧ 2018-12-25 17:03:20 檢舉

好的~ 那我在研究一下，目前看起來需要INNER JOIN 四個表，測試看看會不會效能變差囉，希望不會！感謝您~

rogeryao iT邦超人 7 級 ‧ 2018-12-25 19:38:04 檢舉

匯出檔案=>上面說的兩層迴圈改成下式應該就不用再跑
for j=0 to dealernum

sql="SELECT MClass,ClassName,Dept,Creater,ISNULL(SUM(MCount),0) AS MC,ISNULL(SUM(MMoney),0) AS MM "
sql=sql+"FROM BONUS_TEMP "
sql=sql+"INNER JOIN "&table&" ON MClass = ClassID "&datestr&""
sql=sql+"WHERE Creater = '"&session("Employeecode")&"' AND Salary IS NULL "
sql=sql+"GROUP BY MClass,ClassName,Dept,Creater "
sql=sql+"ORDER BY MClass,ClassName,Dept,Creater "

rogeryao iT邦超人 7 級 ‧ 2018-12-28 08:54:08 檢舉

邏輯上的作法 :
1.一般 SQL 取出所有 DepCode
2.一般 SQL 取出正確資料 A
3.用 A 內的數量做行轉列 Pivot 1
4.用 A 內的Bonus_Dept做行轉列 Pivot 2
5.以 CPTBONUS_promClass 為主串接 Pivot 1 ,Pivot 2
=> 只需 第 5 條 SQL 就可以了

程式原則 :
1.可以在 SQL Server 上做的 , 就不要把資料拉到 Web Server 去做
2.大量的資料傳輸很耗時,在加上存到陣列也要花時間,若是又用了兩層的迴圈,跑很久是必然的,運氣不好就只會出現 TimeOut

登入發表回應