What is the difference between SELECT data from table directly or from view?
And What is the best use for each one?
直接从表中或从视图中选择SELECT数据有什么区别？每个人的最佳用途是什么？
4 个解决方案
#1
3
According to Microsoft there is a performance benifit if you use indexed views in sql server 2000/2005/2008.
根据微软的说法，如果你在sql server 2000/2005/2008中使用索引视图，那么它有一个性能优势。
Indexed views can increase query performance in the following ways
1. Aggregations can be precomputed and stored in the index to minimize expensive computations during query execution.
2. Tables can be prejoined and the resulting data set stored.
3. Combinations of joins or aggregations can be stored
索引视图可以通过以下方式提高查询性能1.聚合可以预先计算并存储在索引中，以最大限度地减少查询执行期间的昂贵计算。 2.可以预先连接表，并存储结果数据集。 3.可以存储联接或聚合的组合
But just like indexes on tables, indexes on views experience modification overhead. So only add an index to a view if the benefit of its speed increase when running exceeds the time it takes to update the view's index.
但就像表上的索引一样，视图上的索引会遇到修改开销。因此，只有在运行时速度增加的好处超过更新视图索引所需的时间时，才向视图添加索引。
The below links give more information on this (on when to use what).
以下链接提供了有关此内容的更多信息（何时使用内容）。
-
SQL Server 2000/2005 Indexed View Performance Tuning and Optimization Tips.
- SQL Server 2000/2005索引视图性能调优和优化提示。
-
Improving Performance with SQL Server 2000 Indexed View.
- 使用SQL Server 2000索引视图提高性能。
- See performance gains by using indexed views in SQL.
- 通过在SQL中使用索引视图来查看性能提升。
#2
1
In most databases, they are functionally interchangeable (disregarding materialized views, which are something entirely different anyway.) There are two common reasons for creating views. 1. An abstraction (and column-aliasing) mechanism, and 2. For permissions and access control. But as for efficiency, it's not an issue.
在大多数数据库中，它们在功能上是可互换的（无视物化视图，无论如何都是完全不同的。）创建视图有两个常见原因。 1.抽象（和列别名）机制，以及2.用于权限和访问控制。但就效率而言，这不是问题。
#3
1
It depends on the database and the definition of the view.
它取决于数据库和视图的定义。
A simple view that aliases columns or performs simple calculations will not be different from making the query directly.
别名列或执行简单计算的简单视图与直接进行查询没有什么不同。
However, in some cases the views can be much slower. An example: In Oracle, if you nest view queries too much (e.g. one view uses another as a table, which uses another, etc.), you can create awful performance.
但是，在某些情况下，视图可能会慢得多。例如：在Oracle中，如果您过多地嵌套视图查询（例如，一个视图使用另一个视图作为表，使用另一个视图等），则可能会创建可怕的性能。
In general you need to test with the specific database and queries in question.
通常，您需要使用特定数据库和有问题的查询进行测试。
#4
0
Think of it this way:
想一想：
A view is just a select statement that lives on the server and has been compiled by the SQL engine.
视图只是一个存在于服务器上并由SQL引擎编译的select语句。
Usually views are used to limit/simplify the results from the table.
通常，视图用于限制/简化表中的结果。
Regards K
问候K.
#1
3
According to Microsoft there is a performance benifit if you use indexed views in sql server 2000/2005/2008.
根据微软的说法，如果你在sql server 2000/2005/2008中使用索引视图，那么它有一个性能优势。
Indexed views can increase query performance in the following ways
1. Aggregations can be precomputed and stored in the index to minimize expensive computations during query execution.
2. Tables can be prejoined and the resulting data set stored.
3. Combinations of joins or aggregations can be stored
索引视图可以通过以下方式提高查询性能1.聚合可以预先计算并存储在索引中，以最大限度地减少查询执行期间的昂贵计算。 2.可以预先连接表，并存储结果数据集。 3.可以存储联接或聚合的组合
But just like indexes on tables, indexes on views experience modification overhead. So only add an index to a view if the benefit of its speed increase when running exceeds the time it takes to update the view's index.
但就像表上的索引一样，视图上的索引会遇到修改开销。因此，只有在运行时速度增加的好处超过更新视图索引所需的时间时，才向视图添加索引。
The below links give more information on this (on when to use what).
以下链接提供了有关此内容的更多信息（何时使用内容）。
-
SQL Server 2000/2005 Indexed View Performance Tuning and Optimization Tips.
- SQL Server 2000/2005索引视图性能调优和优化提示。
-
Improving Performance with SQL Server 2000 Indexed View.
- 使用SQL Server 2000索引视图提高性能。
- See performance gains by using indexed views in SQL.
- 通过在SQL中使用索引视图来查看性能提升。
#2
1
In most databases, they are functionally interchangeable (disregarding materialized views, which are something entirely different anyway.) There are two common reasons for creating views. 1. An abstraction (and column-aliasing) mechanism, and 2. For permissions and access control. But as for efficiency, it's not an issue.
在大多数数据库中，它们在功能上是可互换的（无视物化视图，无论如何都是完全不同的。）创建视图有两个常见原因。 1.抽象（和列别名）机制，以及2.用于权限和访问控制。但就效率而言，这不是问题。
#3
1
It depends on the database and the definition of the view.
它取决于数据库和视图的定义。
A simple view that aliases columns or performs simple calculations will not be different from making the query directly.
别名列或执行简单计算的简单视图与直接进行查询没有什么不同。
However, in some cases the views can be much slower. An example: In Oracle, if you nest view queries too much (e.g. one view uses another as a table, which uses another, etc.), you can create awful performance.
但是，在某些情况下，视图可能会慢得多。例如：在Oracle中，如果您过多地嵌套视图查询（例如，一个视图使用另一个视图作为表，使用另一个视图等），则可能会创建可怕的性能。
In general you need to test with the specific database and queries in question.
通常，您需要使用特定数据库和有问题的查询进行测试。
#4
0
Think of it this way:
想一想：
A view is just a select statement that lives on the server and has been compiled by the SQL engine.
视图只是一个存在于服务器上并由SQL引擎编译的select语句。
Usually views are used to limit/simplify the results from the table.
通常，视图用于限制/简化表中的结果。
Regards K
问候K.