Why VIEW takes long time to Execute
Here in this article we are discussing related to the performance of the views. Why the performance of the view is slow. Please note that I am not taking about the indexed views, it's another thing and out of the scope of this article.
Like stored procedure, the optimizers cache the execution plan in the case for further use.
Let's take an example:
We have a simple base table contains records and we just create a view from this base table objects and then execute both (Base table and View separately)
CREATE TABLE emp_Table
(empid INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
empname VARCHAR(50) NOT NULL,
CREATE VIEW vw_empView
SELECT empid, empname, empsal
SELECT * FROM emp_Table
SELECT * FROM vw_empView
Here in this example which one is executed faster?
It's Example-1. That means direct query from Table objects. To get the better understanding related to execution we just look at the execution plan.
Both execution plans are same. So, why the execution of views take long time?
This is because it takes SQL Server extra work such as looking up data in the system tables before it can execute the view.
This extra work is not part of the execution plan, so it appears that the two SELECT statements should run at the same speed, which they don't, because some of the work SQL Server is doing is hidden.