title: Comparing The Performance Of Indexed Views And Stored Procedures In SQL Server
body: <p>I've just recently become aware of the fact that you can now index your views in SQL Server (see <a href="http://technet.microsoft.com/en-us/library/cc917715.aspx" rel="noreferrer">http://technet.microsoft.com/en-us/library/cc917715.aspx</a>). I'm now trying to figure out when I'd get better performance from a query against an indexed view versus the same query inside a stored procedure that's had it's execution path cached?</p>
  
  <p>Take for example the following:</p>
  
  <pre><code>SELECT colA, colB, sum(colC), sum(colD), colE
  FROM myTable
  WHERE colFDate &lt; '9/30/2011'
  GROUP BY colA, colB, colE
  </code></pre>
  
  <p>The date will be different every time it's run, so if this were a view, I wouldn't include the <code>WHERE</code> in the view and instead have that as part of my select against the view. If it were a stored procedure, the date would be a parameter.  Note, there are about 300,000 rows in the table.  200,000 of them would meet the where clause with the date.  10,000 would be returned after the group by.</p>
  
  <p>If this were an indexed view, should I expect to get better performance out of it than a stored procedure that's had an opportunity to cache the execution path? Or would the proc be faster? Or would the difference be negligible?  I know we could say "just try both out" but there are too many factors that could falsely bias the results leading me down a false conclusion, so I'd like to hear more of the theory behind it and what the expected outcomes are instead.</p>
  
  <p>Thanks!</p>