查询视图是否比执行一次查询慢? [英] Is querying on views slower than doing one query?

问题描述

当我查询一个视图而不是一个查询时，Mssql 会更快吗?

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

例子

当我有这个观点时:

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"

推荐答案

虽然在您的简单示例中情况相同，但使用嵌套视图时仍需谨慎.

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

我在一个系统上工作，该系统在大约 6 级嵌套视图上构建了 30 秒后查询超时，并设法通过针对基表重写查询来将这些查询加速约 100 倍.

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.
*/

这篇关于查询视图是否比执行一次查询慢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！