title: SQL Server proc running 5x slower than plain query
body: <p>I have the following query:</p>
  
  <pre><code>DECLARE @DaysNotUsed int = 14
  DECLARE @DaysNotPhoned int = 7
  
  --Total Unique Students
  DECLARE @totalStudents TABLE (SchoolID uniqueidentifier, TotalUniqueStudents int)
  INSERT INTO @totalStudents
  SELECT 
          SSGG.School,
          COUNT(DISTINCT S.StudentID)
      FROM Student S
          INNER JOIN StudentStudents_GroupGroups SSGG ON (SSGG.Students = S.StudentID AND SSGG.School = S.School)
          INNER JOIN [Group] G ON (G.GroupID = SSGG.Groups AND G.School = SSGG.School)
          INNER JOIN SessionHistory SH ON (SH.Student = S.StudentID AND SH.School = S.School AND SH.StartDateTime &gt; GETDATE() - @DaysNotUsed)
      WHERE G.IsBuiltIn = 0
          AND S.HasStartedProduct = 1
      GROUP BY SSGG.School
  
  --Last Used On
  DECLARE @lastUsed TABLE (SchoolID uniqueidentifier, LastUsedOn datetime)
  INSERT INTO @lastUsed
  SELECT
          vi.SchoolID,
          MAX(sh.StartDateTime)
      FROM View_Installation as vi
          INNER JOIN SessionHistory as sh on sh.School = vi.SchoolID
      GROUP BY vi.SchoolID
  
  SELECT 
          VI.SchoolID, 
          INS.DateAdded,
          INS.Removed,
          INS.DateRemoved,
          INS.DateToInclude,
          VI.SchoolName AS [School Name], 
          VI.UsersLicensed AS [Licenses],
          ISNULL(TS.TotalUniqueStudents, 0) as [Total Unique Students],
          ISNULL(TS.TotalUniqueStudents, 0) * 100 / VI.UsersLicensed as [% of Students Using],
          S.State,
          LU.LastUsedOn,
          DATEDIFF(DAY, LU.LastUsedOn, GETDATE()) AS [Days Not Used],
          SI.AreaSalesManager AS [Sales Rep],
          SI.CaseNumber AS [Case #],
          SI.RenewalDate AS [Renewal Date],
          SI.AssignedTo AS [Assigned To],
          SI.Notes AS [Notes]
      FROM View_Installation VI
          INNER JOIN School S ON S.SchoolID = VI.SchoolID
          LEFT OUTER JOIN @totalStudents TS on TS.SchoolID = VI.SchoolID
          INNER JOIN @lastUsed LU on LU.SchoolID = VI.SchoolID
          LEFT OUTER JOIN InactiveReports..SchoolInfo SI ON S.SchoolID = SI.SchoolID
          LEFT OUTER JOIN InactiveReports..InactiveSchools INS ON S.SchoolID = INS.SchoolID
      WHERE VI.UsersLicensed &gt; 0
          AND VI.LastPhoneHome &gt; GETDATE() - @DaysNotPhoned
          AND
          (
              (
                  SELECT COUNT(DISTINCT S.StudentID)
                      FROM Student S
                          INNER JOIN StudentStudents_GroupGroups SSGG ON (SSGG.Students = S.StudentID AND SSGG.School = S.School)
                          INNER JOIN [Group] G ON (G.GroupID = SSGG.Groups AND G.School = SSGG.School)
                      WHERE G.IsBuiltIn = 0
                          AND S.School = VI.SchoolID
              ) * 100 / VI.UsersLicensed &lt; 50
              OR
              VI.SchoolID NOT IN 
              (
                  SELECT DISTINCT SH1.School
                  FROM SessionHistory SH1
                  WHERE SH1.StartDateTime &gt; GETDATE() - @DaysNotUsed
              ) 
          )
      ORDER BY [Days Not Used] DESC
  </code></pre>
  
  <p>Running just plain sql like this in SSMS take about 10 seconds to run.  When I created a stored procedure with exactly the same code, the query takes 50 seconds instead.  The only difference in the actual code of the proc is a SET NOCOUNT ON that the IDE put in by default, but adding that line to the query doesn't have any impact.  Any idea what would cause such a dramatic slow down like this?</p>
  
  <p><strong>EDIT</strong> I neglected the declare statements at the beginning.  These are not in the proc, but are parameters to it.  Could this be the difference?</p>