Sql Server 2008 嵌套视图
关于是否使用嵌套视图是否有通用的最佳实践？使用嵌套视图时是否会影响性能？是否有最佳实践表明，只有达到 4 层或更多层深度后，性能才会真正受到影响？
我问这个问题的原因是因为我正在纠结是否使用它们。收到报告请求并不罕见，我访问该信息的唯一方法是将 20 个或更多表连接在一起。字段并非从所有表中返回，但需要选择正确的数据。在这种情况下，我喜欢嵌套视图并为其他报告重用较低级别的视图，因为如果需要更改逻辑，我只需更新一个视图，然后所有报告都会更新。我使用的许多表都包含数百万条记录。
然而，这也许不是一个好的做法。您介意分享您对此的想法吗？
Is there a general best practice on whether or not to use nested views? Is there a performance hit when using nested views? Is there a best practice that says there really isn't a performance hit until you go 4 or more layers deep?
The reason I'm asking this is because I'm struggling with whether or not to use them. It is not unusual to get a report request of which the only way I can get access to that information is by joining 20 or more tables together. Fields are not returned from all the tables but are needed to select the correct data. In this case I like nesting the views and reusing the lower level views for other reports because if a change to the logic is needed I just update one view and all reports are updated. Many of the tables I work with contain millions and millions of records.
However perhaps this is not a good practice. Do you mind sharing your thoughts on this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我会不惜一切代价避免这种情况。首先，一旦嵌套视图，它们就无法被索引。接下来，因为他们必须完全具体化底层视图才能进入下一层。因此，您可以具体化数百万条记录以获得 5 条记录的最终结果。我们几乎失去了一位价值数百万美元的客户，因为当我们的开发人员在一个数据库（不是我输入设计的数据库）上执行此操作时，性能非常糟糕。
最后我发现，当您需要进行更改时，这些层的维护要困难得多。跟踪 12 层视图来找到需要修复的视图并不有趣。我们还遇到了一个问题，因为开发人员发现添加另一层比修复底层更容易，然后尝试在一个查询中访问太多表，而这些表中有太多是正在访问的相同的数百万条记录表在不同层的视图中 7 或 8 次。
在任何情况下，我都不会允许我管理的数据库中的视图中有多个层，如果您这样做，我会很生气。
I would avoid this at all costs. First once you nest views they cannot be indexed. Next, since they have to fully materialize the underlying views to get to the next layer. So you could be materializing multi-millions of records to get an end result of 5 records. We very nearly lost a multimillion dollar client because performance was so abysmal when our devs did this on one database (not a database I had input into the design of).
Finally I have found that these sorts of layers are much, much harder to maintain when you need to make a change. It's no fun to track through 12 layers of views to find the one you need to fix. We also ran into an issue because devs found it easier just to add another layer than fix the underlying layers and then were trying to access too many tables in one query and way too many of those tables were the same multi-million record table being accessed 7 or 8 times in different layers of the views.
There is no circumstance where I would allow more than one layer in a view in a database I manage and I'd be angry if you did that.
其他需要考虑的选择：
索引视图——如果使用不当，使用起来可能很危险，但性能提升可能是惊人的。
分析 - 例如分组集
程序和流程临时表 -- 通过过程获取所需的数据，将其写出到临时表中，然后从临时表中选择。
总的来说，我不喜欢视图上的视图或嵌套视图的性能影响。
一般来说，您可以使用表之间的正确连接生成一个视图，其中包含您需要的所有信息，并使用条件过滤掉数据。
Other options to consider:
Indexed Views -- Can be dangerous to use if not used correctly but the performance gains can be amazing.
Analytics -- such as grouping sets
procedures & temp tables -- Get the data you need via procedure write it out to temp tables select from temp tables.
Overall I don't like the performance hit of view on view on view or nested views.
Generally you can generate one view using the correct joins between tables which contains all the information your after and filter out the data using criteria.