当前位置： 文江博客话题详情

使用 SqlServer 视图有哪些缺点？

发布于 2024-09-30 18:35:49 字数 210 浏览 3 评论 0原文

使用 SqlServer 视图有哪些缺点？

我经常创建视图以非规范化形式显示数据。

我发现查询这些联接之一比使用许多表之间的复杂联接生成复杂查询要容易得多，因此速度更快、更不容易出错并且更具自记录性。特别是当我从不同角度分析相同的数据（许多相同的字段，相同的表连接）时。

但是创建和使用这些视图有成本吗？

我是否减慢（或加快？）查询处理速度？

原文

What are the downsides of using SqlServer Views?

I create views frequently to show my data in a denormalized form.

I find it much easier and therefore faster, less error prone, and more self documenting, to query one of these joins rather than to generate complex queries with complicated joins between many tables. Especially when I am analyzing the same data (many same fields, same table joins) from different angles.

But is there a cost to creating and using these views?

Am I slowing down (or speeding up?) query processing?

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

扫码二维码加入Web技术交流群

发布评论

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

评论（10）

左耳近心 2024-10-07 18:35:49

视图有优点也有缺点。

优点：

  1. 它们是虚拟表，不作为不同的对象存储在数据库中。所存储的只是 SELECT 语句。
  2. 它可以通过限制用户可以看到的内容来用作安全措施。
  3. 通过将常用的复杂查询封装到视图中，可以使它们更易于阅读。但这是一把双刃剑——参见缺点#3。

缺点：

  1. 它没有缓存优化的执行计划，因此它不会像存储过程那么快。
  2. 由于它基本上只是 SELECT 的抽象，因此它比纯 SELECT 稍微慢一些。
  3. 它可能隐藏复杂性并导致陷阱。 （问题：ORDER BY 不被尊重）。

我个人的意见是不要使用视图，而是使用存储过程，因为它们提供了视图的安全性和封装性，而且还提高了性能。

When comes to Views there are advantages and disadvantages.

Advantages:

  1. They are virtual tables and not stored in the database as a distinct object. All that is stored is the SELECT statement.
  2. It can be used as a security measure by restricting what the user can see.
  3. It can make commonly used complex queries easier to read by encapsulating them into a view. This is a double edged sword though - see disadvantages #3.

Disadvantages:

  1. It does not have an optimized execution plan cached so it will not be as fast as a stored procedure.
  2. Since it is basically just an abstraction of a SELECT it is marginally slower than doing a pure SELECT.
  3. It can hide complexity and lead to gotchas. (Gotcha: ORDER BY not honored).

My personal opinion is to not use Views but to instead use stored procedures as they provide the security and encapsulation of Views but also come with improved performance.

回复 原文
清君侧 2024-10-07 18:35:49

使用视图的一个可能的缺点是您抽象了底层设计的复杂性，这可能导致初级开发人员和报告创建者滥用。

对于一个特别大且复杂的项目，我设计了一组视图，主要由报表设计者用来填充水晶报表。几周后我发现初级开发人员已经开始使用这些视图来获取聚合并加入这些已经很大的视图，只是因为它们在那里并且易于使用。 （数据库中有很强的 EAV 设计元素。）在初级开发人员开始询问为什么看似简单的报告需要花费很多分钟来执行后，我发现了这一点。

One possible downside of using views is that you abstract the complexity of the underlying design which can lead to abuse by junior developers and report creators.

For a particularly large and complex project I designed a set of views which were to be used mostly by report designers to populate crystal reports. I found out weeks later that junior devs had started using these views to fetch aggregates and join these already large views simply because they were there and were easy to consume. (There was a strong element of EAV design in the database.) I found out about this after junior devs started asking why seemingly simple reports were taking many minutes to execute.

回复 原文
逆光飞翔i 2024-10-07 18:35:49

视图的效率在很大程度上取决于基础表。视图实际上只是一种有组织的、一致的方式来查看查询结果。如果用于形成视图的查询良好，并且在基础表上使用正确的索引，则视图不应对性能产生负面影响。

在 SQL Server 中，您还可以创建物化视图或索引视图（自 SQL Server 2000 起），这会增加速度有点。

The efficiency of a view depends in large part on the underlying tables. The view really is a just an organized an consistent way to look at query results. If the query used to form the view is good, and uses proper indexes on the underlying tables, then the view shouldn't negatively impact performance.

In SQL Server you can also create materialized or indexed views (since SQL Server 2000), which increase speed somewhat.

回复 原文
半﹌身腐败 2024-10-07 18:35:49

我也经常使用视图。然而，需要注意的一件事是，如果基础表频繁更改（尤其是在开发期间），则使用大量视图可能难以维护。

编辑：话虽如此，我发现能够简化和重用复杂查询的便利和优势超过了维护问题，特别是在负责任地使用视图的情况下。

I use views regularly as well. One thing to note, however, is that using lots of views could be hard to maintain if your underlying tables change frequently (especially during development).

EDIT: Having said that, I find the convenience and advantage of being able to simplify and re-use complex queries outweighs the maintenance issue, especially if the views are used responsibly.

回复 原文
空气里的味道 2024-10-07 18:35:49

当视图包含最终查询最终未使用的逻辑、列、行或表时，视图可能会降低性能。我无法告诉你有多少次看到这样的东西：（

SELECT ... 
FROM (View with complex UNION of ActiveCustomer and InactiveCustomer tables)
WHERE Active = True

从而从 InactiveCustomer 表中过滤掉视图中包含的所有行），或者

SELECT (one column)
FROM (view that returns 50 columns)

（SQL 必须检索大量数据，然后在稍后丢弃这些数据）步骤。其他列的检索成本可能很高，例如通过书签查找），或者

SELECT ...
FROM (view with complex filters)
WHERE (entirely different filters)

（如果直接查询表，SQL 可能会使用更合适的索引），
或者

SELECT (only fields from a single table)
FROM (view that contains crazy complex joins)

（通过连接产生大量 CPU 开销，以及随后丢弃的表读取的不必要 IO），或者我最喜欢的：（

SELECT ...
FROM (Crazy UNION of 12 tables each containing a month of data)
WHERE OrderDate = @OrderDate

当它只需要读取 1 个表时，读取 12 个表）。

在大多数情况下，SQL 足够聪明，可以“透视表象”并提出有效的查询计划。但在其他情况下（尤其是非常复杂的情况），它不能。在上述每种情况下，答案是删除视图并查询基础表。

至少（即使您认为 SQL 足够聪明，可以优化它），消除视图有时可以使您自己的查询调试和优化更容易（更明显需要做什么）。

Views can be a detriment to performance when the view contains logic, columns, rows, or tables that aren't ultimately used by your final query. I can't tell you how many times I've seen stuff like:

SELECT ... 
FROM (View with complex UNION of ActiveCustomer and InactiveCustomer tables)
WHERE Active = True

(thus filtering out all rows that were included in the view from the InactiveCustomer table), or

SELECT (one column)
FROM (view that returns 50 columns)

(SQL has to retrieve lots of data that is then discarded at a later step. Its possible those other columns are expensive to retrieve, like through a bookmark lookup), or

SELECT ...
FROM (view with complex filters)
WHERE (entirely different filters)

(its likely that SQL could have used a more appropriate index if the tables were queried directly),
or

SELECT (only fields from a single table)
FROM (view that contains crazy complex joins)

(lots of CPU overhead through the join, and unnecessary IO for the table reads that are later discarded), or my favorite:

SELECT ...
FROM (Crazy UNION of 12 tables each containing a month of data)
WHERE OrderDate = @OrderDate

(Reads 12 tables when it only really needs to read 1).

In most cases, SQL is smart enough to "see through the covers" and come up with an effective query plan anyway. But in other cases (especially very complex ones), it can't. In each of the above situations, the answer was to remove the view and query the underlying tables instead.

At the very least (even if you think SQL would be smart enough to optimize it anyway), eliminating the view can sometimes make your own query debugging and optimization easier (a bit more obvious what needs to be done).

回复 原文
甜嗑 2024-10-07 18:35:49

我遇到的视图的一个缺点是将它们合并到分布式查询中时，性能会大幅下降。这篇 SQLMag 文章讨论了 - 同时我在演示中使用了高度人工的数据，我在“现实世界”中一次又一次遇到这个问题。

尊重你的观点，他们就会善待你。

A downside to views that I've run into is a dive in performance when incorporating them into distributed queries. This SQLMag article discusses - and whilst I use highly artificial data in the demo, I've run into this problem time and time again in the "real world".

Respect your views, and they'll treat you well.

回复 原文
最佳男配角 2024-10-07 18:35:49

SQL Server 中视图的各种限制有哪些？

视图的 11 个主要限制

  • 视图不支持 COUNT ()；但是，它可以支持 COUNT_BIG ()
  • ORDER BY 子句在视图中不起作用
  • 常规查询或存储过程在我们需要另一列时为我们提供了灵活性；我们可以立即向常规查询添加一列。如果我们想对视图做同样的事情，那么我们必须首先修改它们。
  • 在不经常使用的视图上创建索引
  • 一旦创建视图，如果基本表添加或删除了任何列，通常不会反映在视图中在刷新之前，
  • 索引视图中不允许进行 UNION 操作。
  • 我们无法在嵌套视图上创建索引，这意味着我们无法在从另一个视图构建的视图上创建索引。
  • 索引视图中不允许
  • 自连接 索引视图中不允许外连接 索引视图
  • 中不允许跨数据库查询

源 SQL MVP Pinal Dave

http://blog.sqlauthority.com/2010/10/03/sql -server-the-limitations-of-the-views-11-and-more/

What are Various Limitations of the Views in SQL Server?

Top 11 Limitations of Views

  • Views do not support COUNT (); however, it can support COUNT_BIG ()
  • ORDER BY clause does not work in View
  • Regular queries or Stored Procedures give us flexibility when we need another column; we can add a column to regular queries right away. If we want to do the same with Views, then we will have to modify them first
  • Index created on view not used often
  • Once the view is created and if the basic table has any column added or removed, it is not usually reflected in the view till it is refreshed
  • UNION Operation is not allowed in Indexed View
  • We cannot create an Index on a nested View situation means we cannot create index on a view which is built from another view.
  • SELF JOIN Not Allowed in Indexed View
  • Outer Join Not Allowed in Indexed Views
  • Cross Database Queries Not Allowed in Indexed View

Source SQL MVP Pinal Dave

http://blog.sqlauthority.com/2010/10/03/sql-server-the-limitations-of-the-views-eleven-and-more/

回复 原文
月光色 2024-10-07 18:35:49

当我开始时，我总是认为视图会增加性能开销，但是经验描绘了一个不同的故事（视图机制本身的开销可以忽略不计）。

这完全取决于底层查询是什么。 此处或此处，最终您应该测试两种方式的性能以获得清晰的性能概况

When I started I always though views added performance overhead, however experience paints a different story (the view mechanism itself has negligible overhead).

It all depends on what the underlying query is. Check out indexed views here or here , ultimately you should test the performance both ways to obtain a clear performance profile

回复 原文
倾城花音 2024-10-07 18:35:49

我最大的“抱怨”是 ORDER BY 在视图中不起作用。虽然这是有道理的，但如果没有预料到的话，这种情况可能会跳起来咬人。因此，在某些无法稍后指定 ORDER BY 的情况下，我不得不放弃使用视图来使用 SPROCS（这本身就有足够多的问题）。 （我希望有一个具有“最终视图”的构造——例如可能包括排序依据——语义）。

http: //blog.sqlauthority.com/2010/10/03/sql-server-the-limitations-of-the-views-eleven-and-more/ （限制 #1 是关于 ORDER BY :-）

My biggest 'gripe' is that ORDER BY does not work in a view. While it makes sense, it is a case which can jump up and bite if not expected. Because of this I have had to switch away from using views to SPROCS (which have more than enough problems of their own) in a few cases where I could not specify an ORDER BY later. (I wish there was a construct with "FINAL VIEW" -- e.g. possibly include order by -- semantics).

http://blog.sqlauthority.com/2010/10/03/sql-server-the-limitations-of-the-views-eleven-and-more/ (Limitation #1 is about ORDER BY :-)

回复 原文
独孤求败 2024-10-07 18:35:49

以下是一个 SQL hack，它允许在视图中引用 order by：

create view toto1 as 
select top 99.9999 percent F1
from Db1.dbo.T1 as a 
order by 1

但我更喜欢使用 Row_Number：

create view toto2 as 
select *,  ROW_NUMBER() over (order by [F1]) as RowN from ( 
select f1
from Db1.dbo.T1) as a

The following is a SQL hack that allows an order by to be referenced in a view:

create view toto1 as 
select top 99.9999 percent F1
from Db1.dbo.T1 as a 
order by 1

But my preference is to use Row_Number:

create view toto2 as 
select *,  ROW_NUMBER() over (order by [F1]) as RowN from ( 
select f1
from Db1.dbo.T1) as a
回复 原文
~没有更多了~

关于作者

旧故

暂无简介

0 文章
0 评论
22 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

qq_E2Iff7

文章 0 评论 0

Archangel

文章 0 评论 0

freedog

文章 0 评论 0

Hunk

文章 0 评论 0

18819270189

文章 0 评论 0

wenkai

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文