To continue reading for free, register below or login
To read more you must become a member of SearchEnterpriseLinux.com
On the face of it, one might think that a view and a stored procedure were equivalent to one another. After all, a view returns a result set, and a stored procedure can also return one. But that's where the similarity ends.
A view is really nothing more than a virtual table. That is, it looks like a table and behaves like one, but it only exists in a compiled form. In effect, the table is insubstantiated when the view is referenced. You can refer to this virtual table (the view) in any query where you could refer to a 'real' table. There are some restrictions on this, especially with regards to updating via the view.
A view cannot take parameters. However, it can be used as a security mechanism without giving permission to the underlying tables. More importantly, you can use the view to BCP out a subset of data from the underlying table or tables.
A view is a very handy mechanism for insulating the complexity of the database. A view can be constructed which hides the details of normalization, for example, allowing the data consumer to be unaware of the technical details of the normalizations that may have been applied to the underlying data tables. Thus, the consumers can be presented with a view of the data which hides the complexities of the JOIN operations required of the underlying data tables, instead presenting a "flatter" view of the data which is easier to understand.
When a query containing a view is processed, the view definition is incorporated into the query plan and this is how the virtual table is materialized. Therefore, explicitly coding the view or referring to it is equivalent, performance-wise. If you have a complex set of JOINs and you use that set often, it may be better to code them as a view, ensuring that all uses of the data perform the JOINs in the same way.
It is queries that are compiled into an execution plan, and then that plan is cached. If another query comes along that can use the same (cached) query plan, and nothing significant has changed since the plan was compiled, the plan will be re-used.
This simply avoids the cost of compilation of the query plan. "Compiling" a plan doesn't make the query run faster - every query has to be "compiled" before it can run. It's just that circumstances may allow the compilation effort to be avoided on subsequent executions. Compilation can be a major process, as the optimizer which is responsible for creating the plan will try very hard to come up with the most efficient plan possible. Really complicated queries may convince the optimizer that it is worth it to spend more time trying to find the best plan, so compilation time goes up.
It is true that using more stored procedures will likely result in a more efficient use of the cache and that may explain why, in general, a stored procedure execution seems to be faster than using a view returning the identical result set as shown below
First create a view:
- create view v_test1 as select * from test1
- go
- declare @start datetime
- declare @end datetime
- select @start = getdate()
- select * from v_test1
- select @end = getdate()
- select datediff(ms,@start,@end) as 'Time taken in milliseconds'
(500 rows affected)
(1 row affected)
Time taken in milliseconds
--------------------------
410
(1 row affected)
Now:
- create proc p_test1 as select * from test1
- go
- declare @start datetime
- declare @end datetime
- select @start = getdate()
- exec p_test1
- select @end = getdate()
- select datediff(ms,@start,@end)
(500 rows affected)
(return status = 0)
Time taken in milliseconds
--------------------------
346
|
