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