title: C# ADO.net query runs slow
body: <p>I have a query that .net builds and executes via ADO to Sql Server 2012. If ADO executes the query it times out waiting for the database to return but if I copy and past it into SQL Management studio and execute it against the same database it returns in less then a second.</p>
  
  <p>I found that wrapping the query that ADO generates in ' storing it in a SQL variable and using SP_EXECUTESQL makes it run nice and fast. Why would there be a difference? I have not changed the query in any way.</p>
  
  <p>Here is one of the queries it builds and executes (I left out all the code that dynamically builds the string that CommandText gets set to).</p>
  
  <pre><code>using(SqlConnection conn = ConnectionStringHelper.GetOpenConnection)
  using(SqlCommand cmd = conn.CreateCommand)
  {
      cmd.CommandType = CommandType.Text;
      cmd.Parameters.AddWithValue("agencyID", broadcastAgencyID);
      cmd.Parameters.AddWithValue("tableName", mapping.TableName);
      cmd.Parameters.AddWithValue("schemaName", mapping.SchemaName);
      cmd.Parameters.AddWithValue("broadcastEntityMappingID", mapping.BroadcastEntityMappingID);
  
      cmd.CommandText = @"SET NOCOUNT ON; 
          DECLARE @currentAgencyID NVARCHAR(MAX)
          DECLARE @currentFacilityID NVARCHAR(MAX)
  
          DECLARE @currentAgencyEntityBroadcastID INT
          SELECT broadcastEntity.AgencyID, broadcastEntity.FacilityID
          INTO #missingBroadcasts
          FROM [Resource].[AgencyFacility] broadcastEntity
          LEFT JOIN(
              SELECT keys.AgencyID, keys.FacilityID
                  , record.BroadcastAgencyID
                  , record.AgencyEntityBroadcastID
              FROM [Propagation].[AgencyEntityBroadcast] record
              INNER JOIN (
                  SELECT AgencyEntityBroadcastID
                      ,[AgencyID], [FacilityID]
                  FROM (
                      SELECT AgencyEntityBroadcastID
                          ,ColumnName AS [PropagationColumnName]
                          ,ColumnValue AS [PropagationColumnValue]
                      FROM Propagation.AgencyEntityBroadcastKeys
                      ) Keys
                  PIVOT(MAX(PropagationColumnValue) FOR PropagationColumnName IN (
                              [AgencyID], [FacilityID]
                              )) pivoted
                  ) keys ON keys.AgencyEntityBroadcastID = record.AgencyEntityBroadcastID
                  WHERE record.BroadcastAgencyID = @agencyID
                      AND record.BroadcastEntityMappingID = @broadcastEntityMappingID
              ) keys ON keys.BroadcastAgencyID = broadcastEntity.AgencyID
                  AND keys.AgencyID = broadcastEntity.AgencyID
              AND keys.FacilityID = broadcastEntity.FacilityID
          WHERE broadcastEntity.AgencyID = @agencyID
              AND keys.AgencyEntityBroadcastID IS NULL
          DECLARE entity_cursor CURSOR FOR
          SELECT * FROM #missingBroadcasts
  
          OPEN entity_cursor
          FETCH NEXT FROM entity_cursor
          INTO @currentAgencyID, @currentFacilityID
          WHILE @@FETCH_STATUS = 0
          BEGIN
              INSERT INTO Propagation.AgencyEntityBroadcast(
                  BroadcastAgencyID,
                  BroadcastEntityMappingID,
                  BroadcastTypeID,
                  CreatedOn,
                  ModifiedOn
              )
              VALUES(
                  @agencyID,
                  @broadcastEntityMappingID,
                  1,
                  GETUTCDATE(),
                  GETUTCDATE()
              )
  
              SET @currentAgencyEntityBroadcastID = (SELECT SCOPE_IDENTITY())
              INSERT INTO Propagation.AgencyEntityBroadcastKeys(
                  AgencyEntityBroadcastID,
                  ColumnName,
                  ColumnValue
              ) VALUES (
                  @currentAgencyEntityBroadcastID,
                  'AgencyID',
                  @currentAgencyID
              )
          INSERT INTO Propagation.AgencyEntityBroadcastKeys(
              AgencyEntityBroadcastID,
              ColumnName,
              ColumnValue
          ) VALUES (
              @currentAgencyEntityBroadcastID,
              'FacilityID',
              @currentFacilityID
          )
  
          FETCH NEXT FROM entity_cursor INTO @currentAgencyID, @currentFacilityID
          END
  
          CLOSE entity_cursor
          DEALLOCATE entity_cursor
  
          DROP TABLE #missingBroadcasts
  
          SET NOCOUNT OFF;";
  
      cmd.ExecuteNonQuery();
  }
  </code></pre>