数据查询速度越慢，请高手指点!

smile8088 2007-12-17 03:11:28

查询数据库生成如附件中所示报表，数据量10000条左右，需要用时20-23秒左右，现在才两个月数据，如果是数据达到10W或以上的话那速度简直不敢想象，请高手指点一二，尽可能的通过优化代码实现提速!

基本数据库结构：
一级类别 bclass
二级类别 mclass
三级类别 sclass
一级仓库 ku1
二级仓库 ku2
三级仓库 ku3
供货商 ghs
入库信息 inku(内含品名、单位、单价、数量、一级类别、二级类别、三级类别、一级仓库、二级仓库、三级仓库、供货商，入库时间。。。等信息)

代码如下：
-------------------------

<%
oldtime=timer
if session("username")="" then
response.write"<script language='javascript'>top.location.href='../login.asp';</script>"
response.end
end if

%>


<%
if fla4="0" and fla5="0" then
response.write"<br><center><font color='#FF0000'>你不具备此权限，请与管理员联系！</font></center>"
call closedb()
response.end
end if
nowstartdate=request("startdate")
if nowstartdate="" then
nowstartdate=date()-30
end if
nowenddate=request("enddate")
if nowenddate="" then
nowenddate=date()+1
end if
%>
<html>
<head>
<title><%=dianming%> - 统计分析 </title>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<link href="../style2.css" rel="stylesheet" type="text/css">
<style>
TD { FONT-SIZE: 9pt}
</style>
<style media=print>
.Noprint{display:none;}
.PageNext{page-break-after: always;}
</style>
</HEAD>
<BODY>
<table width="880" border="1" cellspacing="1" cellpadding="2" align="center" >
<tr class="a1" style="color:#ffffff;font-size:12px;" class="TableMenu">
<th height="35" style="color:#ffffff;font-size:14px" colspan="5"><img src="../images/ico/ico09.gif" border="0" height="32" width="32"><%=request("kuname")%>原材料出库金额统计(按类别统计) </th>
</tr>
<tr bgcolor='#ececec'><td valign="top" colspan="4" >  统计部门：<%=request("kuname")%></td>
<td>  统计日期：<%=nowstartdate%> 至 <%=nowenddate%></div></td></tr>
<tr bgcolor='#ececec' style=font-size:10px height='20'><td align="center" colspan=3 ><b>统计类别</b></td><td width="100" align="center"><b>金额</b></td><td width="400" align="center"><b>供货商货款明细</b></td></tr>
<%
sql="select * from bclass order by id"
set rs_ku1=conn.execute(sql)
set rs =server.createobject("ADODB.RecordSet")
set rs1 =server.createobject("ADODB.RecordSet")
Do While Not rs_ku1.eof
response.write "<tr bgcolor='#ececec' style=font-size:10px height='20'><td width='80'>" & Trim(rs_ku1("bclass")) & "</td><td width=150>  </td><td width=150>  </td><td width='100' align='right'>"
sql="select sum(je) from inku where bclassid=" & rs_ku1("id") & " and zt=1 and addtime >= '"& nowstartdate &"' and addtime <= '"& nowenddate &"' and " & request("ku") & " = " & request("id")
rs.open sql,conn,1,3
if rs.eof then
zje=0
else
if rs(0) ="" or rs(0) = 0 or isnull(rs(0)) then
zje=0
else
zje=rs(0)
end if
end if
response.write FormatCurrency(zje) &"  </td><td width='400' align='center'> -- </td></tr>"
rs.close
sql1="select * from mclass where bclassid=" & rs_ku1("id") &" order by id"
set rs_ku2=conn.execute(sql1)
if rs_ku2.eof Then
response.write "<tr bgcolor='#ffffff' style=font-size:10px height='20'><td align='center' colspan='5'>一级分类[" &rs_ku1("bclass") &"] 下没有设置二级分类，请联系系统管理员!</td></tr>"
Else
Do While Not rs_ku2.eof
response.write "<tr bgcolor='#ffffff' style=font-size:10px height='20' ><td width='80'> </td><td width='150'>" & Trim(rs_ku2("mclass")) & "</td><td width=150>  </td><td width='100' align='right'>"
sql= "select sum(je) from inku where mclassid=" & rs_ku2("id") & " and zt=1 and addtime >= '"& nowstartdate &"' and addtime <= '"& nowenddate &"' and " & request("ku") & " = " & request("id")
rs.open sql ,conn,1,3
if rs.eof then
zje=0
else
if rs(0) ="" or rs(0) = 0 or isnull(rs(0)) then
zje=0
else
zje=rs(0)
end if
end if
response.write FormatCurrency(zje) &"  </td><td width='400' align='center'> -- </td></tr>"
rs.close

sql2="select * from sclass where mclassid=" & rs_ku2("id") & " order by id"
set rs_ku3=conn.execute(sql2)
if rs_ku3.eof Then
response.write "<tr bgcolor='#ffffff' style=font-size:10px height='20'><td align='center' colspan=5>二级分类[" &rs_ku2("mclass") &"] 下没有设置三级分类，请联系系统管理员!</td></tr>"
Else
Do While Not rs_ku3.eof
response.write "<tr bgcolor='#ffffff' style=font-size:10px height='20'><td width='80'> </td><td width=150></td><td width='150'>"& Trim(rs_ku3("sclass")) & "</td><td width='100' align='right'>"
sql="select sum(je) from inku where sclassid=" & rs_ku3("id") & " and zt=1 and addtime >= '"& nowstartdate &"' and addtime <= '"& nowenddate &"' and " & request("ku") & " = " & request("id")
rs.open sql,conn,1,3
if rs.eof then
zje=0
else
if rs(0) ="" or rs(0) = 0 or isnull(rs(0)) then
zje=0
else
zje=rs(0)
end if
end if
rs.close
ghsstr=""
rs.open"select * from ghs order by id",conn,1,3
do while not rs.eof
sql="select sum(je) from inku where sclassid=" & rs_ku3("id") & " and addtime >= '"& nowstartdate &"' and addtime < '"& nowenddate &"' and zt=1 and ghs="&rs("id") & " and " & request("ku") & " = " & request("id")
rs1.open sql,conn,1,3
if rs1.eof then
'ghsstr=""
else
if rs1(0) ="" or rs1(0) = 0 or isnull(rs1(0)) then
'ghsstr=""
else
ghsstr=ghsstr & rs("jname")& "："&formatnumber(rs1(0))&"元； "
end if
end if
rs1.close
rs.movenext
loop
rs.close

response.write FormatCurrency(zje) &"  </td><td width='400'> "&ghsstr&"</td></tr>"
rs_ku3.movenext
Loop
End If
Set rs_ku3=nothing
'
rs_ku2.movenext
Loop
End If
Set rs_ku2=Nothing
rs_ku1.movenext
Loop
Set rs_ku1=Nothing

sql="select sum(je) from inku where zt=1 and addtime >= '"& nowstartdate &"' and addtime <= '"& nowenddate &"' and " & request("ku") & " = " & request("id")
rs.open sql,conn,1,3
if rs.eof then
zje=0
else
if rs(0) ="" or rs(0) = 0 or isnull(rs(0)) then
zje=0
else
zje=rs(0)
end if
end if
rs.close
response.write"<tr bgcolor='#ececec' style=font-size:10px height='20'><td align=center colspan=5><b>小写合计："&FormatCurrency(zje)&"<b/></td></tr>"
response.write"<tr bgcolor='#ececec' style=font-size:10px height='20'><td align=center colspan=5><b>大写合计：<b/>(人民币)"&AtoC(zje)&"</td></tr>"
set rs=nothing
response.write"</table>"
nowtime=timer
thetime=cint((nowtime-oldtime)*1000)
if request("print")="yes" then%>
<object id="WebBrowser" width=0 height=0 classid="CLSID:8856F961-340A-11D0-A96B-00C04FD705A2">
</object>
<p align="center" class="Noprint">
<div align="center" class="Noprint">
<input type="button" value="打印报表" onclick="pagesetup_null();document.all.WebBrowser.ExecWB(7,1);">
</div>
</p>
<%else%>
<p align="center" class="Noprint">
<div align="center" class="Noprint">
页面执行[<font color=red><%=thetime%></font>]毫秒
<input type="button" value="打印报表" onclick="javascript:var win=window.open('lbtj2.asp?startdate=<%=nowstartdate%>&enddate=<%=nowenddate%>&print=yes&kuname=<%=request("kuname")%>&ku=<%=request("ku")%>&id=<%=request("id")%>','打印报表','width=895,height=503,top=142,left=115,toolbar=no,location=no,directories=no,status=no,menubar=no,resizable=no,scrollbars=yes'); win.focus()"">
</div>
</p>
<%
end if
response.write "</body>"
Call closedb()
%>

...全文