28,408
社区成员
发帖
与我相关
我的任务
分享100分求此程序优化，谢谢！
我做了一个汇总程序，里面有2个for循环，外面的for有160次，里面的for有50次，在两层for循环里面还有N多的连接数据库查询计算，运行要5分多钟。我想了两天都没有办法解决，所以发上来求各午路高人给个办法，万分感激！
以下是源代码：
<%
if session("u_id")="" then
response.redirect "index.asp"
end if
%>
<!--#include file=cn.asp-->
<script language="javascript">
<!--
function AutomateExcel()
{
// Start Excel and get Application object.
var oXL = new ActiveXObject("Excel.Application");
// Get a new workbook.
var oWB = oXL.Workbooks.Add();
var oSheet = oWB.ActiveSheet;
var table = document.all.data;
var hang = table.rows.length;
var lie = table.rows(0).cells.length;
// Add table headers going cell by cell.
for (i=0;i<hang;i++)
{
for (j=0;j<lie;j++)
{
oSheet.Cells(i+1,j+1).value = table.rows(i).cells(j).innerText;
}
}
oXL.Visible = true;
oXL.UserControl = true;
}
//
-->
</script>
<html>
<head>
<title>年度预算费用汇总表</title>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<link rel="stylesheet" href="default1.css" type="text/css">
</head>
<body bgcolor="#336699" text="#000000" leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
<p align="center"><b><font size="4" color="#000000"><object style="border:0px" type="text/x-scriptlet" data="top.asp" width=100% height=75>
</object><br>
<br>
<font color="#FFFFFF"><%=year(date())+1%>年生产作业费</font></font></b><font size="4" color="#FFFFFF"><b>预算汇总表
</b></font><font size="4" color="#000000"> </font> </p>
<%
'文件开始执行时
startime=timer()
Server.ScriptTimeout = 500
set rs3 = Server.CreateObject("ADODB.Recordset") '科目
sql3="select * from kemu order by k_order asc"
rs3.open sql3,cn,3
count_k=rs3.recordcount-1
set rs4 = Server.CreateObject("ADODB.Recordset") '油田
sql4="select * from you order by y_order asc"
rs4.open sql4,cn,3
count_y=rs4.recordcount-1
''''''''''''''''''''建立数组'''''''''''''''''''''''''
'数据数组
dim ky()
redim ky(count_k,count_y)
'科目名称数组及科目code数组
dim kemu()
dim kemu_code()
redim kemu(count_k)
redim kemu_code(count_k)
for ii=0 to count_k '产生科目级别的空格
for kk=0 to rs3("k_depth")*2
nn=nn&" "
next
kemu(ii)=nn&rs3("k_name")
nn=""
kemu_code(ii)=rs3("k_code")
rs3.movenext
if rs3.eof then exit for
next
rs3.movefirst
'油田数组
dim you()
redim you(count_y)
for jj=0 to count_y
you(jj)=rs4("y_name")
rs4.movenext
if rs4.eof then exit for
next
rs4.movefirst
''''''''''''''''''''''''''''''''''''''''''''''''''''''
dim f,c
dim f2,c2
for ee=0 to count_k
rs4.movefirst
for rr=0 to count_y
c=""
call aaa(rs3("k_code"))
c2=""
call bbb(rs4("y_code"))
'根据SQL查数据，把值转给数据单元
set rs5 = Server.CreateObject("ADODB.Recordset")
sql5="select sum(d_z) as sum_d_z from ysdata where ("&left(c,len(c)-3)&") and ("&left(c2,len(c2)-3)&") and d_year='"&year(date)+1&"'"
rs5.open sql5,cn,3
'response.write sql5&"<br>"&"<br>"
ky(ee,rr)=rs5("sum_d_z")
if vartype(ky(ee,rr))=1 then
ky(ee,rr)=0
end if
rs5.close
set rs5=nothing
rs4.movenext
if rs4.eof then exit for
next
rs3.movenext
if rs3.eof then exit for
next
rs3.close
set rs3=nothing
rs4.close
set rs4=nothing
'取得科目串
sub aaa(a)
set rs11 = Server.CreateObject("ADODB.Recordset")
'sql11="select * from kemu where k_parent='"&a&"' order by k_order asc"
sql11="select * from kemu where k_parent='"&a&"' order by k_order asc"
rs11.open sql11,cn,3
if rs11.eof then
f=a
c=c&"k_code="&f&" or "
else
for i=1 to rs11.recordcount
f=rs11("k_code")
call aaa(rs11("k_code"))
rs11.movenext
if rs11.eof then exit for
next
end if
end sub
'取得油田串
sub bbb(b)
set rs22 = Server.CreateObject("ADODB.Recordset")
sql22="select * from you where y_parent='"&b&"'"
rs22.open sql22,cn,3
if rs22.eof then
f2=b
c2=c2&"y_code="&f2&" or "
else
for j=1 to rs22.recordcount
f2=rs22("y_code")
call bbb(rs22("y_code"))
rs22.movenext
if rs22.eof then exit for
next
end if
end sub
%>
<form name="form1" method="post" action="history_index.asp">
<table id="data" width="2600" border="0" cellspacing="1" cellpadding="1" align="center" class="zhuti">
<tr align="center" valign="middle">
<td bgcolor="#FFFF99" height="30" class="title1"><font size="2" color="#336699">科目</font></td>
<%for i=0 to count_y%>
<td bgcolor="#FFFF99" height="30" class="title1"><font size="2" color="#336699"><%=you(i)%></font></td>
<%next%>
</tr>
<%for ee=0 to count_k%>
<%if ee mod 2 = 0 then%>
<tr align="center" valign="middle" bgcolor="#EAE8E8">
<%else%>
<tr align="left" valign="middle" bgcolor="#FFFFFF">
<%end if%>
<td>
<div align="left"><font size="2" color="#000000"><%=kemu(ee)%></font></div>
</td>
<%for rr=0 to count_y%>
<td>
<div align="left"><font size="2" color="#000000">
<%
if vartype(ky(ee,rr))=1 then
response.write ee&"-"&rr
else
response.write ky(ee,rr)
end if
%>
</font></div>
</td>
<%next%>
</tr>
<%next%>
</table>
<div align="center">
<input type="button" name="out_excel" onClick="AutomateExcel();" value="导出到excel" class="notPrint">
<input type="submit" name="Submit" value="备份当前数据">
</div>
</form>
<div align="center"> </div>
<%
'结束后
endtime=timer()
getime=FormatNumber((endtime-startime)*1000,3)
response.write getime&"毫秒"&"<BR>"
%>
</body>
</html>
以下是源代码：
<%
if session("u_id")="" then
response.redirect "index.asp"
end if
%>
<!--#include file=cn.asp-->
<script language="javascript">
<!--
function AutomateExcel()
{
// Start Excel and get Application object.
var oXL = new ActiveXObject("Excel.Application");
// Get a new workbook.
var oWB = oXL.Workbooks.Add();
var oSheet = oWB.ActiveSheet;
var table = document.all.data;
var hang = table.rows.length;
var lie = table.rows(0).cells.length;
// Add table headers going cell by cell.
for (i=0;i<hang;i++)
{
for (j=0;j<lie;j++)
{
oSheet.Cells(i+1,j+1).value = table.rows(i).cells(j).innerText;
}
}
oXL.Visible = true;
oXL.UserControl = true;
}
//
-->
</script>
<html>
<head>
<title>年度预算费用汇总表</title>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<link rel="stylesheet" href="default1.css" type="text/css">
</head>
<body bgcolor="#336699" text="#000000" leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
<p align="center"><b><font size="4" color="#000000"><object style="border:0px" type="text/x-scriptlet" data="top.asp" width=100% height=75>
</object><br>
<br>
<font color="#FFFFFF"><%=year(date())+1%>年生产作业费</font></font></b><font size="4" color="#FFFFFF"><b>预算汇总表
</b></font><font size="4" color="#000000"> </font> </p>
<%
'文件开始执行时
startime=timer()
Server.ScriptTimeout = 500
set rs3 = Server.CreateObject("ADODB.Recordset") '科目
sql3="select * from kemu order by k_order asc"
rs3.open sql3,cn,3
count_k=rs3.recordcount-1
set rs4 = Server.CreateObject("ADODB.Recordset") '油田
sql4="select * from you order by y_order asc"
rs4.open sql4,cn,3
count_y=rs4.recordcount-1
''''''''''''''''''''建立数组'''''''''''''''''''''''''
'数据数组
dim ky()
redim ky(count_k,count_y)
'科目名称数组及科目code数组
dim kemu()
dim kemu_code()
redim kemu(count_k)
redim kemu_code(count_k)
for ii=0 to count_k '产生科目级别的空格
for kk=0 to rs3("k_depth")*2
nn=nn&" "
next
kemu(ii)=nn&rs3("k_name")
nn=""
kemu_code(ii)=rs3("k_code")
rs3.movenext
if rs3.eof then exit for
next
rs3.movefirst
'油田数组
dim you()
redim you(count_y)
for jj=0 to count_y
you(jj)=rs4("y_name")
rs4.movenext
if rs4.eof then exit for
next
rs4.movefirst
''''''''''''''''''''''''''''''''''''''''''''''''''''''
dim f,c
dim f2,c2
for ee=0 to count_k
rs4.movefirst
for rr=0 to count_y
c=""
call aaa(rs3("k_code"))
c2=""
call bbb(rs4("y_code"))
'根据SQL查数据，把值转给数据单元
set rs5 = Server.CreateObject("ADODB.Recordset")
sql5="select sum(d_z) as sum_d_z from ysdata where ("&left(c,len(c)-3)&") and ("&left(c2,len(c2)-3)&") and d_year='"&year(date)+1&"'"
rs5.open sql5,cn,3
'response.write sql5&"<br>"&"<br>"
ky(ee,rr)=rs5("sum_d_z")
if vartype(ky(ee,rr))=1 then
ky(ee,rr)=0
end if
rs5.close
set rs5=nothing
rs4.movenext
if rs4.eof then exit for
next
rs3.movenext
if rs3.eof then exit for
next
rs3.close
set rs3=nothing
rs4.close
set rs4=nothing
'取得科目串
sub aaa(a)
set rs11 = Server.CreateObject("ADODB.Recordset")
'sql11="select * from kemu where k_parent='"&a&"' order by k_order asc"
sql11="select * from kemu where k_parent='"&a&"' order by k_order asc"
rs11.open sql11,cn,3
if rs11.eof then
f=a
c=c&"k_code="&f&" or "
else
for i=1 to rs11.recordcount
f=rs11("k_code")
call aaa(rs11("k_code"))
rs11.movenext
if rs11.eof then exit for
next
end if
end sub
'取得油田串
sub bbb(b)
set rs22 = Server.CreateObject("ADODB.Recordset")
sql22="select * from you where y_parent='"&b&"'"
rs22.open sql22,cn,3
if rs22.eof then
f2=b
c2=c2&"y_code="&f2&" or "
else
for j=1 to rs22.recordcount
f2=rs22("y_code")
call bbb(rs22("y_code"))
rs22.movenext
if rs22.eof then exit for
next
end if
end sub
%>
<form name="form1" method="post" action="history_index.asp">
<table id="data" width="2600" border="0" cellspacing="1" cellpadding="1" align="center" class="zhuti">
<tr align="center" valign="middle">
<td bgcolor="#FFFF99" height="30" class="title1"><font size="2" color="#336699">科目</font></td>
<%for i=0 to count_y%>
<td bgcolor="#FFFF99" height="30" class="title1"><font size="2" color="#336699"><%=you(i)%></font></td>
<%next%>
</tr>
<%for ee=0 to count_k%>
<%if ee mod 2 = 0 then%>
<tr align="center" valign="middle" bgcolor="#EAE8E8">
<%else%>
<tr align="left" valign="middle" bgcolor="#FFFFFF">
<%end if%>
<td>
<div align="left"><font size="2" color="#000000"><%=kemu(ee)%></font></div>
</td>
<%for rr=0 to count_y%>
<td>
<div align="left"><font size="2" color="#000000">
<%
if vartype(ky(ee,rr))=1 then
response.write ee&"-"&rr
else
response.write ky(ee,rr)
end if
%>
</font></div>
</td>
<%next%>
</tr>
<%next%>
</table>
<div align="center">
<input type="button" name="out_excel" onClick="AutomateExcel();" value="导出到excel" class="notPrint">
<input type="submit" name="Submit" value="备份当前数据">
</div>
</form>
<div align="center"> </div>
<%
'结束后
endtime=timer()
getime=FormatNumber((endtime-startime)*1000,3)
response.write getime&"毫秒"&"<BR>"
%>
</body>
</html>
...全文
请发表友善的回复…
发表回复
shuishang 2006-08-03
- 打赏
- 举报
不会.
===============================================================
域名注册 虚拟主机 FTP空间 数据库
企业邮局 托管租用 网站建设 代理合作
网址: http://www.huike007.net
在线客服QQ:31926944 MSN:huike007@hotmail.com
===============================================================
域名注册 虚拟主机 FTP空间 数据库
企业邮局 托管租用 网站建设 代理合作
网址: http://www.huike007.net
在线客服QQ:31926944 MSN:huike007@hotmail.com
jspadmin 2006-08-03
- 打赏
- 举报
mezzi100(MSMVP八卦兔子)
又是谁的马甲？
又是谁的马甲？
张三大胖 2006-08-03
- 打赏
- 举报
精神上支持，智力上我没有办法
cch345 2006-08-03
- 打赏
- 举报
set rs3 = Server.CreateObject("ADODB.Recordset") '科目
sql3="select * from kemu order by k_order asc"
rs3.open sql3,cn,3
count_k=rs3.recordcount-1
用这个打个比方
查询不要用"*",用几个取几个
"order by k_order asc"此句可以不用,"order by"有点费资源
rs3用完后关闭
rs3.close
Set rs3=nothing
sql3="select * from kemu order by k_order asc"
rs3.open sql3,cn,3
count_k=rs3.recordcount-1
用这个打个比方
查询不要用"*",用几个取几个
"order by k_order asc"此句可以不用,"order by"有点费资源
rs3用完后关闭
rs3.close
Set rs3=nothing
mezzi100 2006-08-03
- 打赏
- 举报
把这些都交给存储过程处理,调用结果集就行了.
swazn_yj 2006-08-03
- 打赏
- 举报
这样的报表最好是修改数据结构，尽量用一次查询就列出想要的格式？
笨笨兔兔兔兔兔 2006-08-03
- 打赏
- 举报
rs.getrows
先用这个,直接变数组
如果想变字符串用join的方法
先用这个,直接变数组
如果想变字符串用join的方法
jordan4 2006-08-03
- 打赏
- 举报
自己顶:P
leohuang 2006-08-03
- 打赏
- 举报
把数据库结构贴出来
生成什么样的报表也贴出来,帮你写个SQL
生成什么样的报表也贴出来,帮你写个SQL
你也叫风语者 2006-08-03
- 打赏
- 举报
CREATE PROCEDURE TestProc
AS
BEGIN
select * from kemu order by k_order asc
select * from you order by y_order asc
END
GO
=============================
asp调用
=============================
set rs = Server.CreateObject("ADODB.Recordset")
set rs1 = Server.CreateObject("ADODB.Recordset")
sql = "Exec TestProc"
set rs = conn.execute(sql)
dim rs1
set rs1= rs.NextRecordset '读取后一个记录集的内容
jordan4 2006-08-03
- 打赏
- 举报
那么存储过程应该怎么写啊，小弟不会啊!
笨笨兔兔兔兔兔 2006-08-03
- 打赏
- 举报
不是我的,我也不知道是谁的....
