0

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..

bAN
  • 13,375
  • 16
  • 60
  • 93
  • 1
    possible duplicate of [SQL-Server Performance: What is faster, a stored procedure or a view?](http://stackoverflow.com/questions/1603853/sql-server-performance-what-is-faster-a-stored-procedure-or-a-view) – HTTP 410 Dec 22 '10 at 08:43
  • Sorry it seems it is an exact duplicate, sorry again.. – bAN Dec 22 '10 at 08:59

3 Answers3

3

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.

Adriaan Stander
  • 162,879
  • 31
  • 289
  • 284
3

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.
Byron Whitlock
  • 52,691
  • 28
  • 123
  • 168
0

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.

Grzegorz Gierlik
  • 11,112
  • 4
  • 47
  • 55