Q

Views vs. Stored procedures

Sybase expert Mich Talebzadeh describes the similarities between views and stored procedures that can lead to differences in the speed of compilation.

Assume the query " select * from emp " is written in a stored procedure or a view. If both are pre-compiled objects, which of the two is faster?

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:

  1. create view v_test1 as select * from test1
  2. go
  3. declare @start datetime
  4. declare @end datetime
  5. select @start = getdate()
  6. select * from v_test1
  7. select @end = getdate()
  8. 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:

  1. create proc p_test1 as select * from test1
  2. go
  3. declare @start datetime
  4. declare @end datetime
  5. select @start = getdate()
  6. exec p_test1
  7. select @end = getdate()
  8. select datediff(ms,@start,@end)
(500 rows affected) (return status = 0) Time taken in milliseconds -------------------------- 346
This was first published in December 2005

Dig deeper on Open source databases

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataCenter

SearchServerVirtualization

SearchCloudComputing

SearchEnterpriseDesktop

Close