哪个性能更好：存储过程或使用 dataadapter 执行查询？
我正在重新设计一个迄今为止运行缓慢的 .NET 应用程序。我们的数据库是Oracle，代码是用VB编写的。在编写查询时，我通常将参数传递给构建原始 SQL 的中间层函数。我有一个数据库类，它有一个函数 ExecuteQuery，它接受 SQL 字符串并返回 DataTable。这使用 OleDbDataAdapter 在数据库上运行查询。
我发现一些现有代码将 SQL 和参数发送到存储过程，据我所知，该代码打开查询并将其输出到 SYS_REFCURSOR / DataSet。
我不知道为什么要这样设置，但是有人可以告诉我哪个性能更好吗？或者这样做的优点/缺点？
提前致谢
I am reworking a .NET application that so far has been running slowly. Our databases are Oracle, and the code is written in VB. When writing queries, I typically pass the parameters to a middle tier function which builds the raw SQL. I have a database class that has a function ExecuteQuery which takes in a SQL string and returns a DataTable. This uses an OleDbDataAdapter to run the query on the database.
I found some existing code that sends the SQL and a parameter to a stored procedure which as far as I can tell, opens the query and ouputs it to a SYS_REFCURSOR / DataSet.
I don't know why it's set up this way, but could someone tell me which is better performance-wise? Or the pros/cons to doing it this way?
Thanks in advance
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
与在代码中构建原始查询并执行它相比，存储过程肯定会具有更好的性能，但要认识到的重要一点是，性能差异不会成为性能问题，还有许多其他因素会极大地影响性能不仅仅是将查询更改为存储过程，即使您运行存储过程并使用适配器、数据表、数据集处理结果，您仍然会产生大量性能，特别是如果您传递那些大型对象周围（我见过数据集返回包装在网络中的情况服务调用），因此，不要关注这一点，专注于缓存数据，进行良好的查询，创建适当的索引，尽量减少数据集、数据表的使用，这将比仅仅将查询移至存储过程产生更好的好处
a stored procedure will definitely have better performance over building a raw query in code and executing it, but the important thing to realize is that, that difference in performance won't be your performance issue, there are many other things that will affect performance much more than just changing just query to be a stored procedure, even if you run a stored procedure and process the results using adapters, data tables, data sets, you're still incurring in a lot of performance, specially if you pass those large objects around (I have seen cases where datasets are returned wrapped in web service calls), so, don't focus on that, focus on caching data, having a good query, create the proper indexes, minimize the use of datasets, datatables, that will yield better benefits than just moving queries to stored procedures
存储过程与动态 SQL 具有完全相同的性能。换句话说，其中一种相对于另一种没有性能优势。 （顺便说一句，由于许多其他原因，我非常相信使用存储过程来处理所有事情，但这不是当前的主题）。
出现瓶颈的原因有很多。
其一，如果您实际上是生成选择语句的代码，那么这些语句很可能没有针对应用程序所需的数据进行优化。例如，执行
SELECT *会拉回 50 列，而SELECT ID, Description则仅拉回应用程序中此时所需的两列。在此示例中，必须从磁盘读取、通过网络传输并推送到 Web 服务器内存中的对象中的数据量并不小。这些必须根据具体情况进行评估。
我强烈建议，如果您有一个“缓慢”的应用程序，您需要提高第一件事的性能，那么您应该做的就是分析该应用程序。哪一部分运行缓慢？它可能位于数据库服务器内部，也可能位于中间层，甚至可能是网络带宽或 Web 服务器上的内存/负载限制的函数。哎呀，甚至可能有一个 WAIT 命令潜伏在某个地方，是由一些离开公司的前程序员放置的……
简而言之，此时您完全不知道从哪里开始。因此，查看实际代码还为时过早。分析应用程序并查看哪些地方速度变慢。您可能会发现，只需在数据库服务器中放置更多内存，性能就可以从根本上提高......这是比重写、测试和部署大量代码便宜得多的替代方案。
Stored Procedures vs dynamic SQL have the exact same performance. In other words there is no performance advantage of one over the other. (Incidentally, I am a HUGE believer in using stored procs for everything for a host of other reasons but that's not the topic on hand).
Bottle necks can occur for many reasons.
For one, if you are actually code generating select statements it is highly probable that those statements are very unoptimized for the data the app needs. For example, doing a
SELECT *which pulls 50 columns back versus aSELECT ID, Descriptionwhich just pulls the two you need in your application at that point. In this example, the amount of data that has to be read from disk, transferred over the network wire, and pushed into objects in memory of the web server isn't trivial.These will have to be evaluated on a case by case basis.
I would highly suggest that if you have a "slow" application that you need to improve the performance of the very first thing you ought to do is profile the application. What part of it is running slow? It might be inside the database server, it might be in your middle tier, it may even be a function of your network bandwidth or memory / load limitations on your web server. Heck, there might even be a WAIT command lurking somewhere in there placed by some previous programmer that left the company...
In short, you have at this point absolutely no idea on where to begin. So looking at actual code is premature. Go profile the app and see where things are slowing down. You might find that performance may radically improve simply by putting more memory in the database server.... Which is a much cheaper alternative than rewriting, testing and deploying vast amounts of code.