Tip

Sybase: Views vs. stored procedures

Don't be fooled, one Sybase expert warns. Views and stored procedures both return result sets, but that's about the only thing they have in common. SearchOpenSource.com's expert, Dr. Mich Talebzadeh, explains the differences between views and stored procedures and how they recompile in different ways. He also offers an example of a view.

Alike but not equivalent

On the face of it, one might think that a view and a stored procedure were the same thing. After all, a view returns a result set, and a stored procedure can also return a result set. 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 exists only 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 in which 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. But 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 very handy for insulating the complexity of the database. A view can be constructed to hide the the technical details of the normalizations that may have been applied to the underlying data tables. Thus, the consumers can be presented

    Requires Free Membership to View

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 that is easier to understand.

Processing queries

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, you get the same performance results whether you explicitly code the view or merely referring to it. 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.

Queries 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 compiling 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, because the optimizer that 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.

Faster execution of queries

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.

Assume the query " select * from emp " is written in a view. 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 August 2006

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.