ADODB Command.Execute Timeout

Question

I am seeing a timeout error when running a query from Excel vba

The query takes <2 seconds from the SQL Server Management Studio, but from vba the timeout happens at 2 minutes with nothing returned

Is there something i am not doing right setting up the Command object? I have noticed that ADODB seems to be slower but never anything like this

The query joins several tables and does some other calculations, but going from 1.5 seconds to >2 minutes must mean something in the vba that I have missed

This is my vba connection string code:

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

This is my vba SELECT code:

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

The sql string and parameters are passed into the function, the connection is opened from another method

The query is:

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

Is my understanding correct that queries run from SQL Server Management Studio and from Excel VBA have the same input parameters and are run against the same database? — Michał Komorowski
– Michał Komorowski, Commented Apr 1, 2015 at 7:48
yes the query is the same, i used the same parameters and the database is the same — iabbott
– iabbott, Commented Apr 1, 2015 at 7:53
Did you try to run this query in Excel VBA two times one by one. I'd like to confirm that it always takes 2 minutes to execute it and not only for the first time. — Michał Komorowski
– Michał Komorowski, Commented Apr 1, 2015 at 7:59
It takes longer then 2 mins, i have set that as the timeout and it errors each time. I am changing the timeout to zero to see how long it takes, i will run it a couple of times and get back to you — iabbott
– iabbott, Commented Apr 1, 2015 at 8:01
so 3 runs - 1st took 2min 31, 2nd took 2min 27, 3rd took 2min 35 — iabbott
– iabbott, Commented Apr 1, 2015 at 8:15

Michał Komorowski · Accepted Answer · 2015-04-01 10:38:41Z

2

The discussion on the chat showed that:

The problem occurs only while connecting to the remote database.
The old SQLOLEDB provider is used.

I suggested to give a chance to a newer provider SQLNCLI which should be more effective while communicating with MSSQL. When a connection string was modified the execution time dropped from 2 minutes to 3 seconds.

answered Apr 1, 2015 at 10:38

Michał Komorowski

6,2681 gold badge23 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

iabbott Over a year ago

So finally my connection string looks like: conStr = "Provider=SQLNCLI11;Data Source= (......).

Collectives™ on Stack Overflow

ADODB Command.Execute Timeout

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related