title: Creating local variables from parameters increases sproc performance
body: <p>I have a relatively simple stored procedure that is used to group data inside a relatively nasty view I have no control over based on the supplied parameters. A simplified version of the stored procedure is:</p>
  
  <pre><code>CREATE PROCEDURE dbo.GroupList 
      @list VARCHAR(MAX), 
      @start DATETIME, 
      @end DATETIME,
      @resolution INT
  AS
  BEGIN  
      SET NOCOUNT ON
  
      declare @elements ListTVP;
      insert into @elements select Value as elementID from udf_Split(@localList, ',');
  
      IF @resolution = 1  -- group by month
      BEGIN
          SELECT *
          FROM (
              SELECT
                  Timestamp = CAST(CONVERT(VARCHAR, DATEPART(YEAR, Timestamp)) + '-' + CONVERT(VARCHAR, DATEPART(MONTH, Timestamp)) + '-01' AS DATETIME)
                  , Total = SUM(Cost)
              FROM eLP e
                  INNER JOIN @elements m ON e.elementID = m.elementID
              WHERE Timestamp &gt;= @start AND Timestamp &lt;= @end
              GROUP BY 
                  DATEPART(YEAR, Timestamp),
                  DATEPART(MONTH, Timestamp)
              ) AS t1
      END
      ELSE IF @resolution = 2 -- group by year
      BEGIN
          SELECT *
          FROM (
              SELECT
                  Timestamp = CAST(CONVERT(VARCHAR, DATEPART(YEAR, Timestamp)) + '-01-01' AS DATETIME)
                  , Total = SUM(Cost)
              FROM eLP e
                  INNER JOIN @elements m ON e.elementID = m.elementID
              WHERE Timestamp &gt;= @start AND Timestamp &lt;= @end
              GROUP BY 
                  DATEPART(YEAR, Timestamp)
              ) AS t1
      END
  END
  </code></pre>
  
  <p>If I run the SQL code in the stored procedure inside SSMS, the code is relatively fast. If I run the stored procedure, the code is between 10 to 100 times slower for the exact same data. Why is this happening?</p>
  
  <p>Also, I've noticed that if I create local variables from the supplied parameters, performance improves dramatically:</p>
  
  <pre><code>CREATE PROCEDURE dbo.GroupListWithVariables
      @list VARCHAR(MAX), 
      @start DATETIME, 
      @end DATETIME,
      @resolution INT
  AS
  BEGIN
      DECLARE @localList VARCHAR(MAX) = @list
      DECLARE @localStart DATETIME = @start, @localEnd DATETIME = @end
      DECLARE @localResolution INT = @resolution
  </code></pre>
  
  <p>Comparing the execution time of the 2 versions of the stored procedure, I have:</p>
  
  <ul>
  <li>GroupList:
  <ul>
  <li>568 ms average execution time</li>
  <li>9 seconds reported by SSMS to actually run the sproc and retrieve the results</li>
  <li>355 kB of data received from server</li>
  </ul></li>
  <li>GroupListWithVariables:
  <ul>
  <li>1040 ms average execution time</li>
  <li>1 second reported by SSMS to actually run the sproc and retrieve the results</li>
  <li>239 kB of data received from server</li>
  </ul></li>
  </ul>
  
  <p>Why is there such a large difference between the 2 versions? I've noticed the same behavior in both SQL Server 2008 R2, as well as in SQL Server 2012.</p>