If I have a table that contains tens of thousands of records of accounts for multiple locations.
Is there any speed difference if I query a view that selects all accounts for a specific location and a stored procedure that with the same SQL statement?
EDIT: I ran across this Materialized View. It seems like this would always be used.
When would you want to use a store procedure, view, and materialized view? What pros and cons do you want to keep in mind when making this decision?
Yes and no.
A view is a query definition that is basically replaced in-place when used, and it gets compiled into the query that references the view. That means that the actual execution depends on the query that references the view. If the query is a straight forward SELECT * FROM view then this will be pretty much exactly the same execution plan as the equivalent procedure. However if the query is SELECT onefield FROM view the the query is significantly different. There is no equivalent procedure, and this query may perform significantly better because of the reduced projection list.
There are also huge usability diferences. A stored procedure can only be executed. A view can be selected from and used with multiple other statements like joins, subqueries and the like.
Given the much better flexibility of views, unless no other factor plays a role then a procedure makes sense only if you have parameters, since views cannot have parameters.
A view and a stored procedure both are compiled into the database so they are faster than a direct query, the difference in speed between them appears when you need to have dynamic parameters. The views just don't accept them.
Each one has his own specific use. Views can be used in other queries or views, Stored procedures can only be executed. But at your question with the same SELECT * FROM they have exactly the same speed.
Short answer: "It depends"
Longer answer: "It depends on the shape of the query"
As with any question about performance in SQL Server (what's better: x vs y), there's no correct answer. In the case of views vs. sprocs, there's no way to reliably predict which will (if any) be faster, short of profiling the query.
I've seen both be faster, and it's come down to how the view's been used and whether it's part of a bigger query. I've also seen views slow queries down because they can hide a lot of complexity that the query using the view doesn't actually need.
You need to assess what you're trying to achieve: if all you're doing is wanting access to the table rows, and you aren't going to want to use the output as part of another query, I'd choose a stored procedure, especially if the query against the table is going to take some WHERE clause.
Where is the query going to be called from? Another part of SQL? Some application framework? A custom data-access layer? It's worth thinking how the calling code is going to put the query together, as this can affect how SQL Server ends up caching and reusing the execution plan. If it just bolts together a bunch of dynamic SQL, then performance might suffer slightly as SQL Server may need to rebuild the query plan each time; so in this instance a sproc has the advantage with a cached plan. If the access layer is intelligent and does parameterised dynamic SQL, there may not be so much in it.
Conclusion: understand what it is you want to achieve. Then profile, tune, tweak, and repeat until satisfied.
The anserws to this Post will provide useful background on indexed (materialized) views in SQL Server.