asp 查询双access库占用服务器资源的解决办法，急~~~~~

bigheadfd 2009-05-02 03:01:41

我的程序有两个access库,现在我做了一个双库的查询,
用一个conn文件,建立了两个库连接,分别用conn和connItem命名两个连接,然后在程序中对这两个库进行数据名称的查询,结果执行这个查询程序会很慢而且只要执行这个查询程序,就会导致网站首页(静态的)无法显示,直到查询完成才会正常显示静态首页,另外的我数据库里有3W多条数据,请问下高手,程序需要如何改才能改善查询速度,降低查询的服务器资源消耗;急!!!!!
原代码:
1、连接文件
On Error Resume Next
dbpath="第一个access数据库物理地址"'数据库连接地址
Set conn=Server.CreateObject("adodb.connection")
db="provider = Microsoft.Jet.OLEDB.4.0;Data Source ="&server.mappath(dbpath)
conn.open db
sub connclose()
conn.close
set conn=nothing
end sub
dim connstrItem
dim connItem,Itemdata
Set connItem = Server.CreateObject("ADODB.Connection")
Itemdata="第二个access数据库物理地址（与第一个不在同一目录）"
connstrItem="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(Itemdata)
connItem.Open connstrItem
If Err Then
err.Clear
Set ConnItem = Nothing
Response.Write "数据库链接错误，请检查根目录中数据库连接文件的连接设置。"
Response.End
End If

Sub CloseConnItem()
ConnItem.close
Set ConnItem=nothing
End Sub
2、查询程序原码
<%
dim content="任一名称"
set rsss=server.CreateObject("ADODB.recordset")
If content="" Then
sql="select top 25 * from [表] order by djnum"
Else
sql="select top 25 * from [表] where name like '%"&content&"%'"
End If
rsss.CursorLocation = 3
rsss.open sql,conn,1,1
if rsss.eof and rsss.bof then
response.write 暂无数据"
Else
maxk=rsss.recordcount
%>
<TD class=num1><%=maxk%>(最多25个，以后忽略)</TD>
<TD class=all_right><A onclick=changeEngine(0);
href="javascript:void(0);">
<DIV id=keyengine0>点击查看</DIV></A></TD></TR>
<TR id=key0 style="DISPLAY: none">
<TD colSpan=4>
<TABLE cellSpacing=0 cellPadding=0 border=0 width=720>
<TBODY>
<TR>
<TD width="35%"><center>缩略名称</center></TD>
<TD width="10%"><center>点击次数</center></TD>
<TD width="35%"><center>简单介绍</center></TD>
<TD width="20%"><center>----</center></TD>
</TR>
<%
Do While Not rsss.Eof
%>
<TR>
<TD><center><A href="read_bao.asp?ID=<%=rsss("id")%>" target=_blank><%=replace(rsss("name"),content,"<font color='red'>"&content&"</font>")%></center></A></TD>
<TD><center><%=rsss("djnum")%></center></TD>
<TD><DIV align=left><center><A href="read_bao.asp?ID=<%=rsss("id")%>" target=_blank><%=rsss("easysay")%></a></center></TD>
<TD><center><A href="read.asp?ID=<%=rsss("id")%>" target=_blank>更多</a></center></TD>
</TR>
<%
Rsss.MoveNext
Loop

response.write "</TBODY></TABLE></TD></TR>"
End If
Rsss.Close
Set Rsss=Nothing

<%

set rsss=server.CreateObject("ADODB.recordset")
If content="" Then
sql="select top 25 * from [表] order by id"
Else
sql="select top 25 * from 表 where list_name like '%"&content&"%' ORDER BY ID desc"
End If
rsss.open sql,conn,1,1
if rsss.eof and rsss.bof then
response.write "暂无数据"
Else
maxk=rsss.recordcount
%>
<TD class=num1><%=maxk%>(最多25个，以后忽略)</TD>
<TD class=all_right><A onclick=changeEngine(2); href="javascript:void(0);"><DIV id=keyengine2>点击查看</DIV></A></TD>
</TR>
<TR id=key2 style="DISPLAY: none">
<TD colSpan=5>
<TABLE cellSpacing=0 cellPadding=0 border=0 width=720>
<TBODY>
<TR>
<TD width="20%">名称</TD>
<TD width="15%">作者</TD>
<TD width="15%">点击次数</TD>
<TD width="40%">现在至</TD>
<TD width="10%">----</TD>
</TR>
<%
Do While Not rsss.Eof
%>
<TR>
<TD><a target="_blank" href="book.asp?id=<%=rsss("id")%>"><%=replace(rsss("list_name"),content,"<font color='red'>"&content&"</font>")%></a></TD>
<TD><%=rsss("list_user")%></TD>
<TD><%=rsss("list_hit")%></TD>
<TD><a target="_blank" href="view_book.asp?id=<%=rsss("newviewid")%>"><%=rsss("newviewname")%></a></TD>
<TD><a target="_blank" href="look_book.asp?id=<%=rsss("id")%>">去侃侃</a></TD>
</TR>
<%
Rsss.MoveNext
Loop
response.write "</TBODY></TABLE></TD></TR>"
End If
Rsss.Close
Set Rsss=Nothing

Call connclose()
%>

<%

set rsss=server.CreateObject("ADODB.recordset")
If content="" Then
sql="select top 25 * from [表] order by lasttime desc"
Else
sql="select top 25 * from [表] where title like '%"&content&"%' order by lasttime desc"
End If
'rsss.CursorLocation = 3
rsss.open sql,connItem,1,1
if rsss.eof and rsss.bof then
response.write "暂无数据"
Else
maxk=rsss.recordcount
%>
<TD class=num1><%=maxk%>(最多25个，以后忽略)</TD>
<TD class=all_right><A onclick=changeEngine(1);
href="javascript:void(0);">
<DIV id=keyengine1>点击查看</DIV></A></TD></TR>
<TR id=key1 style="DISPLAY: none">
<TD colSpan=5>
<TABLE cellSpacing=0 cellPadding=0 border=0 width=720>
<TBODY>
<TR>
<TD width="25%">缩略名称</TD>
<TD width="15%">查看</TD>
<TD width="15%">发表人</TD>
<TD width="30%">简介</TD>
<TD width="15%">-----</TD>
</TR>
<%
Do While Not rsss.Eof
%>
<TR>
<TD><a href="leadbbs/a/a.asp?ID=<%=rsss("id")%>" target="_blank"/><%=Left(replace(rsss("title"),content,"<font color='red'>"&content&"</font>"),50)%></a></TD>
<TD><%=rsss("childnum")%>/<%=rsss("hits")%></TD>
<TD><%=rsss("username")%></TD>
<TD><a href="a.asp?ID=<%=rsss("id")%>" target="_blank"/><%=Left(replace(rsss("content"),content,"<font color='red'>"&content&"</font>"),50)%></a></TD>
<TD><a href="leadbbs/a/a.asp?ID=<%=rsss("id")%>" target="_blank"/>去看看</a></TD>
</TR>
<%
Rsss.MoveNext
Loop
response.write "</TBODY></TABLE></TD></TR>"
End If
Rsss.Close
Set Rsss=Nothing
Call CloseConnItem()
%>

3W多数据，查询起来很慢，而且在执行过程中，首页静态无法显示，只能等查询结束，才能正常显示，这样就影响了多用户体验，请教下应该如何改才能降低查询的资源消耗！

...全文