I got a chance to evaluate performance of both views and stored procedure for high level decision making.
As all of Us knows that purpose/scope of views and stored procs are different, I have done this quick POC only to prove execution time of both views and stored procedures are same.
seem still lots of people have misconception that
1. only stored procedures plans can be cached
2. views can't make use index of underlying table. both are not true.
Here is quick POC on views vs stored procedure to understand performance/accessibility constraints.
View took 2152 milliseconds and consumed 1830 logical reads
SP took 2187 milliseconds and consumed 1830 logical reads
Case 1 finding : No performance difference while fetching 160,000 thru views and stored procedures
-- Case 2: to analyze execution time and logical reads to fetch 1 records from view and stored procedure
Both View and Sp took 1 millisecond and consumed 3 logical reads to fetch 1 record
Case 2 finding : Both Views and stored procedures are equipped to use index of underlying table
Case 3: difference between Views and Stored procedures on Data accessibility
Views | Stored Procedures |
No option to return customized resultset | Can be customized to handle many requirements in single SP |
Views cannot be parameterized. | Can be parameterized. can return multiple result sets. Can return different result based on parameters |
no control on the way end user manipulate Views | have control over final data |
Bad usage* of view may kill production server performance(*non sargable conditions in view) | more control over the stored procedure usage |
Execution plan is cached for reuse | Execution plan is cached for reuse |
Internal data processing not possible | Data can be processed using programming constructs |
Views can be used in SELECT commands and can be joined with other views or tables | stored procedures usually wont be part of SELECT statement. but some people have tried this thru OPENQUERY. if not, stored procedure result set can be stored in temp table for later use. |
-- Case 4: View and stored procedure performance in cross database query to fetch 1.23 million records
To perform this below query (join tables from 3 databases) is used in views and stored procedure.
select a.Associate_FirstName, n.Country_ID, allo.START_DT, allo.END_DT
from DB1..Ps_Associate_Details a
inner join DB2..Ps_AssociateNationality n on a.associate_id = n.associate_id
inner join DB3..PSs_INT allo on a.associate_id = allo.EMPLID
case 4 findings: both view and stored procedure took 15 seconds and consumed 169093 logical IO to return 1.23 million records from 3 databases.
final word:
1. Both Views and Stored Procedure perform well and intelligent enough to make use of available indexes
2. There is a possibility of miss handling views as we don’t have control on it. Stored procedure is more flexible in many ways like parameterization, programming constructs to process the data etc.
3. Views are used as a guard to provide only specific columns/rows to users. Stored procedures are used for business data processing needs.
I know that I have touch only few points. Waiting to see our viewer comments on this
