http://www.sqlservercentral.com/blogs/livingforsqlserver/2011/02/13/evaluating-performance-of-view-and-stored-procedure/

Printed 2014/10/22 10:46PM

Evaluating Performance of View and Stored procedure

By Ramkumar (LivingForSQLServer), 2011/02/13

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

 

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


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.