Is querying on views slower than doing one query?

Question

Will Mssql be even fast when I query on a view opposed to one query?

example

When I have this view:

create view ViewInvoicesWithCustomersName
Select * from Invoices left join Customer on Customer.ID=Invoices.CustomerID

What will be faster or will it be even fast?

a) select * from ViewInvoicesWithCustomersName where customerName="Bart"
b) select * from Invoices left join Customer on Customer.ID=Invoices.CustomerID
   where customername="Bart"

Answer 1

Both queries will be equivalent.

Answer 2

They are the same, but view the execution plan so you can see what is going on. If you are having performance issues, it's likely that you need an index. Presuming Customer.ID is the primary key with a clustered index then Invoice.CustomerID and CustomerName are good candidates for an index.

Answer 3

A view is simply a macro that is expanded/unnested into the main query. So these are equivalent.

Note: if customername is in the Customer table, you've actually created an INNER JOIN. To filter for Bart's invoices and invoices with no customer you'd need to do this:

select
  *
from
  Invoices
  left join
  Customer on Customer.ID=Invoices.CustomerID and customername="Bart"

Answer 4

Both should take almost same time.

View here is just representation for query to be executed for data whenever View is accessed.

There is another type of View i.e. Materialized View. This type of view has physical existance as Tables. And query (passed during view creation) is not executed while accessing this type of view. acessing from this type of view should be faster.

Answer 5

Whilst in your simple example things will be the same some caution is necessary with using nested views.

I worked on a system where queries were timing out after 30 seconds built on about 6 levels of nested views and managed to speed these up by a factor of about 100 by rewriting the queries against the base tables.

A simple example of the type of issue that can arise is below.

CREATE VIEW MaxTypes
AS
SELECT
  [number],
  MAX(type) AS MaxType
  FROM [master].[dbo].[spt_values]
GROUP BY [number]

GO

CREATE VIEW MinTypes
AS
SELECT
  [number],
  MIN(type) AS MinType
  FROM [master].[dbo].[spt_values]
GROUP BY [number]

GO
SET STATISTICS IO ON

SELECT     MaxTypes.number, MinTypes.MinType, MaxTypes.MaxType
FROM         MinTypes INNER JOIN
                      MaxTypes ON MinTypes.number = MaxTypes.number
ORDER BY MaxTypes.number

/*
Gives

Table 'spt_values'. Scan count 2, logical reads 16, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/
GO

SELECT 
  [number],
  MAX(type) AS MaxType,
  MIN(type) AS MinType
  FROM [master].[dbo].[spt_values]
GROUP BY [number]
ORDER BY  [number]

/*
Gives

Table 'spt_values'. Scan count 1, logical reads 8, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/