我写的一个论坛的代码,用的是分页,给你看看吧'Dim Rst As ADODB.Recordset Dim RstChild As ADODB.Recordset Dim SQL As String Dim I As Integer Dim J As Integer Dim Nod As Node Dim NodChild As StringIf lstBBS.Selected = -1 Then Exit Sub tvwCap.Nodes.Clear Set RstBBs = Nothing Set RstBBs = New ADODB.Recordset RstBBs.CursorLocation = adUseClient SQL = "select * from bbsmatter where bbsid=" & BBS(lstBBS.Selected) RstBBs.Open SQL, Conn, adOpenStatic, adLockReadOnly, adCmdText RstBBs.PageSize = 8Page = 1 If RstBBs.EOF Then GoTo 1 For I = 1 To RstBBs.PageSize Set Nod = tvwCap.Nodes.Add(, , "n" & CStr(RstBBs.Fields("id")), RstBBs.Fields("caption") & _ " 发表于" & RstBBs.Fields("date") & " " & RstBBs.Fields("time") & " 阅读" & RstBBs.Fields("reading") & _ " 回复" & RstBBs.Fields("return") & " 作者 " & CStr(RstBBs.Fields("userid")), Val(RstBBs.Fields("imageindex"))) Set RstChild = New ADODB.Recordset RstChild.CursorLocation = adUseClient SQL = "select * from bbsreturn where bbsid=" & BBS(lstBBS.Selected) & " and topicid=" & RstBBs.Fields("id") RstChild.Open SQL, Conn, adOpenStatic, adLockReadOnly, adCmdText
If Not RstChild.EOF Then For J = 1 To RstChild.RecordCount NodChild = RstChild.Fields("matter") If Len(NodChild) > 15 Then NodChild = Mid(NodChild, 1, 12) & "..." Set Nod = tvwCap.Nodes.Add("n" & CStr(RstBBs.Fields("id")), tvwChild, "r" & CStr(RstBBs.Fields("id")) & "_" & RstChild.Fields("id"), "回复: " _ & NodChild & " 发表于" & RstChild.Fields("date") & " " & RstChild.Fields("time") & _ " 作者 " & CStr(RstChild.Fields("userid")), Val(RstChild.Fields("imageindex"))) RstChild.MoveNext Next End If RstChild.Close Set RstChild = Nothing RstBBs.MoveNext If RstBBs.EOF Then GoTo 1 Next
cuizm(射天狼) ( ) : 你写的select top 4 id,ip from aa where id in (select top 8 id from aa) order by id desc; //id,ip为字段名称,aa为表名称 //这是取表中的第5条到第8条数据正是我需要的:但经我实验不成功,还是前4条数据,不第5条到第8条数据?
Dim RstChild As ADODB.Recordset
Dim SQL As String
Dim I As Integer
Dim J As Integer
Dim Nod As Node
Dim NodChild As StringIf lstBBS.Selected = -1 Then Exit Sub
tvwCap.Nodes.Clear
Set RstBBs = Nothing
Set RstBBs = New ADODB.Recordset
RstBBs.CursorLocation = adUseClient
SQL = "select * from bbsmatter where bbsid=" & BBS(lstBBS.Selected)
RstBBs.Open SQL, Conn, adOpenStatic, adLockReadOnly, adCmdText
RstBBs.PageSize = 8Page = 1
If RstBBs.EOF Then GoTo 1
For I = 1 To RstBBs.PageSize
Set Nod = tvwCap.Nodes.Add(, , "n" & CStr(RstBBs.Fields("id")), RstBBs.Fields("caption") & _
" 发表于" & RstBBs.Fields("date") & " " & RstBBs.Fields("time") & " 阅读" & RstBBs.Fields("reading") & _
" 回复" & RstBBs.Fields("return") & " 作者 " & CStr(RstBBs.Fields("userid")), Val(RstBBs.Fields("imageindex")))
Set RstChild = New ADODB.Recordset
RstChild.CursorLocation = adUseClient
SQL = "select * from bbsreturn where bbsid=" & BBS(lstBBS.Selected) & " and topicid=" & RstBBs.Fields("id") RstChild.Open SQL, Conn, adOpenStatic, adLockReadOnly, adCmdText
If Not RstChild.EOF Then
For J = 1 To RstChild.RecordCount
NodChild = RstChild.Fields("matter")
If Len(NodChild) > 15 Then NodChild = Mid(NodChild, 1, 12) & "..."
Set Nod = tvwCap.Nodes.Add("n" & CStr(RstBBs.Fields("id")), tvwChild, "r" & CStr(RstBBs.Fields("id")) & "_" & RstChild.Fields("id"), "回复: " _
& NodChild & " 发表于" & RstChild.Fields("date") & " " & RstChild.Fields("time") & _
" 作者 " & CStr(RstChild.Fields("userid")), Val(RstChild.Fields("imageindex")))
RstChild.MoveNext
Next
End If
RstChild.Close
Set RstChild = Nothing
RstBBs.MoveNext
If RstBBs.EOF Then GoTo 1
Next
第二就是如楼上说的,在写SELECT的时候只查询你需要的数据,想想700多万条记录全部返回记录集要多大的内存..如果不是用于统计之类计算的话应该不需要显示这么多的记录(其实在SQLSERVER中可以在存储过程中完成统计,不需要返回记录集).
第三在你需要查询的字段要索引,这样查询会快很多.
第四像二楼那位说的分页对你来说没有意义,你说的慢主要是在SQL的执行和FILL到RECORDSET中所花的时间长,如果真的要分页应该在SELECT中加入TOP和ORDER BY,用它们帮你分页.
第五在返回记录集以后把记录FILL到数组中,RECORDSET中有一个GETROW方法,用于这个功能.
//id,ip为字段名称,aa为表名称
//这是取表中的第5条到第8条数据
//id,ip为字段名称,aa为表名称
//这是取表中的第5条到第8条数据正是我需要的:但经我实验不成功,还是前4条数据,不第5条到第8条数据?