Comparing The Performance Of Indexed Views And Stored Procedures In SQL Server

Question

I've just recently become aware of the fact that you can now index your views in SQL Server (see http://technet.microsoft.com/en-us/library/cc917715.aspx). 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?

Take for example the following:

SELECT colA, colB, sum(colC), sum(colD), colE
FROM myTable
WHERE colFDate < '9/30/2011'
GROUP BY colA, colB, colE

The date will be different every time it's run, so if this were a view, I wouldn't include the WHERE 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.

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.

Thanks!

Answer 1

An indexed view can be regarded like a normal table - it's a materialized collection of rows.

So the question really boils down to whether or not a "normal" query is faster than a stored procedure.

If you look at what steps the SQL Server goes through to execute any query (stored procedure call or ad-hoc SQL statement), you'll find (roughly) these steps:

1. syntactically check the query
2. if it's okay - it checks the plan cache to see if it already has an execution plan for that query
3. if there is an execution plan - that plan is (re-)used and the query executed
4. if there is no plan yet, an execution plan is determined
5. that plan is stored into the plan cache for later reuse
6. the query is executed

The point is: ad-hoc SQL and stored procedures are treatly no differently.

If an ad-hoc SQL query is properly using parameters - as it should anyway, to prevent SQL injection attacks - its performance characteristics are no different and most definitely no worse than executing a stored procedure.

Stored procedure have other benefits (no need to grant users direct table access, for instance), but in terms of performance, using properly parametrized ad-hoc SQL queries is just as efficient as using stored procedures.

Using stored procedures over non-parametrized queries is better for two main reasons:

• since each non-parametrized query is a new, different query to SQL Server, it has to go through all the steps of determining the execution plan, for each query (thus wasting time - and also wasting plan cache space, since storing the execution plan into plan cache doesn't really help in the end, since that particular query will probably not be executed again)

• non-parametrized queries are at risk of SQL injection attack and should be avoided at all costs

Now of course, if you're indexed view can reduce down the number rows significantly (by using a GROUP BY clause) - then of course that indexed view will be significantly faster than when you're running a stored procedure against the whole dataset. But that's not because of the different approaches taken - it's just a matter of scale - querying against a few dozen or few hundred rows will be faster than querying against 200'000 or more rows - no matter which way you query.