ADODB命令.执行超时

从Excel vba运行查询时出现超时错误

查询采用<来自SQL Server Management Studio的2秒,但来自vba的超时时间为2分钟,没有返回任何内容

在设置Command对象时,是否有什么我做得不对?我注意到ADODB似乎速度较慢,但从未像这样

查询连接多个表并执行一些其他计算,但从1.5秒变为>2分钟一定意味着我错过了vba

这是我的vba连接字符串代码:

If svrCon Is Nothing Then
    Set svrCon = New ADODB.Connection
End If

If Not CheckServerConnectionState Then

    conStr = "Provider=SQLOLEDB;Data Source=ussantapps332;" & _
                    "Initial Catalog=Global_OEE_Data_Capture_Dev;User Id=sqluser;Password=*****;"

    ' Open the connection
    svrCon.ConnectionTimeout = 0
    svrCon.Open conStr

End If

这是我的vba SELECT代码:

Dim cmd As ADODB.Command
Dim par As ADODB.Parameter
Dim rst As ADODB.Recordset        

' Create command object
Set cmd = New ADODB.Command

cmd.CommandTimeout = 120
cmd.ActiveConnection = svrCon
cmd.CommandText = sql


' Create parameter object
If IsArrayInitialized(params) Then
    For x = 0 To UBound(params)
        If IsNull(params(x, 1)) Then
            Set par = cmd.CreateParameter(Type:=params(x, 0), Size:=1)
        Else
            Set par = cmd.CreateParameter(Type:=params(x, 0), Size:=Len(params(x, 1)) + 1)
        End If
        par.Value = params(x, 1)

        cmd.Parameters.Append par

        Set par = Nothing

        DoEvents
    Next
End If


' Open recordset object
On Error GoTo ExecuteError
Debug.Print Format(Now, "hh:mm:ss")
Set rst = cmd.Execute
Debug.Print Format(Now, "hh:mm:ss")
On Error GoTo 0

sql字符串和参数被传递到函数中,连接从另一个方法打开

查询是:

SELECT U.UnitsID, L.LineName, V.VSName, O.OperatorShift, O.LineLeader, O.CotyOps, O.TempOps, U.WorkOrder, U.ProductCode,
           S.ProdDesc, U.TimeLineStart, U.TimeLineEnd, U.UnitsProduced, U.ActLineSpeed, U.TgtLineSpeed, SUM(CASE WHEN C.DTIncludedInOEE = 0 THEN D.DowntimeLength ELSE 0 END),
           U.OfflineTaskID, R.Rate, S.LabHrsPerThou, S.PHeads, T.TgtOEE, T.TgtEff, T.TgtProd
      FROM dataUnits U
        LEFT JOIN dataOperatorNames O ON O.OperatorID = U.OperatorNameID
        INNER JOIN setupLines L ON U.LineID = L.LineID
        INNER JOIN setupValueStreams V on V.VSID = L.VSID
        INNER JOIN setupPUs P ON V.PUID = P.PUID
        LEFT JOIN dataDowntimes D ON U.UnitsID = D.UnitsID
        LEFT JOIN setupDowntimes sD ON D.DTID = sD.DTID
        LEFT JOIN setupDowntimeCats C ON sD.DTCatID = C.DTCatID
        LEFT JOIN (SELECT VSID, AVG(RateVal) Rate
                 FROM dataRates WHERE FYStart >= '2014-07-01' AND FYStart < '2015-07-01'
                        GROUP BY VSID) R ON R.VSID = L.VSID
        LEFT JOIN dataStandards S ON S.ProdCode = U.ProductCode
        LEFT JOIN (SELECT LineID, AVG(TgtOEE) TgtOEE, AVG(TgtEff) TgtEff, AVG(TgtProd) TgtProd
                    FROM dataTargets WHERE TgtMonth >= '2015-03-01' AND TgtMonth < '2015-04-01'
                            GROUP BY LineID) T ON L.LineID = T.LineID
      WHERE (S.SAPVersion = (SELECT MIN(SAPVersion) FROM dataStandards s2 WHERE s2.ProdCode = S.ProdCode)
            OR S.SAPVersion IS NULL)
      AND P.SiteID = 2 AND U.TimeLineStart >= '2015-03-05 23:00' AND U.TimeLineStart < '2015-03-31 23:00'
      GROUP BY U.UnitsID, L.LineName, V.VSName, O.OperatorShift, O.LineLeader, O.CotyOps, O.TempOps, U.WorkOrder, U.ProductCode, S.ProdDesc, U.TimeLineStart,
               U.TimeLineEnd, U.UnitsProduced, U.ActLineSpeed, U.TgtLineSpeed, U.OfflineTaskID, R.Rate, S.LabHrsPerThou, S.PHeads, T.TgtOEE, T.TgtEff, T.TgtProd
      ORDER BY U.TimeLineStart ASC