StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POComparing The Performance Of Indexed Views And Stored Procedures In SQL Server
text
Body
copied!<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 < '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>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload