SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Evaluating Performance of View and Stored procedure

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.


 -- Case 1: to analyze execution time and logical reads to fetch 160,000 records from view and stored procedure


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



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


I am Ramkumar, 34 years old Consultant, trainer, blogger and speaker at SQL Server user group in India. I have more than 10 years of experience as a developer and SQL Server DBA. I love reading, teaching and blogging about SQL Server internals and performance tuning. My Facebook page: https://www.facebook.com/LivingForSqlServer


No comments.

Leave a Comment

Please register or log in to leave a comment.