嵌入的视图（视图中的视图......）邪恶，如果是这样，为什么？ [英] Are embedded views (Views within views...) evil and if so why?

问题描述

数据库开发人员，


我想借鉴你的观点体验。我有一个包含很多视图的数据库

。有时，视图包含其他视图，而

这些视图又可能包含视图。事实上，我在

我的数据库中有一些观点，这些观点是深达6层的嵌套视图的产物！


我们这样做的原因是。


1.面向对象的性质。使用它们很容易。

2.更改基础视图（添加新字段，删除等），

自动更高的视图继承此新信息。这个

使维护变得非常简单。

3.这些嵌套视图仅用于我们的

应用程序的报告端，而不是用于应用程序使用的日常数据库。

我们使用Crystal Reports和Crystal足够聪明（不能相信我只是说b $ b只是说关于Crystal）只撤回报告访问的字段

。换句话说，Crystal将发布一个


从ReportingView中选择field1，field2，field3 ......即使是

，也可以使用ReportingView。包含很长的字段列表。


我能看到的问题。


1.父视图通常使用Select * From childview。这意味着

我们必须执行sp_refreshview命令所有视图

每当改变子视图时。

2.父视图返回大量信息，不一定是
二手。

3.更难以准确追踪信息的来源

来自。您必须直接钻到子视图以查看原始表连接等的



有没有人对此数据库设计有任何意见？我很乐意

听取你对战壕的意见和故事。


祝你好运，


Rod。

Fellow database developers,

I would like to draw on your experience with views. I have a database
that includes many views. Sometimes, views contains other views, and
those views in turn may contain views. In fact, I have some views in
my database that are a product of nested views of up to 6 levels deep!

The reason we did this was.

1. Object-oriented in nature. Makes it easy to work with them.
2. Changing an underlying view (adding new fields, removing etc),
automatically the higher up views inherit this new information. This
make maintenance very easy.
3. These nested views are only ever used for the reporting side of our
application, not for the day-to-day database use by the application.
We use Crystal Reports and Crystal is smart enough (can''t believe I
just said that about Crystal) to only pull back the fields that are
being accessed by the report. In other words, Crystal will issue a

Select field1, field2, field3 from ReportingView Where .... even
though "ReportingView" contains a long list of fields.

Problems I can see.

1. Parent views generally use "Select * From childview". This means
that we have to execute a "sp_refreshview" command against all views
whenever child views are altered.
2. Parent views return a lot of information that isn''t necessarily
used.
3. Makes it harder to track down exactly where the information is
coming from. You have to drill right through to the child view to see
the raw table joins etc.

Does anyone have any comments on this database design? I would love to
hear your opinions and tales from the trenches.

Best regards,

Rod.

推荐答案

nesed VIEWs没有问题，事实上，你可以做一些

整洁的技巧在

嵌套的各个级别使用WITH CHECK OPTION。


真正的诀窍是确保VIEW具有合理的名称和

含义。

There are no problems with nesed VIEWs and in fact, you can do some
neat tricks using the WITH CHECK OPTION at various levels of the
nesting.

The real trick is to make sure that the VIEWs have sensible names and
meanings.

>使用VIEWs没有问题，事实上，你可以在
嵌套的各个层面使用WITH CHECK OPTION做一些

> There are no problems with nesed VIEWs and in fact, you can do some

巧妙的技巧。

所以您从未尝试使用嵌套视图来诊断由

a第三方开发的代码中的性能或错误吗？


嵌套视图是维护的噩梦，应该避免不惜一切代价。

真正的诀窍是确保VIEW具有合理的名称和含义。


真正的诀窍是先考虑一下你想要做的事情并正确设计你的

架构。

-

Tony Rogerson

SQL Server MVP
http://sqlserverfaq.com - 免费视频教程


" - CELKO - " < JC ******* @ earthlink.net>在留言中写道

新闻：11 ********************** @ v46g2000cwv.googlegr oups.com ...没有问题有了必要的VIEW，事实上，你可以在
嵌套的各个层面使用WITH CHECK OPTION做一些巧妙的技巧。

真正的诀窍是确保视图有合理的名称和含义。

neat tricks using the WITH CHECK OPTION at various levels of the
nesting.
So you have never tried to diagnose performance or bugs in code developed by
a third party using nested views then?

Nested views are a maintanence nightmare and should be avoided at all cost.
The real trick is to make sure that the VIEWs have sensible names and
meanings.
The real trick is to think through what you are trying to do and design your
schema properly in the first place.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"--CELKO--" <jc*******@earthlink.net> wrote in message
news:11**********************@v46g2000cwv.googlegr oups.com... There are no problems with nesed VIEWs and in fact, you can do some
neat tricks using the WITH CHECK OPTION at various levels of the
nesting.

The real trick is to make sure that the VIEWs have sensible names and
meanings.

嗨Rod，


请不要使用嵌套视图！我想在第（3）点你已经提到了什么

我也在提及它是一个维护噩梦。


考虑像我一样的人进来和需要解决

性能问题，使用嵌套视图解决

查询的问题需要更长的时间，因为它需要额外的一步来解决什么

每个视图正在进行以及优化者如何扩展视图以制定

真正的查询计划。


单级视图很好所以只要它们仅在

数据库或报告应用程序中使用 - 在使用具有固定访问路径的应用程序时，总是尝试使用存储过程

。


Tony。


-

Tony Rogerson

SQL Server MVP
http://sqlserverfaq.com - 免费视频教程


< ; RO ****** @ gmail.com>在留言中写道

news：11 ********************** @ u72g2000cwu.googlegr oups.com ...

Hi Rod,

Please don''t use nested views! I think in point (3) you have mentioned what
I''m refering too, its a maintanence nightmare.

Consider somebody like me coming in and needing to work through a
performance problem, it takes significantly longer to work out problems with
queries using nested views because it takes an extra step to work out what
each view is doing and how the optimiser is expanding the views to formulate
the real query plan.

Single level views are fine so long as they are only used within the
database or a reporting application - always try and use stored procedures
when using an application with fixed access paths.

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

<ro******@gmail.com> wrote in message
news:11**********************@u72g2000cwu.googlegr oups.com...

数据库开发人员，

我想借鉴你的观点经验。我有一个包含很多视图的数据库。有时，视图包含其他视图，而这些视图又可能包含视图。事实上，我在我的数据库中有一些观点，这些观点是最多6层深度嵌套视图的产物！

我们这样做的原因是。
1.面向对象的性质。使用它们很容易。
2.更改基础视图（添加新字段，删除等），
自动更高的视图继承这些新信息。这使得维护变得非常容易。
3.这些嵌套视图仅用于我们
应用程序的报告端，而不是用于应用程序的日常数据库使用。我们使用Crystal Reports和Crystal足够聪明（不能相信我只是说关于Crystal）只能撤回报告访问的字段。换句话说，Crystal将从ReportingView Where发出一个选择field1，field2，field3 ......甚至
尽管ReportingView也是如此。包含很长的字段列表。

我能看到的问题。

1.父视图通常使用Select * From childview。这意味着我们必须执行sp_refreshview。每次观看儿童观点时都要对所有观点进行命令。
2.家长观点会返回大量不一定使用的信息。
3.使跟踪变得更难准确地说明来自哪里的信息。您必须直接钻到子视图以查看原始表连接等。

有没有人对此数据库设计有任何意见？我很乐意听取你的意见和故事。

致以最诚挚的问候，

Rod。

Fellow database developers,

I would like to draw on your experience with views. I have a database
that includes many views. Sometimes, views contains other views, and
those views in turn may contain views. In fact, I have some views in
my database that are a product of nested views of up to 6 levels deep!

The reason we did this was.

1. Object-oriented in nature. Makes it easy to work with them.
2. Changing an underlying view (adding new fields, removing etc),
automatically the higher up views inherit this new information. This
make maintenance very easy.
3. These nested views are only ever used for the reporting side of our
application, not for the day-to-day database use by the application.
We use Crystal Reports and Crystal is smart enough (can''t believe I
just said that about Crystal) to only pull back the fields that are
being accessed by the report. In other words, Crystal will issue a

Select field1, field2, field3 from ReportingView Where .... even
though "ReportingView" contains a long list of fields.

Problems I can see.

1. Parent views generally use "Select * From childview". This means
that we have to execute a "sp_refreshview" command against all views
whenever child views are altered.
2. Parent views return a lot of information that isn''t necessarily
used.
3. Makes it harder to track down exactly where the information is
coming from. You have to drill right through to the child view to see
the raw table joins etc.

Does anyone have any comments on this database design? I would love to
hear your opinions and tales from the trenches.

Best regards,

Rod.

这篇关于嵌入的视图（视图中的视图......）邪恶，如果是这样，为什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！