Pros & Cons of Using SELECT, Views, and Stored Procedures in SQL Server

The value of Usecounts has been incremented. SQL Server has used the same compiled plan for the SELECT statement on the view and incremented the Usecounts of the executable plan. Different users will execute the SELECT statement on view with different empid value will use the same compiled plan and only increase the Usecounts value of the executable plan

Now, let’s execute the same statement on the view with the username on the SELECT statement and verify the results.

SELECT EmpId, EmpName from dbo.vwDummyTable1 WHERE EmpID = 8

GO

SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects

GO

Though we have given different empid value, SQL Server has used same cache compiled plan and increased the Usecounts value on the Execution plan.

Now, let us execute the same statement with the username on the SELECT statement and verify the results.

SELECT EmpId, EmpName from dbo.vwDummyTable1 WHERE EmpID = 8

GO

SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects

GO

Cacheobjtype Refcounts Usecounts Sql
Executable Plan

1

1

(@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [dbo].[vwDummyTable1] WHERE [EmpID]=@1
Compiled Plan

2

2

(@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [dbo].[vwDummyTable1] WHERE [EmpID]=@1
Executable Plan

1

2

(@1 tinyint)SELECT EmpID, EmpName FROM [vwDummyTable1] WHERE [EmpID]=@1
Compiled Plan

2

2

(@1 tinyint)SELECT EmpID, EmpName FROM [vwDummyTable1] WHERE [EmpID]=@1
Executable Plan

1

2

()SELECT EmpID, EmpName FROM [vwDummyTable1]
Compiled Plan

2

2

()SELECT EmpID, EmpName FROM [vwDummyTable1]

Now, we have two more rows in the cache plan because we have used different usernames in the SELECT statement. SQL Server generates a new compiled and Execution plan for different user. The same user will execute the SELECT statement more than one time will use the same compiled plan and only increase the Usecounts value of the executable plan

Let’s execute the same SELECT statement on view with different empid and verify the result.

SELECT EmpId, EmpName from dbo.vwDummyTable1 WHERE EmpID = 6

GO

SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects

GO

Cacheobjtype Refcounts Usecounts Sql
Executable Plan

1

2

(@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [dbo].[vwDummyTable1] WHERE [EmpID]=@1
Compiled Plan

2

2

(@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [dbo].[vwDummyTable1] WHERE [EmpID]=@1
Executable Plan

1

2

(@1 tinyint)SELECT EmpID, EmpName FROM [vwDummyTable1] WHERE [EmpID]=@1
Compiled Plan

2

2

(@1 tinyint)SELECT EmpID, EmpName FROM [vwDummyTable1] WHERE [EmpID]=@1
Executable Plan

1

2

()SELECT EmpID, EmpName FROM [vwDummyTable1]
Compiled Plan

2

2

()SELECT EmpID, EmpName FROM [vwDummyTable1]

Now, let’s execute the same statement with the databasename and username on the view SELECT statement and verify the results.

SELECT EmpId, EmpName from vijay.dbo.vwDummyTable1 WHERE EmpID = 10

GO

SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects

GO

Cacheobjtype Refcounts Usecounts Sql
Executable Plan

1

1

(@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [dbo].[vwDummyTable1] WHERE [EmpID]=@1
Compiled Plan

2

2

(@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [dbo].[vwDummyTable1] WHERE [EmpID]=@1
Executable Plan

1

2

(@1 tinyint)SELECT EmpID, EmpName FROM [vwDummyTable1] WHERE [EmpID]=@1
Compiled Plan

2

2

(@1 tinyint)SELECT EmpID, EmpName FROM [vwDummyTable1] WHERE [EmpID]=@1
Executable Plan

1

1

(@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [vijay].[dbo].[vwDummyTable1] WHERE [EmpID]=@1
Compiled Plan

2

2

(@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [vijay].[dbo].[vwDummyTable1] WHERE [EmpID]=@1
Executable Plan

1

2

()SELECT EmpID, EmpName FROM [vwDummyTable1]
Compiled Plan

2

2

()SELECT EmpID, EmpName FROM [vwDummyTable1]

We have seen that we can minimize the creation of compilation plans if we add the database name, username, and WHERE condition for a SELECT statement or on a view. If you do change these, then your code has to be recompiled, hurting your system’s performance. So what is the difference between a SELECT statement and a view in regards to performance?. Is there any difference at all? No, the compiled and executable plans are same for both SELECT statement and view. But one difference is that a view is stored (physically) in a database, while SELECT statements are not. The advantage of a view is that it, in some cases, allows the easier administration of object permissions. For example, you create a view and provide a SELECT permission for certain set of users, but not others, that only allows them to see certain columns in a table, but not all. In most cases, if you don’t have this need from a security perspective, then the use of views in unnecessary.

Biography

G. Vijayakumar has worked in client server and web application.  He is currently working for Transworld, which is located in Bangalore, India, working on an e-banking prod

Pages: 1 2 3 4 5 6




Related Articles :

  • No Related Articles Found

3 Responses to “Pros & Cons of Using SELECT, Views, and Stored Procedures in SQL Server”

  1. hi,

    I ran the select statement and i got the desired result but when i run the select on syscacheobjects i am seeing only parse tree and compiled plan but i am not able to see the executable plan.

    please reply with valuable suggestions

  2. I am using sql server 2008? i think we can see cache object executable plan in sql 2000 but not in 2005/20008
    am i on the right track?

  3. Hi Vijay,

    Thanks for Good article on Select,View & SP.

    I Need Some Clarification
    I’m using Sql Server 2008

    A). When i Execute Same SQL statement with one same user but diffrent empid,
    then it produce diffrent Compiled Plan.

    B). the above is same for VIEW also.

    C). But for SP uses same Compiled Plan for diffrent empid input.

    I think this is correct behaviour in Sql server 2008.

    What you say regarding SELECT & VIEW is OPPOSITE

    Please Explain

    Regards,
    Anil Vanjre

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |