使用如下代码读取保存在数据库中二进制字段的数据,并在本地生成文件,然后打开
在几百条数据的情况下,即使id做了索引,至少也要10秒!!如何才能提高速度?谢谢!'从数据库中读取数据,保存成文件
Sub OpenFile(myID As String)
On Error GoTo errmsg
Dim strFileName As String
Dim strName As String
Dim strLx As String
Dim iStm As New ADODB.Stream
Dim iRe As New ADODB.Recordset
Dim Conn As New ADODB.Connection
Conn.Open strConnection
'打开表
iRe.Open "myDoc", Conn, adOpenKeyset, adLockReadOnly
iRe.Filter = "id=" & myID
strName = iRe("文件名")
strFileName = App.Path & "\" & strName
strLx = iRe("文档类型")
'保存到文件
Set iStm = New ADODB.Stream
With iStm
.Mode = adModeReadWrite
.Type = adTypeBinary
.Open
.Write iRe("内容")
.SaveToFile strFileName, adSaveCreateOverWrite
End With
'关闭对象
iRe.Close
Set iRe = Nothing
iStm.Close
Set iStm = Nothing
Conn.Close
Set Conn = Nothing
ShellExecute 0, vbNullString, strFileName, "", "", 3
Exit Sub
errmsg:
Exit Sub
End Sub
在几百条数据的情况下,即使id做了索引,至少也要10秒!!如何才能提高速度?谢谢!'从数据库中读取数据,保存成文件
Sub OpenFile(myID As String)
On Error GoTo errmsg
Dim strFileName As String
Dim strName As String
Dim strLx As String
Dim iStm As New ADODB.Stream
Dim iRe As New ADODB.Recordset
Dim Conn As New ADODB.Connection
Conn.Open strConnection
'打开表
iRe.Open "myDoc", Conn, adOpenKeyset, adLockReadOnly
iRe.Filter = "id=" & myID
strName = iRe("文件名")
strFileName = App.Path & "\" & strName
strLx = iRe("文档类型")
'保存到文件
Set iStm = New ADODB.Stream
With iStm
.Mode = adModeReadWrite
.Type = adTypeBinary
.Open
.Write iRe("内容")
.SaveToFile strFileName, adSaveCreateOverWrite
End With
'关闭对象
iRe.Close
Set iRe = Nothing
iStm.Close
Set iStm = Nothing
Conn.Close
Set Conn = Nothing
ShellExecute 0, vbNullString, strFileName, "", "", 3
Exit Sub
errmsg:
Exit Sub
End Sub
修改一下,换一下打开方式。
iRe.Filter = "id=" & myID 如果将这里改成T-SQL查询语句,是否可以提高速度?
2、一个动作,从出库,到成文件,到客户看到结果,大于3秒,就让人感觉不舒服,百十来条记录,10秒完成任务肯定不行,我从30万条记录中出40条记录还不到1秒(SQL Sever库)。
3、用timer函数测一下,你的这段程序用时大约多少秒。
Set iStm = New ADODB.Stream
With iStm
.Mode = adModeReadWrite
.Type = adTypeBinary
.Open
.Write iRe("内容")
.SaveToFile strFileName, adSaveCreateOverWrite
End With
写磁盘速度是计算机中动作最慢的一项事,因为硬盘速度是毫秒级的。
如果这段时间较长,建议采用#1楼的建议,因为这里你可能已经无法提高速度。
使用sql语句,可以提高到7秒左右,写磁盘文件大概不到1秒
查询耗时还是太长,好像带有image字段的库select就很久,有什么办法吗?