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!

Freeze

How many rows per date per colA, colB, colE group on average? – Wildeyed

"...you can now Index your Views in SQL Server" > You can create indexed views starting from SQL Server 2000. – Cilice

@MartinSmith Around 200,000 – Freeze

Seems like a good candidate for an indexed view then. If the time period covers 50 days you just need to seek 50 rows out of the view compared with aggregating 10 million from the base table. – Wildeyed

@BogdanSahlean Yep, I'm just becoming aware of it though. One of the reasons I'd go with procs over a view in the past (in cases where either or could be used) is because of the performance advantage of the proc storing the execution path. But now, with Indexed Views, I'm thinking the advantage may not be as prevalant. – Freeze

@MartinSmith I'm sorry...I think I may have misunderstood your comment. What I meant is that there would be 200,000 rows returned by most queries and 300,000 in the entire table. With this clarification, do you still feel the same? – Freeze

@Freeze - So on average each colA, colB, colE only has 1-2 rows per date? In that case the benefit of GROUP BY colA, colB, colE, Date is greatly diminished relative to the first case yes! – Wildeyed

Will an indexed view negatively impact performance on CUD operations on the base table(s)? – Exploratory

@SonicTheLichen - Yes. There is overhead maintaining them as for any index. – Wildeyed

@MartinSmith nope...I'm just an idiot while clarifying. Good thing I got this coffee in front of me. Because of the Group By and Sums, what would have been 200,000 rows returned, is instead about 10,000 rows. I'll clarify my question above with this info. – Freeze

@Nullqwerty: Indexed views and stored procedures are complementary tools. If you are using stored procedures (or ad-hoc queries) then you can use also indexed views or SQL Server can decide it self to use indexed views. SQL Server can store the execution plans also for ad-hoc queries (in some conditions: first it stored a stub and then the execution plan). – Cilice

@BogdanSahlean Thanks! It's great to hear. There have been occasions where I wanted to use a View but didn't just because of the articles I've read talking of the performance benefits of procs. Now I know I can seek out Indexed Views as well. – Freeze

All said and done I find it a bit strange nobody mentions the 'dark-side' of Indexed Views?! I know it's not really raised in the question, but assuming the source-table gets updated, said update could cause a full rebuild of the indexed view! Depending on the way the data is used this might be perfectly acceptable but it surely should be thought about upfront! (IMHO) – Backhanded

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.

Vere

Thanks for the response. In regards to the last paragraph, both the Proc and the View would have the group by. What's odd though is that I can find a bunch of articles that say that there is a performance advantage to procs because of the caching. You're saying that occurs even if you're not using a proc. Does this vary from db to db? – Freeze

@Nullqwerty: yes, the performance benefits of stored procedure are a hard-to-kill myth about SQL Server - are there really none, compared to a properly parametrized query. Such a query's execution plan is cached just the same, will stay in cache just as long as stored procedure execution plan - there really is no benefit to stored procedure in this area – Vere

See also: Pre-Compiled Stored Procedures: Fact or Myth, Why Stored Procedure is faster than Query?, “The SQL Guy” Post #14: The Myth Behind Stored Procedure Behaviour – Vere