Public Sub SQLExt(StrSql As String)
Dim cmd As New ADODB.Command
Connect
Set cmd.ActiveConnection = Conn
cmd.CommandText = StrSql
cmd.Execute
Set cmd = Nothing
Disconnect
End Sub
Public Function QueryExt(ByVal StrSql As String) As ADODB.Recordset
Dim Rs As New ADODB.Recordset
Connect
Set Rs.ActiveConnection = Conn
Rs.CursorType = 1
Rs.LockType = 1
Rs.Open StrSql
Set QueryExt = Rs
End Function
Public Sub Connect()
If IsConnect = True Then
Exit Sub
End If
Set Conn = New ADODB.Connection
Conn.ConnectionString = Cnn
Conn.Open
If Conn.State <> adStateOpen Then
MsgBox "数据库连接失败!", vbOKOnly + vbCritical, "警告"
End
End If
IsConnect = True
End Sub
Public Sub Disconnect()
If IsConnect = False Then Exit Sub
Conn.Close
Set Conn = Nothing
IsConnect = False
End Sub
以上几个是写的查询,与执行语句模块,一直用的很顺当
问题是现在有大量的循环查询,将这些查询函数嵌套在里面效率比较慢,我怀疑是不是Connect函数与Disconnect函数反复使用降低了效率,反复的链接断开耗费了大量时间,请老大们指点下,谢谢
Dim cmd As New ADODB.Command
Connect
Set cmd.ActiveConnection = Conn
cmd.CommandText = StrSql
cmd.Execute
Set cmd = Nothing
Disconnect
End Sub
Public Function QueryExt(ByVal StrSql As String) As ADODB.Recordset
Dim Rs As New ADODB.Recordset
Connect
Set Rs.ActiveConnection = Conn
Rs.CursorType = 1
Rs.LockType = 1
Rs.Open StrSql
Set QueryExt = Rs
End Function
Public Sub Connect()
If IsConnect = True Then
Exit Sub
End If
Set Conn = New ADODB.Connection
Conn.ConnectionString = Cnn
Conn.Open
If Conn.State <> adStateOpen Then
MsgBox "数据库连接失败!", vbOKOnly + vbCritical, "警告"
End
End If
IsConnect = True
End Sub
Public Sub Disconnect()
If IsConnect = False Then Exit Sub
Conn.Close
Set Conn = Nothing
IsConnect = False
End Sub
以上几个是写的查询,与执行语句模块,一直用的很顺当
问题是现在有大量的循环查询,将这些查询函数嵌套在里面效率比较慢,我怀疑是不是Connect函数与Disconnect函数反复使用降低了效率,反复的链接断开耗费了大量时间,请老大们指点下,谢谢
是否可以将
Public Sub Disconnect()
If IsConnect = False Then Exit Sub
Conn.Close
Set Conn = Nothing
IsConnect = False
End Sub
去除,一次连接上去不断开就可以了?
你的Conn应该定义模块的,在进入程序的时候,将其打开,当程序结束的时候,才关闭之,没必要每次打开关闭
另外,你的记录集如果只读的,不需要修改,那么,你不要用rS的方式打开数据
你可以使用set rs=conn.exec(sQry)方式打开,该方式速度比较快!
Rs.LockType = 1 Rs.CursorType = 3
Rs.LockType = 3我记得我当时这里赋的值都是3来着,具体记不清了,好久没注意过了,都是拷贝过来就用的。
我的连接都是短连接,从来没觉得慢过,都是查询不慢,显示才慢。如果查询很慢,就说明你的数据量很大。
在最后程序退出的时候全体断开释放就可以了.随用随连一般B/S才这么干. C/S就长连接吧