我写了一个计算库存的存储过程,在查询分析器里执行,只需要55秒,可是如果用VB在程序里调用却用了25分钟,简直是天壤之别,差距怎么这么大的,我的Vb程序是:
Public Function ExecRemoteCommand(ByVal sSqlOrStorProName As String, _
Optional ByVal nType As WwCommandType = WwCmdText, _
Optional ByVal nRecLockType As WwRecordSetType = WwOptimistic, _
Optional ByRef sErr As String) As Recordset
Dim cmdSql As Command
Dim rec As New Recordset
Dim pra As New ADODB.Parameter
On Error GoTo ErrHandler
Set cmdSql = New Command
With cmdSql
Set .ActiveConnection = g_cnnRemoteConnection
Select Case nType
Case WwCmdStoredProc
.CommandType = adCmdStoredProc
.CommandText = sSqlOrStorProName
Case WwCmdText
.CommandText = sSqlOrStorProName
.CommandType = adCmdText
.CommandTimeout = g_nCommandTimeout
End Select
With rec
Select Case nRecLockType
Case WwReadOnly
.CursorType = adOpenKeyset
.LockType = adLockReadOnly
Case WwOptimistic
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
Case WwBatchOptimistic
.CursorType = adOpenKeyset
.LockType = adLockBatchOptimistic
End Select
End With
Set rec = .Execute
Set ExecRemoteCommand = rec
End With
Set cmdSql = Nothing
Set rec = Nothing
Exit Function
ErrHandler:
Set cmdSql = Nothing
Set rec = Nothing
sErr = Err.Description
Set ExecRemoteCommand = Nothing
End Function
×××××××××××××××××××××××××××××××××××××××××××
调用是我这样写的
sSql = "exec calcbill '" & txtchoicempno.text & "'"
ExecRemoteCommand sSql, WwCmdText, WwReadOnly, sErr
If sErr <> "" Then
g_cnnRemoteConnection.RollbackTrans
MsgBox "数据库错误,原因:" & vbCrLf & sErr, vbOKOnly + vbCritical
Exit Sub
End If哪位高手知道为什么?帮我优化一下,或者提供一个调用存储过程的方法,多谢了,在线等!
Public Function ExecRemoteCommand(ByVal sSqlOrStorProName As String, _
Optional ByVal nType As WwCommandType = WwCmdText, _
Optional ByVal nRecLockType As WwRecordSetType = WwOptimistic, _
Optional ByRef sErr As String) As Recordset
Dim cmdSql As Command
Dim rec As New Recordset
Dim pra As New ADODB.Parameter
On Error GoTo ErrHandler
Set cmdSql = New Command
With cmdSql
Set .ActiveConnection = g_cnnRemoteConnection
Select Case nType
Case WwCmdStoredProc
.CommandType = adCmdStoredProc
.CommandText = sSqlOrStorProName
Case WwCmdText
.CommandText = sSqlOrStorProName
.CommandType = adCmdText
.CommandTimeout = g_nCommandTimeout
End Select
With rec
Select Case nRecLockType
Case WwReadOnly
.CursorType = adOpenKeyset
.LockType = adLockReadOnly
Case WwOptimistic
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
Case WwBatchOptimistic
.CursorType = adOpenKeyset
.LockType = adLockBatchOptimistic
End Select
End With
Set rec = .Execute
Set ExecRemoteCommand = rec
End With
Set cmdSql = Nothing
Set rec = Nothing
Exit Function
ErrHandler:
Set cmdSql = Nothing
Set rec = Nothing
sErr = Err.Description
Set ExecRemoteCommand = Nothing
End Function
×××××××××××××××××××××××××××××××××××××××××××
调用是我这样写的
sSql = "exec calcbill '" & txtchoicempno.text & "'"
ExecRemoteCommand sSql, WwCmdText, WwReadOnly, sErr
If sErr <> "" Then
g_cnnRemoteConnection.RollbackTrans
MsgBox "数据库错误,原因:" & vbCrLf & sErr, vbOKOnly + vbCritical
Exit Sub
End If哪位高手知道为什么?帮我优化一下,或者提供一个调用存储过程的方法,多谢了,在线等!
这个问题不是很清楚
帮你顶一下
帮不上