使用SQL视图或SQL查询？ [英] Use SQL View or SQL Query?

问题描述

我工作的一个应用程序从一个MS-SQL服务器（2005年）的数据​​。在命令文本，我可以通过一个SQL查询是这样的：

 查询字符串=SELECT T1.f1，T1.f2 ，T2.f3 FROM表1 T1加入表2 T2+ 
上T1.id = T2.id AND T1.dt = T2.dt ...
 .... 
 CMD .CommandText =查询; 
  

我也可以把查询作为像这样我的SQL服务器上的一个观点：

  CREATE VIEW V1 AS 
选择T1.f1，...
  

然后，我可以使用视图在这样一个简单的查询：

 查询字符串=选择F1，F2，F3从V1 
 .... 
 cmd.CommandText =查询; 
  

我不知道哪种方式更好。将视图更快那么一个SQL查询？顺便说一句，我在这里展示的查询是一个简单的。实际的查询SELECT是比较复杂的。

解决方案

我会创造有以下几个原因。

A VIEW

A）构建良好的观点并倾向于执行比查询速度更快，但优化查询，你可能不会注意到多大的差别。

B）它使数据库内部数据库结构的知识 - 加入了一个抽象良好层（作为一个侧面说明，请考虑使用存储过程，而不是一个内嵌的查询 - 这也会使数据库本身）

C）如果你需要对数据库中的结构变化，可以保持视图，而无需重新构建你的代码是一致的。

修订我要修改这个答案在一些评论的光，从而澄清一些点...

这是绝对的事实标准视图不提供对查询任何真正的性能增益。标准视图是在运行时基本上使得比执行相同结构的查询方便的方法是没有什么不同的物化。索引视图，但是，立即物化和结果在物理存储持续。与任何设计决策，使用索引视图的应慎重考虑。天下没有免费的午餐;你付出的使用索引视图刑罚进来与维护时，有对底层数据库进行任何更改视图相关的附加存储需求和开销的形式。这些问题最好在常用的复合接合，并且其中数据被远的频率高于它被改变访问来自多个表中的数据的聚集和在例

我的实例使用还同意有关结构性变化的意见 - 增加新的列不会影响视图。然而，如果数据被移动时，归一化，归档等也可以是绝缘从应用这种变化的好方法。这些情况是罕见的，同样的结果可以通过使用存储的过程，而不是一个视图来实现。

I am working on an application to get data from a MS-SQL server (2005). In the command text, I can pass a sql query like this:

string query = "SELECT T1.f1, T1.f2, T2.f3 FROM table1 T1 join table2 T2" +
   "on T1.id = T2.id AND T1.dt = T2.dt ..."
....
cmd.CommandText = query;

I could also put the query as a view on my SQL server like this:

 CREATE VIEW V1 AS
   "SELECT T1.f1, ..."

Then I can use the view in a simplified query like this:

 string query = "SELECT f1, f2, f3 FROM V1";
 ....
 cmd.CommandText = query;

I am not sure which way is better. Will be the view be faster then a SQL query? By the way, the query I show here is a simplified one. The actual query SELECT is more complicated one.

解决方案

I would create a VIEW for several reasons

A) A well constructed view does tend to perform faster than a query, though with query optimization you may not notice much of a difference.

B) It keeps knowledge of the database structure within the database itself - adding a good layer of abstraction (as a side note, consider using a stored procedure rather than an inline query - this also keeps database knowledge within the database itself)

C) If you do need to make a structural change to the database, you can keep the view consistent without needing to rebuild your code.

AMENDMENT I'm going to amend this answer in light of some of the comments so as to clarify some points ...

It is absolutely true that a standard view does not provide any real performance gain over a query. A standard view is materialized at run time which essentially makes it no different than a convenient way to execute a query of the same structure. An index view, however, is materialized immediately and the results are persisted in physical storage. As with any design decision, the use of an indexed view should be carefully considered. There is no free lunch; the penalty you pay for use of indexed views comes in the form of additional storage requirements and overhead associated with maintaining the view when there are any changes to the underlying database. These are best used in instances of commonly used complex joining and aggregation of data from multiple tables and in cases in which data is accessed far more frequently than it is changed.

I also concur with comments regarding structural changes - addition of new columns will not affect the view. If, however, data is moved, normalized, archived, etc it can be a good way to insulate such changes from the application. These situations are RARE and the same results can be attained through the use of stored procedures rather than a view.

这篇关于使用SQL视图或SQL查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！