sql2005数据库 经典分页 速度慢

VB code

'环境如下：
'asp代码在第1台服务器，配置就不说了
'sql2005在第2台服务器，配置为4核，16G内存，win2003系统

Dim Conn    
Sub OpenConn
    Set Conn=server.createobject("ADODB.CONNECTION")
    Conn.ConnectionTimeout = 20 '设置connection对象连接数据库的逾期时间
    Conn.CommandTimeout = 20 '设置执行execute方法时间
'    Conn.CursorLocation = 3 '将记录存放在游览器端
    Conn.open"provider=sqloledb;persist security info=false;user id=帐号;password=密码;database=数据库名;server=服务器地址;"
End Sub

Sub CloseConn()
    Conn.Close
    Set Conn=Nothing
End Sub

Dim sql : sql = "select ID, BusinessName from business"
Call OpenConn()
Dim rs : Set rs = Server.CreateObject("ADODB.RecordSet")
'rs.CursorLocation = 3    '将记录存放在浏览器端,按道理是提供速度的.但实际执行这句会很慢
rs.Open sql,conn,1,1
rs.PageSize = 20        '//设置每页显示的记录数
rs.AbsolutePage = 1
'Dim a : a = rs.PageCount    '//注释这句读取1秒不到,如果执行这句后,读取数据相当的慢
'Dim b : b = rs.RecordCount    '//情况同上
If Not(rs.Bof And rs.Eof) Then
    Dim i : i = 0
    Do While Not rs.Eof And i < 20
        Response.Write rs("BusinessName") & ","
        i = i + 1
        rs.MoveNext
    Loop
End If
rs.Close()
Set rs = Nothing
Call CloseConn()

2005的连接用 Provider=SQLNCLI;Server=127.0.0.1;Database=xxxx;Uid=sa;Pwd=sa;
rs.CursorLocation = 3
rs.CacheSize = 20
试试看