为什么基于关系集的查询比游标更好？ [英] Why are relational set-based queries better than cursors?

问题描述

在像TSQL或PLSQL这样的类中编写数据库查询时，我们经常可以选择使用游标对行进行迭代以完成任务，或者制作一个单一的SQL语句，同时执行相同的作业。

When writing database queries in something like TSQL or PLSQL, we often have a choice of iterating over rows with a cursor to accomplish the task, or crafting a single SQL statement that does the same job all at once.

此外，我们可以选择简单地将大量数据提取回我们的应用程序，然后使用C＃或Java或PHP或其他方式逐行处理。

Also, we have the choice of simply pulling a large set of data back into our application and then processing it row by row, with C# or Java or PHP or whatever.

为什么最好使用基于集合的查询？这个选择背后的理论是什么？什么是基于游标的解决方案及其关系等价物的一个很好的例子？

Why is it better to use set-based queries? What is the theory behind this choice? What is a good example of a cursor-based solution and its relational equivalent?

推荐答案

做工作（这是一个很好的解决方案），还有一些其他很好的理由离开查询在DBMS：

In addition to the above "let the DBMS do the work" (which is a great solution), there are a couple other good reasons to leave the query in the DBMS:

• 它（主观上）更容易阅读。稍后查看代码时，您是否希望尝试使用循环和事物解析复杂的存储过程（或客户端代码），或者您希望查看简洁的SQL语句？


• 它避免了网络往返。为什么要将所有数据都发送给客户端，
您的DBMS和应用服务器需要缓存一些/所有的数据工作。如果你没有无限内存，你可能会出页其他数据;

• 你为什么不会？你买了（或者正在其他地方使用）一个高度可靠，非常快的DBMS。为什么不使用它？

• It's (subjectively) easier to read. When looking at the code later, would you rather try and parse a complex stored procedure (or client-side code) with loops and things, or would you rather look at a concise SQL statement?
• It avoids network round trips. Why shove all that data to the client and then shove more back? Why thrash the network if you don't need to?
• It's wasteful. Your DBMS and app server(s) will need to buffer some/all of that data to work on it. If you don't have infinite memory you'll likely page out other data; why kick out possibly important things from memory to buffer a result set that is mostly useless?
• Why wouldn't you? You bought (or are otherwise using) a highly reliable, very fast DBMS. Why wouldn't you use it?

这篇关于为什么基于关系集的查询比游标更好？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！