在存储过程中使用视图而不是表？
在存储过程中查询视图而不是原始表是一个好习惯吗？ （即使视图不提供任何不同的数据）
我总是认为这可能是一个好主意，因为它是一个额外的抽象层，并且类似于在类函数中使用属性而不是成员变量。
但现在我正在查看 ASP.NET 成员资格提供程序创建的存储过程，它们总是直接对表进行查询。
我知道使用视图时不能轻松插入数据，但是如果存储过程仅查询数据，您是否还应该直接使用表？如果有，主要原因是什么？ （表现？）
Is it a good practice to query views instead of the raw tables in stored procedures? (even if the view doesnt provide any different data)
I always figured it might be a good idea, because its an extra layer of abstraction and its similar to using properties instead of member variables in class functions.
But now I was looking at the stored procedures created by the ASP.NET Membership Provider and they always query against the tables directly.
I am aware that you cant insert data easily when using views, but if the stored procedure only queries data, should you still use tables directly? If yes, whats the main reason? (performance?)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
视图只是一个扩展为外部查询的宏。
如果您的视图包含多个联接，那么当您联接到其他视图时，当您在存储过程的 SQL 中实际看到 3 个 JOIN 时，您会突然出现 20 或 30 路 JOIN。您还会发现每个查询都是不同的：为什么要为每个查询继续连接相同的 20 或 30 个表？
一般来说，除非视图被索引/具体化并且优化器可以使用它，否则没有任何好处。
诸如对由视图屏蔽的单个表进行计算之类的想法应该位于计算列中：为什么要继续计算它？对于视图中多个表的计算，应该对其进行索引。
使用存储过程已经意味着没有基表访问（所有权链）。
视图有很好的用途，可以避免用户直接访问表，或者屏蔽模式更改，或者提供一些基本的安全性（例如基于 SUSER_SNAME），但不是为了性能或思想
A view is just a macro that expands into an outer query.
If your view contains several joins, then when you join if to other views you suddenly have a 20 or 30 way JOIN when you actually see 3 JOINs in the SQL of the stored procedure. You'll also find that each query is different: why keep joining the same 20 or 30 tables for every query?
Generally, there is no benefit unless the view is indexed/materialised and the optimiser can use it.
Ideas such as having calculations on a single table masked by a view should be in a computed column: why keep calculating it? For a calculation on multiple tables in a view, it should be indexed.
Using a stored procedure already means no base table access (ownership chaining).
There are good uses of views to avoid direct table access by users, or to mask schema changes, or provide some basic security (eg based on SUSER_SNAME), but not for performance or idealogy
不同的数据库优化器以不同的方式优化查询，因此这不是一个简单的答案。但通常添加一个抽象层可以（不是绝对）阻止优化器正确使用索引。
在 Sql Server 中，如果您有一个包含以下调用的 where 子句：
它将使其成为 不可控制的查询，即不使用索引的查询，或者使用次优索引的查询。
我猜想使用视图也会影响 sarg。我会去测试这个（在 Sql Server 中）- 我会在 5 分钟后回来。
编辑
我想答案是“这取决于”，并且您需要打开查询执行计划才能确定，我所做的以下测试表明查询基于以下内容的简单视图之间没有区别一个表并简单地查询基础表。但它需要进一步测试，因为复杂的视图可能会有不同的行为。
Different database optimizers optimize queries in different ways, so its not an easy answer. But generally adding a layer of abstraction can (not definitely) stop the optimizer from using indexes correctly.
In Sql Server if you have a where clause that contains calls like:
it makes it a non-sargable query, ie a query that does not use indexes - or uses them sub-optimally.
I would guess that using a view will also affect the sarg. I'll go and test this (in Sql Server) - I'll be back in 5 minutes.
EDIT
I guess that the answer is 'it depends' and that you need to turn on your query execution plan to be sure, the following test that I did showed no difference between a querying a simple view based on a table and simply querying the underlying table. But it needs further testing as a complex view could act differently.
我在以下两种情况下在存储过程中使用了视图：
1 - 用于多个过程中所需的复杂联接或条件
2 - 替换重命名的表。例如，我有两个表，分别称为“member”和“non_member”。后来我决定将它们合并到“用户”表中。为了避免修改我编写的每个过程，我创建了名为“member”和“non_member”的视图，它们使用 where 子句来适当地过滤“user”表。所有进程都像以前一样运行，没有任何变化。当我有时间时，我可以更改它们以直接访问新表。
I have used views in stored procedures in these two cases:
1 - for complex joins or conditions that are needed in multiple procedures
2 - to substitue for a renamed table. For example I had two tables, called 'member' and 'non_member'. I later decided to combine these into a 'user' table. In order to avoid modifying every proc I had ever written, I created views called 'member' and 'non_member', which used where clauses to filter the 'user' table appropriately. All the procs ran as they used to without changes. I can change them to access the new table directly when I have time.
在 SQL Server 中（至少），我的理解是存储过程在编译时进行优化，因此通常比视图更有效。我不确定，但我怀疑通过针对视图执行 SPRC，您可能会失去通过这种方式获得的任何优化。
进一步说，为什么呢？正如之前的海报所建议的，如果您所包含的一个或多个视图本身由大量联接组成，那么您可能会执行比需要更多的联接，并且这并不明显。
另外，我的理解是，使用视图的主要原因之一是以用户可以使用的格式呈现表数据，同时保护表数据免受无意的更改。由于存储过程中的结果集不受插入和更新的影响，因此这一点没有实际意义。
由于存储过程在设计上接受参数，不允许用户直接与表数据交互，并且可以执行您可能在视图中使用的任何查询逻辑（以及更多），因此在我看来（有一些例外） ）这样做的主要原因是为了使编码更容易，但代价是性能和可维护性的潜在成本（如果有人改变了其中一个视图，而没有意识到你的存储过程依赖于它怎么办？）。
我建议将其全部编码到 SPROC 中，除非有令人信服的理由不这样做。 。 。
In SQL Server (at least), my understanding is that Stored Procedures are optimized at compile time, and therefore are more efficient in general than views. I am not certain, but I suspect that by executing an SPRC against a view, you may lose any optimization gained this way.
Further, why? As a previous poster sugessted, you may be executing more joins than you need to if one or more of the views you are including are themselves comprised of numerous joins, and this won't be obvious.
Also, my understanding is that one of the primary reasons for using views is to present table data in a format the user can consume, while protecting table data against inadvertent changes. Since the result set from an SPROC is not subject to INSERTS and UPDATES, this point is moot.
Since a stored procedure by design accepts parameters, does not allow the user to interact with table data directly, and can execute any query logic you might use in a view (plus a whole lot more), it seems to me that (with some exceptions) the main reason one might do this is to make CODING easier, at the potential cost of performance and maintainability (what if someone changes one of the views, without realizing your SPROC depend on it?).
I recommend coding it all into the SPROC, unless there is a compelling reason to do otherwise . . .