stored procedures 2

[ksbrace]

Hello, I'm doing an internship programming in asp.net (vb.net) using sql server 2000 as the backend. I need to create some stored procedures. My question: Is it better to get the data from a view or should I join tables together. All the data that I need seems to be in the view. I'm assuming it is more efficient to use the view instead of joining tables, but I'm fairly new to stored procedures, so any help/advice would be greatly appreciated. Thanks in advance!
Kelly

 

[orethos]

You should use joins instead of views. Views bypass the query optimizer and therefore execute slower than joined queries (which can take advantage of statistics).

-Curt.

 

[ksbrace]

orethos,
Thanks for the advice. Now, what is the purpose of views, then?

 

[orethos]

Views are an easy way for non-SQL people to get data directly from the database (that is, without use of a presentation layer).
They're also helpful for DBAs and data analysts since they can save keystrokes.

 

[ksbrace]

Thanks for taking out time to clarify things for me, orethos.

 

[NoCoolHandle]

Curt,

I think you will find that views are always optimized (check your query plan) when you execute them, and that they have a very real reason for being in the tools we can use in SQL. Infact they solve some very real problems other than the obvious one that you mentioned of making it easy for non sql writers to get at data from mutli table joins.

Views are an excellent mechinisim for enhancing security, you can index views and remove a lage amount of execution time in summarizing data.. There are a thousand reasons to use views. There are also a few that show where it might be better to do somehting else, but OPTIMIZATION is not a reason to not use a view. That is just wrong.

Unlike Procs they don't save their query plan. They always recompile at execution time as we might choose to not refernce some columns and possibly add where clauses and order by's...

HTH

Rob

 

[NoCoolHandle]

Kelly reguarding your original question..

Be carefull in selecting a view to use in a stored proc. Some developers of databases tend to "Nest" views and this can definitly cause performance problems.

Run sp_depends on the view before you use it and see what sort of issues you are likely to have before you use it.

But, views can cut down on development time and do get optimized. You wont see any major benifits in performance (without indexed views, partitioned views and distributed views - worth looking up in BOL).

I have seen an indexed view take execution time from minuites to milliseconds.

My 1c


Rob