SQL 游标...您会捍卫任何用例吗? [英] SQL Cursors...Any use cases you would defend?

问题描述

我先走了.

我 100% 加入了集运营.但是当设置逻辑时会发生什么在整个所需输入域上导致如此大的检索，以致查询显着减慢，爬行，或者基本上需要无限时间?

I'm 100% in the set-operations camp. But what happens when the set logic on the entire desired input domain leads to a such a large retrieval that the query slows down significantly, comes to a crawl, or basically takes infinite time?

在这种情况下，我将使用可能最多有几十行(而不是我的目标是数百万行)的很小的游标(或 while 循环).因此，我仍在(分区子)集合中工作，但我的检索运行得更快.

That's one case where I'll use a itty-bitty cursor (or a while loop) of perhaps most dozens of rows (as opposed to the millions I'm targeting). Thus, I'm still working in (partitioned sub) sets, but my retrieval runs faster.

当然，更快的解决方案是从外部并行调用分区输入域，但这会引入与外部系统的交互，并且当可以通过串行循环实现足够好"的速度时，可能不值得(尤其是在开发期间).

Of course, an even faster solution would be to call the partioned input domains in parallel from outside, but that introduces an interaction will an external system, and when "good enough" speed can be achieved by looping in serial, just may not be worth it (epecially during development).

推荐答案

当然，在很多地方游标可能比基于集合的操作更好.

Sure, there are a number of places where cursors might be better than set-based operations.

一种是如果您要更新表中的大量数据(例如 SQL 代理作业以按计划预先计算数据)，那么您可以使用游标在多个小集合中而不是一个大集合中进行更新减少并发锁的数量，从而减少与访问数据的其他进程发生锁争用和/或死锁的机会.

One is if you're updating a lot of data in a table (for example a SQL Agent job to pre-compute data on a schedule) then you might use cursors to do it in multiple small sets rather than one large one to reduce the amount of concurrent locking and thus reduce the chance of lock contention and/or deadlocks with other processes accessing the data.

另一种情况是，如果您想使用 sp_getapplock 存储过程获取应用程序级锁，这在您想确保由多个进程轮询的行只检索一次时非常有用(示例).

Another is if you want to take application-level locks using the sp_getapplock stored procedure, which is useful when you want to ensure rows that are being polled for by multiple processes are retrieved exactly once (example here).

不过，总的来说，我同意最好尽可能开始使用基于集合的操作，并且仅在出于功能或性能原因需要时才移动到游标(有证据支持后者).

In general though, I'd agree that it's best to start using set based operations if possible, and only move to cursors if required either for functionality or performance reasons (with evidence to back the latter up).

这篇关于SQL 游标...您会捍卫任何用例吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！