I infrequently (monthly/quarterly) generate hundreds of Crystal Reports reports using Microsoft SQL Server 2005 database views. Are those views wasting CPU cycles and RAM during all the time that I am not reading from them? Should I instead use stored procedures, temporary tables, or short-lived normal tables since I rarely read from my views?
我很少（每月/每季度）使用Microsoft SQL Server 2005数据库视图生成数百个Crystal Reports报表。这些视图是否在我不读取它们的过程中浪费CPU周期和RAM？我应该使用存储过程，临时表或短命的普通表，因为我很少从我的视图中读取？
I'm not a DBA so I don't know what's going on behind the scenes inside the database server.
我不是DBA所以我不知道数据库服务器内幕后发生了什么。
Is it possible to have too many database views? What's considered best practice?
是否有可能有太多的数据库视图？什么是最佳做法？
4 个解决方案
#1
8
For the most part, it doesn't matter. Yes, SQL Server will have more choices when it parses SELECT * FROM table (it'll have to look in the system catalogs for 'table') but it's highly optimized for that, and provided you have sufficient RAM (most servers nowadays do), you won't notice a difference between 0 and 1,000 views.
在大多数情况下，没关系。是的，SQL Server在分析SELECT * FROM表时会有更多的选择（它必须查看系统目录中的'table'）但它已经针对此进行了高度优化，并且只要你有足够的RAM（现在大多数服务器都可以） ，你不会注意到0到1,000个视图之间的差异。
However, from a people-perspective, trying to manage and figure out what "hundreds" of views are doing is probably impossible, so you likely have a lot of duplicated code in there. What happens if some business rules change that are embedded in these redundant views?
但是，从人们的角度来看，尝试管理和弄清楚“数百”视图正在做什么可能是不可能的，所以你可能会有很多重复的代码。如果某些业务规则发生变化，嵌入在这些冗余视图中会发生什么？
The main point of views is to encapsulate business logic into a pseudo table (so you may have a person table, but then a view called "active_persons" which does some magic). Creating a view for each report is kind of silly unless each report is so isolated and unique that there is no ability to re-use.
主要观点是将业务逻辑封装到伪表中（因此您可能有一个人员表，但后来称为“active_persons”的视图会产生一些魔力）。除非每个报告都是如此孤立和独特，以至于无法重复使用，否则为每个报告创建一个视图是有点愚蠢的。
#2
2
A view is a query that you run often with preset parameters. If you know you will be looking at the same data all the time you can create a view for ease of use and for data binding.
视图是您经常使用预设参数运行的查询。如果您知道您将始终查看相同的数据，则可以创建易于使用和数据绑定的视图。
That being said, when you select from a view the view defining query is run along with the query you are running.
话虽这么说，当您从视图中选择时，视图定义查询将与您正在运行的查询一起运行。
For example, if vwCustomersWhoHavePaid is:
例如，如果vwCustomersWhoHavePaid是：
Select * from customers where paid = 1
and the query you are running returns the customers who have paid after August first is formatted like this:
并且您正在运行的查询将返回8月初之后已付款的客户，其格式如下：
Select * from vwCustomersWhoHavePaid where datepaid > '08/01/08'
The query you are actually running is:
您实际运行的查询是：
Select * from (Select * from customers where paid = 1) where datepaid > '08/01/08'
This is something you should keep in mind when creating views, they are a way of storing data that you look at often. It's just a way of organizing data so it's easier to access.
在创建视图时，您应该记住这一点，它们是一种存储您经常查看的数据的方式。它只是一种组织数据的方式，因此更容易访问。
#3
1
The views are only going to take up cpu/memory resources when they are called.
视图仅在调用时占用cpu /内存资源。
Anyhow, best practice would be to consolidate what can be consolidated, remove what can be removed, and if it's literally only used by your reports, choose a consistent naming standard for the views so they can easily be grouped together when looking for a particular view.
无论如何，最佳做法是合并可以合并的内容，删除可以删除的内容，如果它只是由报表使用，请为视图选择一致的命名标准，以便在查找特定视图时可以轻松地将它们组合在一起。
Also, unless you really need transactional isolation, consider using the NOLOCK table hint in your queries.
此外，除非您确实需要事务隔离，否则请考虑在查询中使用NOLOCK表提示。
-- Kevin Fairchild
- 凯文费尔柴尔德
#4
1
You ask: What's going on behind the scenes?
你问：幕后发生了什么？
A view is a bunch of SQL text. When a query uses a view, SQL Server places that SQL text into the query. This happens BEFORE optimization. The result is the optimizer can consider the combined code instead of two separate pieces of code for the best execution plan.
视图是一堆SQL文本。当查询使用视图时，SQL Server将该SQL文本放入查询中。这在优化之前发生。结果是优化器可以考虑组合代码而不是两个单独的代码片段以获得最佳执行计划。
You should look at the execution plans of your queries! There is so much to learn there.
您应该查看查询的执行计划！那里有很多值得学习的东西。
SQL Server also has a concept of a clustered view. A clustered view is a system maintained result set (each insert/update/delete on the underlying tables can cause insert/update/deletes on the clustered view's data). It is a common mistake to think that views operate in the way that clustered views operate.
SQL Server还具有集群视图的概念。集群视图是系统维护的结果集（基础表上的每个插入/更新/删除都可能导致对集群视图的数据进行插入/更新/删除）。认为视图以集群视图的运行方式运行是一种常见错误。
#1
8
For the most part, it doesn't matter. Yes, SQL Server will have more choices when it parses SELECT * FROM table (it'll have to look in the system catalogs for 'table') but it's highly optimized for that, and provided you have sufficient RAM (most servers nowadays do), you won't notice a difference between 0 and 1,000 views.
在大多数情况下，没关系。是的，SQL Server在分析SELECT * FROM表时会有更多的选择（它必须查看系统目录中的'table'）但它已经针对此进行了高度优化，并且只要你有足够的RAM（现在大多数服务器都可以） ，你不会注意到0到1,000个视图之间的差异。
However, from a people-perspective, trying to manage and figure out what "hundreds" of views are doing is probably impossible, so you likely have a lot of duplicated code in there. What happens if some business rules change that are embedded in these redundant views?
但是，从人们的角度来看，尝试管理和弄清楚“数百”视图正在做什么可能是不可能的，所以你可能会有很多重复的代码。如果某些业务规则发生变化，嵌入在这些冗余视图中会发生什么？
The main point of views is to encapsulate business logic into a pseudo table (so you may have a person table, but then a view called "active_persons" which does some magic). Creating a view for each report is kind of silly unless each report is so isolated and unique that there is no ability to re-use.
主要观点是将业务逻辑封装到伪表中（因此您可能有一个人员表，但后来称为“active_persons”的视图会产生一些魔力）。除非每个报告都是如此孤立和独特，以至于无法重复使用，否则为每个报告创建一个视图是有点愚蠢的。
#2
2
A view is a query that you run often with preset parameters. If you know you will be looking at the same data all the time you can create a view for ease of use and for data binding.
视图是您经常使用预设参数运行的查询。如果您知道您将始终查看相同的数据，则可以创建易于使用和数据绑定的视图。
That being said, when you select from a view the view defining query is run along with the query you are running.
话虽这么说，当您从视图中选择时，视图定义查询将与您正在运行的查询一起运行。
For example, if vwCustomersWhoHavePaid is:
例如，如果vwCustomersWhoHavePaid是：
Select * from customers where paid = 1
and the query you are running returns the customers who have paid after August first is formatted like this:
并且您正在运行的查询将返回8月初之后已付款的客户，其格式如下：
Select * from vwCustomersWhoHavePaid where datepaid > '08/01/08'
The query you are actually running is:
您实际运行的查询是：
Select * from (Select * from customers where paid = 1) where datepaid > '08/01/08'
This is something you should keep in mind when creating views, they are a way of storing data that you look at often. It's just a way of organizing data so it's easier to access.
在创建视图时，您应该记住这一点，它们是一种存储您经常查看的数据的方式。它只是一种组织数据的方式，因此更容易访问。
#3
1
The views are only going to take up cpu/memory resources when they are called.
视图仅在调用时占用cpu /内存资源。
Anyhow, best practice would be to consolidate what can be consolidated, remove what can be removed, and if it's literally only used by your reports, choose a consistent naming standard for the views so they can easily be grouped together when looking for a particular view.
无论如何，最佳做法是合并可以合并的内容，删除可以删除的内容，如果它只是由报表使用，请为视图选择一致的命名标准，以便在查找特定视图时可以轻松地将它们组合在一起。
Also, unless you really need transactional isolation, consider using the NOLOCK table hint in your queries.
此外，除非您确实需要事务隔离，否则请考虑在查询中使用NOLOCK表提示。
-- Kevin Fairchild
- 凯文费尔柴尔德
#4
1
You ask: What's going on behind the scenes?
你问：幕后发生了什么？
A view is a bunch of SQL text. When a query uses a view, SQL Server places that SQL text into the query. This happens BEFORE optimization. The result is the optimizer can consider the combined code instead of two separate pieces of code for the best execution plan.
视图是一堆SQL文本。当查询使用视图时，SQL Server将该SQL文本放入查询中。这在优化之前发生。结果是优化器可以考虑组合代码而不是两个单独的代码片段以获得最佳执行计划。
You should look at the execution plans of your queries! There is so much to learn there.
您应该查看查询的执行计划！那里有很多值得学习的东西。
SQL Server also has a concept of a clustered view. A clustered view is a system maintained result set (each insert/update/delete on the underlying tables can cause insert/update/deletes on the clustered view's data). It is a common mistake to think that views operate in the way that clustered views operate.
SQL Server还具有集群视图的概念。集群视图是系统维护的结果集（基础表上的每个插入/更新/删除都可能导致对集群视图的数据进行插入/更新/删除）。认为视图以集群视图的运行方式运行是一种常见错误。