查看和存储过程 [英] View and stored procedure

问题描述

如果有一个带有单个SELECT查询的视图和带有相同SELECT查询的SP在ASP网页上显示将要使用的记录以及为什么会对性能产生什么影响？



我的尝试：



.............. ........................................

.. .........

If there is a view with a single SELECT query and SP with the same SELECT query to display a record on ASP web page which would be used and why- what is the effect on performance?

What I have tried:

......................................................
...........

推荐答案

两种情况在不同的情况下都会发挥不同的作用。

了解更多信息请点击以下链接



SQL-Server性能：什么是更快，存储过程或视图？ - 堆栈溢出 [ ^ ]



比较SQL Server中索引视图和存储过程的性能 - 堆栈溢出 [ ^ ]



视图和存储过程之间的性能差异 [ ^ ]



存储过程不会提高性能 [ ^ ]





问候，

AARIF SHAIKH

Both are play different roll in a different situation.
for more information please follow below links

SQL-Server Performance: What is faster, a stored procedure or a view? - Stack Overflow[^]

Comparing The Performance Of Indexed Views And Stored Procedures In SQL Server - Stack Overflow[^]

Performance difference between view and stored procedure[^]

Stored Procedures DO NOT increase performance[^]


Regards,
AARIF SHAIKH

它们确实不同，用途不同。视图只不过是SQL通常代表一个复杂的演示场景，如搜索屏幕，报表，导出等。



但是存储过程就像二进制文件可能会执行复杂的业务用例。像复杂的逆转场景，ETL过程，数据存档等等。



这个主题还有另一种观点，即提供后期制作支持。在支持模型中，我们需要在最短时间内提供修复。存储过程和视图都很方便，因为它们不直接与业务层耦合。您可以独立更改存储过程并直接向生产提供修复，而不会破坏任何图层，也不需要完整的应用程序烟雾测试。



在您的情况下使用存储过程就像用坦克杀死一个小虫。尝试使事情变得简单。

They are indeed different and serve different purpose. Views are nothing but SQL usually represent One complex presentation scenario like search screens, reports, exports, etc.

However stored procedures are like binaries that may execute complex business use cases. Like complex reversal scenarios, ETL process, Data Archiving and many more.

There is another view to this subject which is providing post production support. In support model we need to provide fixes in minimum timeframe. Stored procedures and views both are handy as they do not get directly coupled with the business layer. You can independently alter stored procedure and provide the fix directly to production without breaking any layers and does not require full application smoke testing.

In your case using stored procedure is like killing a bug with a Tank. Try making things simple, the way they are.

View表示数据库中的虚拟表。您可以在视图中实现连接多个表以获得结果。



存储过程是执行大量SQL语句的数据库，如更新和插入数据，或返回单个/多个值或数据集。



在哪种情况下View / SP需要使用：



查看：如果要从表中获取记录而不使用任何动态过滤器，请使用View。

例如，根据交易从表中获取前10个记录 - 这里我们不需要任何参数



SP ：

1.如果你想声明局部变量并想要实现自定义，那么使用SP

2.如果你想实现transcation然后使用SP

3.您可以在SP内执行其他SP或功能



通过以下链接： sql - 视图和存储过程之间是否有任何性能差异 - Stack Overflow [ ^ ]



根据您的需要和要求选择。

View represents a virtual table in Database. You can implement join multiple tables in a view to get result.

A stored procedure is database which executes bunch of SQL statements like updating and inserting data, or returning single/multiple values or data sets.

In which scenario View/SP need to use:

View: If you want to fetch records from table(s) without any dynamic filter then use View.
For instance, to fetch top 10 records from table based on transaction - here we do not need any parameter

SP:
1. If you want to declare local variable and want to implement custom then use SP
2. If you want to implement transcation then use SP
3. You can execute other SP or function inside SP

Go through below link: sql - Is there any performance difference between view and stored procedures - Stack Overflow[^]

Choose as per your need and requirement.

这篇关于查看和存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！