title: Huge slowdown for C# stored procedure call, parameter sniffing/optimization issues?
body: <p>I have the following code that runs a stored procedure repeatedly. It works pretty well when I run the SQL statement literally, so I created a stored procedure that encapsulated what I was doing.</p>
  
  <pre><code>foreach (string worker in workers)
  {
      _gzClasses.ExecuteCommand("EXEC dbo.Session_Aggregate @workerId = {0}, @timeThresh = {1}", worker, SecondThreshold);
      Console.WriteLine("Inserted sessions for {0}", worker);
  }
  </code></pre>
  
  <p>Then, I wanted to know how many rows each call was generating, so I changed the SP slightly to return <code>@@rowcount</code> as an output parameter. I <a href="https://stackoverflow.com/questions/11654610/how-to-use-datacontext-executecommand-and-get-the-executed-stored-proc-return-va">can't use the DataContext to execute commands with output parameters</a>, so I had to change the above code inside the for loop to the following:</p>
  
  <pre><code>using (var cn = new SqlConnection(CnStr))
  {
      cn.Open();
      using (var cmd = new SqlCommand("Session_Aggregate", 
          cn) {CommandTimeout = 300})
      {                        
          cmd.CommandType = CommandType.StoredProcedure;                        
  
          cmd.Parameters.AddWithValue("@workerId", worker);                        
          cmd.Parameters.AddWithValue("@timeThresh", SecondThreshold);                        
  
          SqlParameter sessions = cmd.Parameters.Add("@sessions", SqlDbType.Int);
          sessions.Direction = ParameterDirection.Output;
  
          cmd.ExecuteNonQuery();
  
          Console.WriteLine("Inserted {1} sessions for {0}", worker, sessions.Value);
      }
  }
  </code></pre>
  
  <p>This works, but it runs MUCH slower than the other query. I thought it might be a case of parameter sniffing, so I changed it to <code>CommandType.Text</code> and used the string <code>EXEC Session_Aggregate ... WITH RECOMPILE</code>. But in that case, I keep getting the error that the out parameter <code>@session</code> is not defined. In any case, the query barely runs now, even though the SQL command runs in &lt; 1 second in SSMS.</p>
  
  <p>Here's the stored procedure, in case anyone can help figure out what is going on, or can figure out a way to speed things up. I would also take pointers for how to properly profile what is going on here. With <code>CommandType.StoredProcedure</code> I can't even see the actual command that is sent to SQL by VS.</p>
  
  <pre><code>PROCEDURE [dbo].[Session_Aggregate] 
      -- Add the parameters for the stored procedure here
      @workerId varchar(64) = 0, 
      @timeThresh dateTime = '13 July 2007 11:27:46'
      @sessions INT OUTPUT
  AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
  
      -- Insert statements for procedure here
      INSERT INTO e_activeSessions
      SELECT *
      FROM (
          SELECT workerId, startTime, COUNT(*) as totalTasks, MAX(timeInSession) as totalTime, 
          MIN(dwellTime) as minDwell, MAX(dwellTime) as maxDwell, AVG(dwellTime) as avgDwell, STDEV(dwellTime) as stdevDwell, 
          SUM(CAST(wrong80 as INT)) + SUM(CAST(correct80 as INT)) as total80, SUM(CAST(correct80 as INT)) as correct80, 
          SUM(CAST(correct80 as FLOAT)) / NULLIF(SUM(CAST(wrong80 as INT)) + SUM(CAST(correct80 as INT)), 0 ) as percent80 
          FROM (
              SELECT *, (SELECT MAX(timeStamp)
                  FROM workerLog w where dwellTime is null AND timeInSession = 0 AND workerId = @workerId AND w.timeStamp &lt;= workerLog.timeStamp
                      AND w.timeStamp &gt;= @timeThresh) as startTime
              FROM workerLog where workerId = @workerId) t 
      GROUP BY startTime, workerId) f 
      WHERE startTime is NOT NULL AND f.totalTasks &gt; 1 AND totalTime &gt; 0;
  
      SET @sessions = @@ROWCOUNT;
  END
  </code></pre>
  
  <p><strong>EDIT: regardless of the execution plan for the original query, it was sped up significantly by creating a temporary table.</strong> I thought that SQL would have done this by analyzing the query, but I was probably wrong. <strong>Also, I found out about the <code>OPTIMIZE FOR UNKNOWN</code> hint which in new versions of SQL Server, mitigates the effect of parameter sniffing for when execution plans are for massively different sizes of data.</strong></p>
  
  <pre><code>PROCEDURE [dbo].[Session_Aggregate] 
      -- Add the parameters for the stored procedure here
      @workerId varchar(64) = 0, 
      @timeThresh dateTime = '13 July 2007 11:27:46',
      @sessions INT OUTPUT
  AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
  
      -- Insert statements for procedure here
  
      CREATE TABLE #startTimes
      (
          startTime DATETIME
      );
  
      CREATE INDEX Idx_startTime ON #startTimes(startTime);
  
      INSERT INTO #startTimes
      SELECT timeStamp FROM workerLog 
      WHERE dwellTime is null AND timeInSession = 0 
      AND workerId = @workerId AND timeStamp &gt;= @timeThresh;
  
      INSERT INTO e_activeSessions
      SELECT *
      FROM (
          SELECT workerId, startTime, COUNT(*) as totalTasks, MAX(timeInSession) as totalTime, 
          MIN(dwellTime) as minDwell, MAX(dwellTime) as maxDwell, AVG(dwellTime) as avgDwell, STDEV(dwellTime) as stdevDwell, 
          SUM(CAST(wrong80 as INT)) + SUM(CAST(correct80 as INT)) as total80, SUM(CAST(correct80 as INT)) as correct80, 
          SUM(CAST(correct80 as FLOAT)) / NULLIF(SUM(CAST(wrong80 as INT)) + SUM(CAST(correct80 as INT)), 0 ) as percent80 
          FROM (
              SELECT *, (SELECT MAX(startTime) FROM #startTimes where startTime &lt;= workerLog.timeStamp) as startTime
              FROM workerLog where workerId = @workerId) t 
      GROUP BY startTime, workerId) f 
      WHERE startTime is NOT NULL AND f.totalTasks &gt; 1 AND totalTime &gt; 0
      OPTION (OPTIMIZE FOR UNKNOWN);
  
      SET @sessions = @@ROWCOUNT;     
  END;
  </code></pre>
  
  <p><strong>Additional simplification</strong>: drag the SP to your DBML file and you can do the following:</p>
  
  <pre><code>foreach (string worker in workers)
  {
      int? rows = 0;
      _gzClasses.Session_Aggregate(worker, SecondThreshold, ref rows);
  
      Console.WriteLine("Inserted {1} sessions for {0}", worker, rows);
  }
  </code></pre>