SQL-Server Performance: What is faster, a stored procedure or a view?

I know I'm not supposed to turn this into a "discussion", but I'm very interested in this and just thought I'd share my empirical observations of a specific situation, with particular reference to all the comments above which state that an equivalent SELECT statement executed from within a Stored Procedure and a View should have broadly the same performance.

I have a view in database "A" which joins 5 tables in a separate database (db "B"). If I attach to db "A" in SSMS and SELECT * from the view, it takes >3 minutes to return 250000 rows. If I take the select statement from the design page of the view and execute it directly in SSMS, it takes < 25 seconds. Putting the same select statement into a stored procedure gives the same performance when I execute that procedure.

Without making any observations on the absolute performance (db "B" is an AX database which we are not allowed to touch!), I am still absolutely convinced that in this case using an SP is an order of magnitude faster than using a View to retrieve the same data, and this applies to many other similar views in this particular case.

I don't think it's anything to do with creating a connection to the other db, unless by using a view it somehow can never cache the connection whereas the select does, because I can switch between the 2 selects in the same SSMS window repeatedly and the performance of each query remains consistent. Also, if I connect directly to db "B" and run the select without the dbname.dbo.... refs, it takes the same time.

Any thoughts anyone?

Views:

• We can create index on views (not possible in stored proc)
• it's easy to give abstract views(only limited column access of multiple table ) of table data to other DBA/users

Store Procedure:

• We can pass parameters to sp(not possible in views)
• Execute multiple statement inside procedure (like insert, update,delete operations)

Found a detailed performance analysis: https://www.scarydba.com/2016/11/01/stored-procedures-not-faster-views/

Compile Time Comparison:

There is a difference in the compile time between the view by itself and the stored procedures (they were almost identical). Let’s look at performance over a few thousand executions:

View AVG: 210.431431431431

Stored Proc w/ View AVG: 190.641641641642

Stored Proc AVG: 200.171171171171

This is measured in microsends, so the variation we’re seeing is likely just some disparity on I/O, CPU or something else since the differences are trivial at 10mc or 5%.

What about execution time including compile time, since there is a difference:

Query duration View AVG: 10089.3226452906

Stored Proc AVG: 9314.38877755511

Stored Proc w/ View AVG: 9938.05410821643

Conclusion:

With the exception of the differences in compile time, we see that views actually perform exactly the same as stored procedures, if the query in question is the same.