1180
社区成员
- 主页
在access数据库中提取数据加速
我这里有个表46万条记录 我10秒钟要从里面取出50条记录上传给服务器 表以myid(自动编号)为索引
(然后update这个记录的sent字段)
我是这样写的rs.Open "select top 50 * from 09 where sent=0 order by myid", conn2, 3, 3
这样速度很慢 如果我这样写rs.Open "select top 50 * from 09 where sent=0",conn2,3,3 速度是快了但是传出去的ID号 是不连续的,id会跳(我估计这是数据库真正存放数据的顺序)
1\有什么办法让 它既是连续的发出去 而且速度不要太慢 (比如 我记住上次的ID然后只打开前面的50条)
2\ 如果我就按rs.Open "select top 50 * from 09 where sent=0",conn2,3,3 取出数据等指针指向最后一条记录时候 下次再调用 rs.Open "select top 50 * from 09 where sent=0",conn2,3,3 它会不会把以前跳掉的记录 再找出来呢
各位朋友我对数据库不是很懂 如果您有好办法 能否写下具体语句
(然后update这个记录的sent字段)
我是这样写的rs.Open "select top 50 * from 09 where sent=0 order by myid", conn2, 3, 3
这样速度很慢 如果我这样写rs.Open "select top 50 * from 09 where sent=0",conn2,3,3 速度是快了但是传出去的ID号 是不连续的,id会跳(我估计这是数据库真正存放数据的顺序)
1\有什么办法让 它既是连续的发出去 而且速度不要太慢 (比如 我记住上次的ID然后只打开前面的50条)
2\ 如果我就按rs.Open "select top 50 * from 09 where sent=0",conn2,3,3 取出数据等指针指向最后一条记录时候 下次再调用 rs.Open "select top 50 * from 09 where sent=0",conn2,3,3 它会不会把以前跳掉的记录 再找出来呢
各位朋友我对数据库不是很懂 如果您有好办法 能否写下具体语句
...全文
请发表友善的回复…
发表回复
QWERT520 2004-10-19
conn2, 3, 3
你的打开方式是读写方式
所占用内存空间较大还是用只读方式打开比较好
如楼上的
你的打开方式是读写方式
所占用内存空间较大还是用只读方式打开比较好
如楼上的
jam021 2004-10-15
关注,帮你顶!
junki 2004-10-15
rs.Open "select top 50 * from 09 where sent=0 order by myid", conn2, 3, 3
“select top 50 *” '*是否有必要
要有序，“order by myid”肯定是要的，不然不会按ASC方式来选择记录了
“conn2, 3, 3”，建议用ADODB.adOpenForwardOnly,ADODB.adLockReadOnly, ADODB.adCmdTable
可以加快访问数据的速度
“select top 50 *” '*是否有必要
要有序，“order by myid”肯定是要的，不然不会按ASC方式来选择记录了
“conn2, 3, 3”，建议用ADODB.adOpenForwardOnly,ADODB.adLockReadOnly, ADODB.adCmdTable
可以加快访问数据的速度
fredlin 2004-10-15
Public Sub AbsolutePageX()
Dim rstEmployees As ADODB.Recordset
Dim strCnn As String
Dim strMessage As String
Dim intPage As Integer
Dim intPageCount As Integer
Dim intRecord As Integer
' Open a recordset using a client cursor
' for the employee table.
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=; "
Set rstEmployees = New ADODB.Recordset
' Use client cursor to enable AbsolutePosition property.
rstEmployees.CursorLocation = adUseClient
rstEmployees.Open "employee", strCnn, , , adCmdTable
' Display names and hire dates, five records
' at a time.
rstEmployees.PageSize = 5
intPageCount = rstEmployees.PageCount
For intPage = 1 To intPageCount
rstEmployees.AbsolutePage = intPage
strMessage = ""
For intRecord = 1 To rstEmployees.PageSize
strMessage = strMessage & _
rstEmployees!fname & " " & _
rstEmployees!lname & " " & _
rstEmployees!hire_date & vbCr
rstEmployees.MoveNext
If rstEmployees.EOF Then Exit For
Next intRecord
MsgBox strMessage
Next intPage
rstEmployees.Close
End Sub
Dim rstEmployees As ADODB.Recordset
Dim strCnn As String
Dim strMessage As String
Dim intPage As Integer
Dim intPageCount As Integer
Dim intRecord As Integer
' Open a recordset using a client cursor
' for the employee table.
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=; "
Set rstEmployees = New ADODB.Recordset
' Use client cursor to enable AbsolutePosition property.
rstEmployees.CursorLocation = adUseClient
rstEmployees.Open "employee", strCnn, , , adCmdTable
' Display names and hire dates, five records
' at a time.
rstEmployees.PageSize = 5
intPageCount = rstEmployees.PageCount
For intPage = 1 To intPageCount
rstEmployees.AbsolutePage = intPage
strMessage = ""
For intRecord = 1 To rstEmployees.PageSize
strMessage = strMessage & _
rstEmployees!fname & " " & _
rstEmployees!lname & " " & _
rstEmployees!hire_date & vbCr
rstEmployees.MoveNext
If rstEmployees.EOF Then Exit For
Next intRecord
MsgBox strMessage
Next intPage
rstEmployees.Close
End Sub
lyadlysysT 2004-10-15
select top 50 * from 09 where sent=0 order by myid