What is more powefull between a stored procedure and a view?

Question

I have a project in which I have to interact with two databases. One, the main and a second one who contains identities and rights of users.. So I made a view in the main with informations I need from the second one, very practical!

A Colleague this morning says to me that is more speedy and powerful (For the return) to use stored procedure in which I aim the second database directly without the view step.

Is it really the case? views are really practical because of the retrieve only data's I need but if perf's are not the same I will change for stored procedures..

Thanks for all answers..

Answer 1

NO, this seems completely incorrect.

The view can easily be used to join in other selects, where as with the stored procedure you will first have to insert the results into a temp/variable table.

Stored Procedures are as they are called used for more procedural coding.

Answer 2

Don't just change stuff wily nily because someone says x is faster.

Always profile before optimizing.

• Figure out where the bottlenecks in your application are.
• Fix those that have the largest impact on total perfomance first.
• Rinse and repeat.

Answer 3

I don't think there is an answer to general question which is faster -- view or stored procedure other then It depends :).

If current solution is too slow (and I can imagine that checking user privileges is bottleneck) I would use both view and stored procedure.

Materialized view can help to aggregate data from various tables and well written stored procedure (with good query plan) helps to access view quickly.