关于视图的问题

发布于 2024-10-08 22:55:51 字数 826 浏览 2 评论 0原文

我支持 SQL Server 2000 至 2008R2（所有标准版）。

问题1

请考虑以下假设情况。假设您有一个 5 个表视图，其中表内部连接。我们进一步假设我从每个表中提取 10 个字段，因此我有一个总共包含 50 个字段的视图。如果我仅从视图中选择 2 个字段（两个表中各一个），假设您拥有所有相同的表和连接，该查询是否会比等效的表连接脚本慢？

问题 2

如果我使用如下脚本创建通用视图会怎样：

Create View SomeView
AS

select * from SomeTable
Go

这些表定期添加字段。每次我向“SomeTable”添加字段时，视图都会自动工作，对吗？这是否明显慢一些如果我的 SomeTable 有 50 个字段，并且我从通用视图中选择了其中两个字段？如果我从 Select * 视图中选择两个字段，它是否只会查询这些字段？

问题3 如果我创建如下所示的视图

Create View Blah
AS
select (some fields) 
from TableA join TableB on TableA.Blah = TableB.Blah
Go

然后我将使用如下视图：

Select (some fields) from dbo.Blah where SomeDate >= '1/1/2008'

我会损失任何性能吗？优化器实际上只会提取适用的视图记录还是会提取所有记录然后子选择结果集？

TIA。

原文

I support SQL Servers 2000 thru 2008R2 (all Standard Edition).

Question 1

Please consider the following hypothetical situation. Let's say you have a 5 table view, with the tables inner joined. Let's further assume that I am pulling 10 fields from each table, so I have a View with a total of 50 fields in it. If I selected only 2 fields from the view (one in each of two of the tables), would that query be slower than the equivalent table join script assuming you had all the same tables and joins?

Question 2

What if I created a generic view with a script like this:

Create View SomeView
AS

select * from SomeTable
Go

These tables have fields added to them fairly regularly. Each time I add a field to "SomeTable" the view would automatically work, correct? Is this appreciably slower
if I SomeTable had 50 fields and I selected two of them from the generic view? If I select two fields from the Select * view, will it only query for those fields?

Question 3
If I create a view like the following

Create View Blah
AS
select (some fields) 
from TableA join TableB on TableA.Blah = TableB.Blah
Go

Then I use the view like this:

Select (some fields) from dbo.Blah where SomeDate >= '1/1/2008'

Will I lose any performance? Will the optimizer actually only pull view records that apply or will it pull all and then sub select the result set?

TIA.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

未蓝澄海的烟 2024-10-15 22:55:51

<块引用>
每次我向“SomeTable”添加字段时，视图都会自动工作，对吗？

不，请参见此处如何使用sp_refreshview确保视图将具有基础表更改

<块引用>
优化器实际上只会提取适用的视图记录，还是会提取所有记录，然后再选择结果集？

视图只不过是一个存储的查询，优化器足够智能，可以仅对其需要的数据进行范围搜索或扫描，您可以通过查看执行计划来验证这一点

<块引用>
所以我有一个视图，其中总共有 50 个字段。如果我仅从视图中选择 2 个字段（两个表中各一个），假设您拥有所有相同的表和连接，该查询是否会比等效的表连接脚本慢？

再次针对带有 * 和仅 2 列的查询运行 stats io 或 time 并查看是否发现读取和时间有差异

，例如

SET STATISTICS IO ON
GO

SELECT * FROM SomeView

SET STATISTICS IO OFF
GO



SET STATISTICS IO ON
GO

SELECT Col1, Col2 FROM SomeView

SET STATISTICS IO OFF
GO

Each time I add a field to "SomeTable" the view would automatically work, correct?

No, see here how to make sure that the view will have the underlying table changes by using sp_refreshview

Will the optimizer actually only pull view records that apply or will it pull all and then sub select the result set?

A view is nothing but a stored query, the optimizer is smart enough to do a range seek or scan on just the data it needs, you can verify this by looking at the execution plan

so I have a View with a total of 50 fields in it. If I selected only 2 fields from the view (one in each of two of the tables), would that query be slower than the equivalent table join script assuming you had all the same tables and joins?

again run stats io or time against the query with * and just 2 columns and look if you see a difference in reads and time

so for example

SET STATISTICS IO ON
GO

SELECT * FROM SomeView

SET STATISTICS IO OFF
GO



SET STATISTICS IO ON
GO

SELECT Col1, Col2 FROM SomeView

SET STATISTICS IO OFF
GO

回复收藏 0 原文

~没有更多了~