连接到非常繁忙的SQL 2000: selectMethod=cursor还是selectMethod=direct？

Question

在试图帮助应用程序开发团队解决SQL 2000服务器上的性能问题(来自不同应用服务器上的一组Java应用程序)的过程中，我运行了一个SQL跟踪，发现对数据库的所有调用都充满了API游标语句(sp_cursorprepexec、sp_cursorfetch、sp_cursorclose)。

看起来他们指定了一些连接字符串属性，强制使用服务器端游标，一次只检索128行数据：(从http://msdn.microsoft.com/en-us/library/Aa172588)

当API游标属性或属性设置为其默认值以外的任何属性或属性时，用于Server的OLE DB提供程序和SQL Server ODBC驱动程序将使用API服务器游标而不是默认结果集。每次调用获取行的API函数都会产生到服务器的往返，以便从API服务器游标中获取行。

UPDATE：所讨论的连接字符串是参数，selectMethod=cursor (它启用了我们前面讨论的服务器端游标)与替代selectMethod=direct。他们一直使用selectMethod=cursor作为所有应用程序的标准连接字符串。

从我的DBA角度来看，这是很烦人的(它把无用的垃圾弄得乱七八糟)，而且(我想推测)正在导致许多额外的应用程序到SQL服务器往返旅行，从而降低了总体性能。

他们显然测试了改变selectMethod=direct (大约60个不同的应用程序连接之一)，但经历了一些问题(我不知道细节)，并担心应用程序中断。

所以，我的问题是：

• 正如我曾经试图论证的那样，使用selectMethod=cursor可以降低应用程序的性能吗？(通过增加已经有很高查询/秒的SQL服务器上所需的往返次数)
• selectMethod=是否是JDBC连接上的应用程序透明设置？如果我们改变它，这会破坏他们的应用程序吗？
• 更广泛地说，您应该在什么时候使用cursor vs direct？

还有交叉张贴到SF。

编辑：收到需要对标题、问题和标签进行重大编辑的实际技术细节。

编辑：添加赏金。还为SF问题添加了大量内容(这个问题关注应用程序行为，SF问题关注SQL性能)。谢谢！！

Answer 1

简而言之，

1. selectMethod=cursor
   • 理论上需要更多服务器端资源而不是selectMethod=direct
   • 只同时将最多批处理大小的记录加载到客户端内存中，从而产生更可预测的客户端内存占用量。

​

1. selectMethod=direct
   • 理论上，与selectMethod=cursor相比，需要更少的服务器端资源。
   • 将整个结果集读入客户端内存(除非驱动程序本机支持异步结果集检索)，然后客户端应用程序才能对其进行迭代；此可以通过：两种方式降低性能。
     1. 如果客户端应用程序是以只遍历结果集的一小部分后停止处理的方式编写的，则会降低大结果集的性能(使用direct，它已经支付了检索数据的成本，它基本上将丢弃数据；对于cursor，浪费仅限于批处理(1行)，早期终止条件可能无论如何都应该在SQL中重新编码，例如SELECT TOP或窗口函数)。
     2. 由于与内存占用增加相关的潜在垃圾收集和/或内存不足问题，导致大结果集的性能下降。

​

​

总而言之，

• 使用selectMethod=cursor可以降低应用性能吗？超过一定的结果集大小，cursor可能仍然更好。关于何时使用其中一种或另一种，请参见下文
• selectMethod=是否是JDBC连接上的应用程序透明设置？？它是透明的，但如果内存使用量显著增长到足以占用客户端系统(相应地说是服务器)或使客户端完全崩溃，它仍然可以破坏它们的应用程序。
• 更普遍地说，什么时候应该使用cursor和direct__？--我个人在处理潜在的大结果集或其他无界结果集时使用cursor。然后，考虑到足够大的批处理大小，对往返开销进行摊销，并且我的客户端内存占用量是可预测的。当我期望的结果集的大小低于我在cursor中使用的批处理大小，或者以某种方式绑定时，或者当内存不是问题时，我就使用cursor。

Answer 2

使用selectMethod=cursor可以防止Server使用并行查询处理，这可能会对性能产生很大影响，例如：

• 你有很多CPU核心(谁没有呢？)
• 通过对表进行分区优化了数据库。
• 您正在运行大量聚合查询(sum()、count()等)

最后，Microsoft 州如下：

(selectMethod=direct)在应用程序处理所有行时提供最快的性能。

您肯定应该试着看看设置selectMethod=direct是否对您有影响。