我有一个程序在执行同样的语句,只是条件不一样时,一个会非常快,另外一个却非常慢,但是
把SQL语句拿到后台执行,均只需0.00.01秒而已。
前台的写法主体如下:
StrSql = "exec usp_POScanned_Query '" & Trim(CboProj) & "','" & Trim(PO) & "','" & Trim(Line) & "','" & Trim(Assy) & "','1','Y'"
Set RS = ExecuteQuery(StrSql)
If Not RS.EOF Then
end if执行sql的语句ExecuteQuery如下
Public Function ExecuteQuery(ByVal sql As String) As ADODB.Recordset
If Conn.State = adStateClosed Then
Conn.Open ConnectString
End If
Set ExecuteQuery = libExecuteQuery(sql)
End FunctionFunction libExecuteQuery _
( _
sql As String, _
Optional CursorType As CursorTypeEnum = adOpenUnspecified, _
Optional LockType As LockTypeEnum = adLockUnspecified, _
Optional CommandType As CommandTypeEnum = adCmdUnspecified _
) As ADODB.Recordset
On Error GoTo ErrH
Dim RS As New ADODB.Recordset
If Conn.State = adStateClosed Then Conn.Open ConnectString RS.CursorLocation = adUseClient
RS.Open sql, Conn, CursorType, LockType, CommandType
Set libExecuteQuery = RS '.Clone
Exit Function
ErrH:
Set RS = Nothing
Set libExecuteQuery = Nothing
End Function我把具体参数带入之后的语句分别为
exec usp_POScanned_Query 'SCH','SCHPO','75','LW817605010213','1','Y'
exec usp_POScanned_Query 'LTM','08GL6BF2','2','848414710','1','Y'
第一条会非常慢,第二条却非常快。但是把这两个语句放到查询分析器却非常快。
把SQL语句拿到后台执行,均只需0.00.01秒而已。
前台的写法主体如下:
StrSql = "exec usp_POScanned_Query '" & Trim(CboProj) & "','" & Trim(PO) & "','" & Trim(Line) & "','" & Trim(Assy) & "','1','Y'"
Set RS = ExecuteQuery(StrSql)
If Not RS.EOF Then
end if执行sql的语句ExecuteQuery如下
Public Function ExecuteQuery(ByVal sql As String) As ADODB.Recordset
If Conn.State = adStateClosed Then
Conn.Open ConnectString
End If
Set ExecuteQuery = libExecuteQuery(sql)
End FunctionFunction libExecuteQuery _
( _
sql As String, _
Optional CursorType As CursorTypeEnum = adOpenUnspecified, _
Optional LockType As LockTypeEnum = adLockUnspecified, _
Optional CommandType As CommandTypeEnum = adCmdUnspecified _
) As ADODB.Recordset
On Error GoTo ErrH
Dim RS As New ADODB.Recordset
If Conn.State = adStateClosed Then Conn.Open ConnectString RS.CursorLocation = adUseClient
RS.Open sql, Conn, CursorType, LockType, CommandType
Set libExecuteQuery = RS '.Clone
Exit Function
ErrH:
Set RS = Nothing
Set libExecuteQuery = Nothing
End Function我把具体参数带入之后的语句分别为
exec usp_POScanned_Query 'SCH','SCHPO','75','LW817605010213','1','Y'
exec usp_POScanned_Query 'LTM','08GL6BF2','2','848414710','1','Y'
第一条会非常慢,第二条却非常快。但是把这两个语句放到查询分析器却非常快。
而是你的数据库连接,或者VB端代码的时间占用很长而已。
你从这方面去看看