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

Overall, stored procedure will compile once and use the same compiled plan and increment the Usecounts value of the executable plan. A stored procedure is pre-loaded in memory for faster execution.  It will really augment your system on performance base. This really shows us the importance of the stored procedure as compared with the SELECT statement and views.

Now, let’s execute the following commands to clear the cache before the upcoming view experiment.

DBCC FREEPROCCACHE

GO

Views

We will create a view and how it is differs from theSELECT statement and stored procedures.

CREATE VIEW vwDummyTable1 AS

SELECT EmpID, EmpName FROM DummyTable1

GO

Now, let’s execute the following commands to display the data and cache information for the vwDummyTable1 we created.

SELECT EmpId, EmpName from vwDummyTable1

GO

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

GO

Cacheobjtype Refcounts Usecounts Sql
Executable Plan

1

1

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

2

2

()SELECT EmpID, EmpName FROM [vwDummyTable1]

SQL Server has displayed the compiled and executable plan for the spDummyTable1 stored procedure.

Let’s now execute the same statement again and view the cache details.

SELECT EmpId, EmpName from vwDummyTable1

GO

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

GO

Cacheobjtype Refcounts Usecounts Sql
Executable Plan

1

2

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

2

2

()SELECT EmpID, EmpName FROM [vwDummyTable1]

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. N number user will use the same compiled plan when we execute the same SELECT statement on view.

Now, let’s add a WHERE clauses on the view SELECT statement and view the results from the master.dbo.Syscacheobjects.

SELECT EmpId, EmpName from vwDummyTable1 WHERE EmpID = 4

GO

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

GO

SQL Server has not used the existing cache plan because of change in the view’s SELECT statement. SQL Server generates a new cache plan for the SELECT statement on view along with old cache plan.

Cacheobjtype Refcounts Usecounts Sql
Executable Plan

1

1

(@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]

Let’s execute the same view SELECT statement with different empid and view the results.

SELECT EmpId, EmpName FROM vwDummyTable1 WHERE EmpID = 8

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 [DummyTable1] WHERE [EmpId]=@1
Compiled Plan

2

2

(@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1] WHERE [EmpId]=@1
Executable Plan

1

2

()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1]
Compiled Plan

2

2

()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1]

Though we have given different empid value, SQL Server has used the same compiled plan for the SELECT statement on the view and incremented the Usecounts of the executable plan.

Continues…

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 |