在使用TSQL或者PLSQL之类的代码编写数据库查询时，我们通常可以选择使用游标遍历行以完成任务，或者设计一条SQL语句一次完成全部工作。

同样，我们可以选择简单地将大量数据返回到我们的应用程序中，然后使用Cor Java或者PHP或者其他方式逐行处理它。

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

解决方案

回答

我知道的主要原因是引擎可以通过在多个线程中运行基于集合的操作来优化它们。例如，考虑一个快速排序，我们可以将要排序的列表分成多个"块"，然后在各自的线程中分别对它们进行排序。 SQL引擎可以在一个基于集合的查询中对大量数据执行类似的操作。

当我们执行基于游标的操作时，引擎只能按顺序运行，并且该操作必须是单线程的。

回答

首选在查询中进行工作的背后想法是，数据库引擎可以通过重新构造它来进行优化。这就是为什么要对查询运行EXPLAIN以便查看数据库实际在做什么的原因。 (例如利用索引，表大小，有时甚至还了解列中值的分布。)

就是说，为了在实际情况下获得良好的性能，我们可能必须弯腰或者破坏规则。

哦，另一个原因可能是约束：如果在所有更新之后都检查了约束，那么将唯一列增加一个就可以了，但是如果一个接一个地进行，则会产生冲突。

回答

一次完成设置
游标与游标的行集一样多的操作

回答

基于集合的查询(通常)更快，因为：

• 他们有更多信息供查询优化器优化
• 他们可以批量从磁盘读取
• 回滚，事务日志等涉及的日志较少。
• 采取较少的锁，这减少了开销
• 基于集合的逻辑是RDBMS的重点，因此已经对其进行了大量优化(通常以牺牲程序性能为代价)

但是，将数据拉到中间层进行处理可能会很有用，因为它消除了数据库服务器的处理开销(这是最难扩展的事情，并且通常还会做其他事情)。同样，我们通常在中间层没有相同的间接费用(或者收益)。诸如事务日志记录，内置锁定和阻止之类的东西，有时这些是必要和有用的，而其他时候它们只是浪费资源。

具有过程逻辑和基于集合的示例(T-SQL)的简单游标，它将基于电话交换机分配区号：

--Cursor
DECLARE @phoneNumber char(7)
DECLARE c CURSOR LOCAL FAST_FORWARD FOR
   SELECT PhoneNumber FROM Customer WHERE AreaCode IS NULL
OPEN c
FETCH NEXT FROM c INTO @phoneNumber
WHILE @@FETCH_STATUS = 0 BEGIN
   DECLARE @exchange char(3), @areaCode char(3)
   SELECT @exchange = LEFT(@phoneNumber, 3)

   SELECT @areaCode = AreaCode 
   FROM AreaCode_Exchange 
   WHERE Exchange = @exchange

   IF @areaCode IS NOT NULL BEGIN
       UPDATE Customer SET AreaCode = @areaCode
       WHERE CURRENT OF c
   END
   FETCH NEXT FROM c INTO @phoneNumber
END
CLOSE c
DEALLOCATE c
END

--Set
UPDATE Customer SET
    AreaCode = AreaCode_Exchange.AreaCode
FROM Customer
JOIN AreaCode_Exchange ON
    LEFT(Customer.PhoneNumber, 3) = AreaCode_Exchange.Exchange
WHERE
    Customer.AreaCode IS NULL

回答

我认为，真正的答案是，像编程中的所有方法一样，它取决于哪种方法更好。通常，基于集合的语言将更加高效，因为这正是它的初衷。游标在两个地方占有优势：

• 我们正在数据库中更新一个较大的数据集，在该数据库中，锁定行是不可接受的(可能在生产时间内)。基于集合的更新可能会将表锁定几秒钟(或者几分钟)，而游标(如果写得正确)则不会。游标可以遍历行，一次更新一个行，而我们不必担心会影响其他任何行。
• 使用SQL的优点是，大多数情况下，优化工作的大部分由数据库引擎处理。使用企业级的db引擎，设计人员竭尽全力确保系统有效地处理数据。缺点是SQL是一种基于集合的语言。我们必须能够定义一组数据才能使用它。尽管这听起来很简单，但在某些情况下却并非如此。查询可能非常复杂，以至于引擎中的内部优化器无法有效地创建执行路径，无法猜测会发生什么...我们具有32个处理器的超级强大的框使用单个线程来执行查询，因为它不知道如何执行其他操作，因此浪费了数据库服务器上的处理器时间，而这通常是多个应用程序服务器中只有一个服务器的原因(所以回到原因1，我们会遇到资源争用以及其他需要在数据库服务器上运行的事情)。使用基于行的语言(C＃，PHP，JAVA等)，我们可以更好地控制发生的情况。我们可以检索数据集并强制其执行所需的方式。 (将数据分开以在多个线程上运行等)。在大多数情况下，它仍然无法像在数据库引擎上运行那样高效，因为它仍然必须访问引擎来更新行，但是当我们必须执行1000多次计算来更新行时(并假设我们有一百万行)，那么数据库服务器可能会开始出现问题。

回答

除了上面的"让DBMS进行工作"(这是一个很好的解决方案)外，还有一些其他充分的理由将查询保留在DBMS中：

• (主观上)更容易阅读。稍后查看代码时，我们是希望尝试使用循环和事物来解析复杂的存储过程(或者客户端代码)，还是希望查看简洁的SQL语句？
• 它避免了网络往返。为什么将所有数据推到客户端，然后再推回去呢？如果不需要，为什么还要破坏网络？
• 这很浪费。DBMS和应用服务器将需要缓冲部分/全部数据以对其进行处理。如果我们没有无限的内存，则可能会调出其他数据。为什么从内存中踢出可能重要的东西来缓冲几乎没有用的结果集？
• 你为什么不呢？我们购买(或者正在使用)高度可靠，非常快速的DBMS。你为什么不使用它？

回答

我们想要一些真实的例子。我公司的光标需要40分钟才能处理30,000条记录(有时我需要更新200,000条记录)。在没有光标的情况下，花了45秒才能完成相同的任务。在另一种情况下，我删除了一个游标，并将处理时间从超过24小时发送到不到一分钟。一个是使用values子句而不是select的插入，另一个是使用变量而不是联接的更新。一个好的经验法则是，如果它是插入，更新或者删除，则应寻找一种基于集合的方式来执行任务。

游标有其用途(或者代码不是一开始就使用的)，但是在查询关系数据库时(Oracle已被优化为使用它们)，它们应该非常少见。他们可以更快的一个地方是在根据先前记录的值(运行总计)进行计算时。甚至应该测试。

使用游标的另一个有限情况是进行一些批处理。如果我们尝试以基于集合的方式一次执行太多操作，则可以将该表锁定给其他用户。如果我们有一个真正大的集合，则最好将其分解为较小的，基于集合的插入，更新或者删除，这些插入，更新或者删除不会将锁保持太长时间，然后使用光标遍历这些集合。

游标的第三种用途是通过一组输入值来运行系统存储的proc。由于这仅限于通常很小的一组，而且任何人都不应干扰系统进程，因此对于管理员而言，这是可以接受的事情。我不建议对用户创建的存储过程进行相同的操作，以便处理大量的批处理并重复使用代码。最好编写一个基于集合的版本，因为在大多数情况下性能应胜过代码重用，因此它的性能更好。

回答

我认为归结为使用数据库是为了使用而设计的。关系数据库服务器经过专门开发和优化，可以最好地回答集合逻辑中表达的问题。

从功能上来说，游标的惩罚因产品而异。一些(大多数？)rdbmss至少部分构建在isam引擎之上。如果问题合适，并且单板足够薄，则实际上使用游标可能同样有效。但是，就dbms品牌而言，这是我们应该首先熟悉的事情之一。

回答

如前所述，该数据库针对设置操作进行了优化。从字面上看，工程师坐了很长时间，调试/调整了该数据库。我们优化它们的机会很小。如果我们要处理一组数据，则可以使用各种有趣的技巧，例如批处理磁盘读写在一起，缓存，多线程。另外，某些操作的开销成本很高，但是如果我们一次对一堆数据进行操作，则每条数据的成本很低。如果我们一次只工作一行，那么很多这些方法和操作就不会发生。

例如，仅查看数据库的连接方式。通过查看解释计划，我们可以看到几种进行联接的方法。最有可能使用游标在一个表中一行一行地走，然后从另一张表中选择所需的值。基本上，这就像一个嵌套循环，只是没有紧密的循环(它很可能被编译成机器语言并进行了超级优化)。 SQL Server本身具有多种连接方式。如果对行进行了排序，它将使用某种类型的合并算法；如果一个表较小，则可能会将一个表转换为哈希查找表，并通过从一个表到查找表执行O(1)查找来进行联接。许多DBMS拥有许多联接策略，它们会击败我们从游标中的一个表中查找值。

只要看一下创建哈希查找表的示例。如果要连接两个长度为n的表和一个长度为m的表(其中m是较小的表)，则构建表可能是m个操作。每次查找应为恒定时间，因此为n次操作。因此，基本上，哈希联接的效率约为m(设置)+ n(查找)。如果我们自己进行操作并且假设没有查找/索引，那么对于n行中的每行，我们将必须搜索m条记录(平均而言，它等于m / 2条搜索)。因此，基本上，操作级别从m + n(一次加入一堆记录)到m * n / 2(通过游标进行查找)。而且，操作是简化的。根据游标的类型，获取游标的每一行可能与从第一张表进行另一选择相同。

锁也会杀死你。如果表上有游标，则我们将锁定行(在SQL Server中，这对于静态游标和forward_only游标而言不太严重...但是我看到的大多数游标代码只是打开了一个游标而未指定任何这些选项)。如果我们在一组中执行该操作，这些行仍将被锁定，但时间较短。优化器还可以看到我们在做什么，并且可以决定锁定整个表而不是一堆行或者页面会更有效。但是，如果我们逐行进行，则优化器毫无头绪。

另一件事是，我听说在Oracle的情况下，它经过超级优化可以执行游标操作，因此与SQL Server中的Oracle游标相比，基于集合的操作与游标的罚款相差无几。我不是Oracle专家，所以不能肯定地说。但是有多个Oracle人士告诉我，游标在Oracle中效率更高。因此，如果我们为Oracle牺牲了长子，则可能不必担心游标，请咨询当地的高薪Oracle DBA ：)

回答

真正的答案是去获得E.F. Codd的书之一，并复习关系代数。然后获得有关Big O符号的好书。经过近20年的IT工作，这就是IMHO，这是现代MIS或者CS学位的重大悲剧之一：很少实际研究计算。我们知道..."计算机"的"计算"部分吗？结构化查询语言(及其所有超集)只是关系代数的一种实际应用。是的，RDBMS已经优化了内存管理和读/写，但是对于过程语言也可以这样说。在我阅读本文时，最初的问题不是关于IDE(即软件)，而是关于一种计算方法与另一种计算方法的效率。

即使快速熟悉Big O表示法，也将开始阐明为什么在处理数据集时，迭代要比声明性语句昂贵。

回答

简而言之，在大多数情况下，让数据库为我们完成任务更快/更容易。

数据库的生命周期目的是以设置的格式存储/检索/处理数据，并且要真正快。VB.NET/ASP.NET代码可能不如专用数据库引擎那么快。充分利用资源是明智的选择。