Why VIEW takes long time to Execute

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,

        empsal   DECIMAL(20,2))

       

GO

CREATE VIEW vw_empView

AS

SELECT empid, empname, empsal

FROM   emp_Table

GO

-- Example-1

SELECT * FROM emp_Table

GO

-- Example-2

SELECT * FROM vw_empView

GO

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.