title: ADODB Command.Execute Timeout
body: <p>I am seeing a timeout error when running a query from Excel vba</p>
  
  <p>The query takes &lt;2 seconds from the SQL Server Management Studio, but from vba the timeout happens at 2 minutes with nothing returned</p>
  
  <p>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</p>
  
  <p>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</p>
  
  <p>This is my vba connection string code:</p>
  
  <pre><code>If svrCon Is Nothing Then
      Set svrCon = New ADODB.Connection
  End If
  
  If Not CheckServerConnectionState Then
  
      conStr = "Provider=SQLOLEDB;Data Source=ussantapps332;" &amp; _
                      "Initial Catalog=Global_OEE_Data_Capture_Dev;User Id=sqluser;Password=*****;"
  
      ' Open the connection
      svrCon.ConnectionTimeout = 0
      svrCon.Open conStr
  
  End If
  </code></pre>
  
  <p>This is my vba SELECT code:</p>
  
  <pre><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
  </code></pre>
  
  <p>The sql string and parameters are passed into the function, the connection is opened from another method</p>
  
  <p>The query is:</p>
  
  <pre><code>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 &gt;= '2014-07-01' AND FYStart &lt; '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 &gt;= '2015-03-01' AND TgtMonth &lt; '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 &gt;= '2015-03-05 23:00' AND U.TimeLineStart &lt; '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
  </code></pre>