View SQL Server 2005 vs. Materialized View and Stored Procedure

Question

If I have a table containing tens of thousands of account entries for multiple locations.

Is there any difference in speed if I request a view that selects all accounts for a specific location and stored procedure with the same SQL operation?

EDIT: I looked at this materialized view. It seems that it will always be used.

When do you want to use the storage procedure, view, and materialized view? What pros and cons do you want to keep in mind when making this decision?

Answer 1

Short answer: "It depends"

Longer answer: "It depends on the request form"

Like any SQL Server performance question (which is better: x vs y), there is no right answer. In the case of views against sprocs, there is no way to reliably predict which one (if any) is faster than query profiling.

I saw how they are faster, and it comes down to how the view is used and whether it is part of a larger query. I also saw the slow queries look down because they can hide the greater complexity that a query using a view is not really needed.

You need to evaluate what you are trying to achieve: if all you do is access the rows of the table and you don’t want to use the output as part of another query, I would choose a stored procedure, especially if the query to the table takes some WHERE clause.

Where will the request be called from? Another piece of SQL? Some application framework? User level data access? It is worth considering how the call code is going to combine the query, as this may affect how SQL Server finishes caching and reusing the execution plan. If it simply connects a bunch of dynamic SQL, performance may be slightly affected, as SQL Server may need to rebuild the query plan each time; therefore, in this case sproc has the advantage of a cached plan. If the access level is intelligent and parameterizes dynamic SQL, there may not be many in it.

Conclusion: understand what you want to achieve. Then profile, tuning, tuning, and repeating until satisfied.

Answer 2

Yes and no.

A view is a query definition that is basically replaced in-place when used, and it compiles into a query that references the view. This means that the actual execution depends on the request that refers to the view. If the query is a direct SELECT * FROM view , then it will be almost exactly the same execution plan as the equivalent procedure. However, if the query is a SELECT onefield FROM view , the query is significantly different. There is no equivalent procedure, and this request can improve significantly due to the reduced list of forecasts.

There are also huge differences in usability. A stored procedure can only be performed. A view can be selected and used with several other operators, such as joins, subqueries, etc.

Given the much better flexibility of representations, if another factor does not matter, then the procedure makes sense only if you have parameters, because representations cannot have parameters.

Answer 3

The view and the stored procedure are compiled into a database, so they are faster than a direct query, the difference in speed between them appears when you need to have dynamic parameters. Views simply do not accept them.

Each has its own use. Views can be used in other queries or views. Stored procedures can only be executed. But to your question with the same SELECT * FROM they have exactly the same speed.

Answer 4

Interacting with this Post will provide useful information about indexed (materialized) views in SQL Server.

Answer 5

I also saw that each one is faster than the other, depending on the context.

The following general rule: if a view has a complex WHERE, depends on other moderately complex representations, or is the result of UNION [ALL], then SQL S will almost certainly not be able to correctly distribute WHERE conditions applicable to the view, down to individual tables, therefore, in some then you will start to receive a table scan (unless it is a materialized view), or your execution plans will be much more complicated (and slower) than what they may be.

In these cases, it is better to switch to proc. And, as others have said, always a profile!