使用 SQL Server 2005中的 CLR 集成

  现在让我们从编程模型和性能这两个方面，看看如何用 CLR 来编写访问数据库的业务逻辑。

编程模型

  使用 T-SQL，只是在过程代码内部嵌入查询语言语句 (SELECT/INSERT/UPDATE/ DELETE)。通过托管代码，可以使用 SQL Server 托管提供程序来实现 Microsoft ADO.NET 数据访问 API（也称为 in-proc ADO.NET）。使用这种方法，可以将查询语言语句（SELECT 和 DML 语句）作为动态字符串嵌入，并且将其作为参数传递给 ADO.NET API。与 T-SQL 的静态方法相比，过程代码内嵌入的 SQL 语句的基于动态 API 的特性是它们在编程模型上的主要区别。不利的是，in-proc ADO.NET 模型会产生比 T-SQL 更冗长的代码。此外，因为 SQL 语句是动态字符串，所以在执行之前不在语法或语义上进行编译和验证。有利的是，带有 ADO.NET 的数据库编程模型与客户端或中间层中使用的模型相似，因而更容易在各层之间移动代码和利用现有的技术。

  此外，在基于 T-SQL 和基于 CLR 的编程模型中使用的都是同一 SQL 查询语言，不同之处在于过程部分，注意到这一点是非常重要的。

性能

  正如已经提到的，在谈及过程代码、计算等方面时，与 T-SQL 相比，托管代码在性能方面具有决定性的优势。然而，对于数据访问方面，T-SQL 在性能方面通常会更好。因此，通用规则是用 CLR 编写计算和逻辑密集的代码要比数据访问密集的代码好。不过这值得更详细地考虑。

  让我们看看数据访问编程中的一些典型的基元和模式，以及在这些情况下如何使用 ADO.NET 进行 T-SQL 和托管编程。 

  将结果发送到客户端

  这包括将一组行发送到客户端，而没有在服务器中“消费”它们（即没有在例程内导航行）。使用 T-SQL，只需在 T-SQL proc 中嵌入一个 SELECT 语句就可以将 SELECT 产生的行发送到客户端。通过托管代码，可以使用 SqlPipe 对象将结果发送到客户端。T-SQL 和 in-proc ADO.NET 平台在这种情况下的作用是一样的。

  提交 SQL 语句

  这包括来自过程代码的 SQL 语句的执行往返。在这种情况下，T-SQL 具有很大的优势（比 in-proc ADO.NET 快两倍多）。

  此处需要重点注意的是，之所以在 CLR 中出现性能降低，是因为增加了额外的代码层，包括将来自托管代码的 T-SQL 语句提交给原生 SQL 代码。在将该语句提交给查询处理器之后，基于语句源（T-SQL 或 in-proc ADO.NET）的性能方面就没有什么不同了。

  注：典型的数据访问密集的存储过程可能涉及提交一系列的 SQL 语句。如果 SQL 语句简单，并且不需要花费大量的时间执行，则来自托管代码的调用开销可能占用大部分执行时间，这样的过程用 T-SQL 编写将执行得更好。

  只进、只读行导航

  这包括以只进、只读方式一次导航一个由 SQL 语句产生的行。在 T-SQL 中，这是通过只进、只读光标实现的。在 CLR 中，这是通过 SqlDataReader 实现的。通常，每一条语句都涉及一些处理。如果忽略了与每行相关联的处理，则导航行在 CLR 中就比在 T-SQL 光标中稍慢。然而，如果您关心为每行执行的处理，则 CLR 会更有优势，因为 CLR 在这种处理上比 T-SQL 做得好。

  带有更新的行导航

  如果需要根据光标中的当前位置更新行，则没有相关的性能比较，因为 in-proc ADO.NET 不支持此功能，而应该通过 T-SQL 可更新光标来进行此操作。

  注 在任何可能的情况下，最好使用 UPDATE 语句来批量更新行，只有在这样的修改无法用单一的 UPDATE 语句进行表示时，才应使用基于光标导航的 UPDATE。

  以下示例说明在特定情况下如何确定 T-SQL 和 CLR 将执行：

1.

  考虑这样一个过程，它执行一系列（或在最简单的情况下仅一个）INSERT/UPDATE/DELETE/SELECT 语句，带有几个或者不带返回到客户端的行，并且不导航 SELECT 产生的行。如果将这样的过程编写成 T-SQL 过程可能执行得更好。

2.

  考虑这样一个过程，它执行单一的 SELECT 语句，并且使用存储过程内的行，方法是，一次导航一行并进行一些不涉及对每行进行更多的数据访问的处理。这个过程可能在带有 in-proc ADO.NET 的 CLR 中执行得更好，特别是如果每行都有一些大量处理的开销时（因为这样的操作在 CLR 中比在 T-SQL 中更加高效）。

  下面是一些简单的指导原则，可以用来在 CLR 和 T-SQL 之间进行选择： 

  尽可能使用带有 T-SQL SELECT、INSERT、UPDATE 和 DELETE 语句的基于集的处理。只有在无法使用基于集的 DML 语句之一表示逻辑时，才应该使用过程和基于行的处理。 

  如果过程仅仅是一个通过封装基本 INSERT/UPDATE/DELETE/SELECT 操作访问基表的包装，则应该用 T-SQL 进行编写。 

  如果过程主要包括结果集中的只进、只读行导航，以及一些涉及每行的处理，则用 CLR 编写可能更有效。 

  如果过程包括大量的数据访问以及计算和逻辑，则可以考虑将过程代码分隔为 CLR 来调用 T-SQL 过程，以进行大部分的数据访问（反之亦然）。另一个替代方法是，使用单一的 T-SQL 批处理，它包括从托管代码执行一次的一组查询，以减少从托管代码提交 T-SQL 语句的往返次数。

  后面的部分将更深入地讨论在处理结果集时何时及如何适当地使用 T-SQL 和 CLR。
  CLR 与 XP

  在 SQL Server 以前的版本中，扩展存储过程 (XP) 为数据库程序开发人员提供了唯一可用的机制来编写服务器端逻辑，这要么难于表示，要么不可能用 T-SQL 编写。CLR 集成提供了一种更健壮的替代方法来编写这种存储过程。此外，使用 CLR 集成，过去以存储过程形式编写的逻辑通常可以更好地表示为表值函数，因为它们允许它们允许将该函数构造的结果放在 SELECT 语句中进行查询（通过将这些结果嵌入到 FROM 子句中）。

  以下是使用 CLR 过程或函数与 XP 相比的优势： 


  粒度控制：很少可以控制 XP 能做什么或者不能做什么。使用代码访问安全模型，SQL Server 管理员可以分配三种权限之一：SAFE、EXTERNAL_ACCESS 或 UNSAFE，从而对托管代码允许进行的操作集进行不同程序的控制。 

  可靠性：托管代码（特别是在 SAFE 和 EXTERNAL_ACCESS 权限集中）提供了比 XP 更安全、更可靠的编程模型。可验证的托管代码确保了所有对对象的访问都是通过强类型化的接口实现的，从而降低了程序访问或破坏属于 SQL Server 的内存缓冲的可能性。 

  数据访问：使用 XP£¬编程人员必须向后显式连接到数据库（称为回环），以访问本地 SQL Server 数据库。而且，必须将此回环连接显式绑定到原来的会话事务上下文，以确保 XP 可以参与到调用它的同一个事务中。通过托管代码，可以使用更自然和更有效的编程模型来访问本地数据，这些模型利用当前的连接和事务上下文。 

  性能：System.Data.SqlServer API 允许托管过程将结果集发送回客户端，其性能比 XP 使用的开放式数据服务 (ODS) API 更好。此外，System.Data.SqlServer API 支持新的数据类型（如 SQL Server 2005 中引入的 XML、(n)varchar(max)、varbinary(max)），而没有扩展 ODS API 来支持这些新的数据类型。 

  可伸缩性：通过托管代码，SQL Server 可以管理资源（如内存、线程和同步）的使用，因为公开这些资源的托管 API 是在 SQL Server 资源管理器上实现的。相反，SQL Server 不能查看或控制 XP 的资源使用情况。举例来说，如果 XP 消耗了太多的 CPU 或内存资源，就没有办法使用 SQL Server 来检测或控制。然而，通过托管代码，SQL Server 可以检测到特定线程在一段很长的时间内一直没有退出，然后就强制该任务退出，这样其他工作可以按计划进行。因此，使用托管代码提供了更好的可伸缩性和健壮性。

  正如上面所提到的，在数据访问和发送结果集给客户端方面，CLR 过程比 XP 做得更好。对于不包括数据访问和发送结果的代码，比较 XP 和托管代码的性能就是比较托管代码和原生代码的性能。一般来说，在这些情况下托管代码比不上原生代码的性能。而且，当在 SQL Server 内运行时，从托管代码到原生代码的事务处理过程有额外的开销，因为在移动到原生代码和从原生代码移回时，SQL Server 需要对特定于线程的设置进行额外的登记-保留。因此，对于在托管代码和原生代码之间有频繁的事务处理的情况，XP 大大地胜过在 SQL Server 内部运行的托管代码。

  对于大多数扩展过程，如果考虑数据访问和结果发送的可伸缩性、可靠性和性能优势，CLR 过程提供了更好的替代方法。对于性能主要是由处理（数据访问和结果发送之外的）和频繁的托管-原生转换决定的情况，应该权衡 CLR 的可伸缩性和可靠性的优势与 XP 的原始性能优势。

 四、代码位置：数据库与中间层

  通过在数据库中提供丰富的编程模型，CLR 集成提供了将逻辑从其他层移动到数据库层的选择。然而，这显然并不意味着所有或大部分逻辑应该移到数据库中。

  将逻辑移到数据库层可以减少网络中的数据流量，但是增加了服务器上宝贵的 CPU 资源的负荷。因此，在应用程序中做出代码放置的决定之前，要慎重权衡。以下注意事项适用于将数据库层作为首选的代码位置： 

   数据验证：在数据层进行数据验证的逻辑可以更好地将数据和逻辑封装在一起。这样避免了在不同数据接触点（如：后端处理、批量上载和来自中间层的数据更新等）中重复验证逻辑。 

  减少网络流量：对于需要处理大量的数据而产生很少的数据的数据处理任务（如数据分析应用程序中的需求预测、基于需求预测的生产安排等）来说，将逻辑放在数据库层中是合适的。

  注即使在引入 CLR 支持之前，上面的注意事项也是有效的。数据库层中的 CLR 支持意味着编程语言的选择没有妨碍代码位置的正确选择。

  示例：生产安排

  生产安排是制造企业的常见任务。在高层次上，它包括制订何时生产多少单位数量的产品的计划，以便能够满足需求、最大程度的降低库存成本，同时将生产成本降到最低。有几个算法将需求预测、库存成本和生产线安装成本作为输入，而将制造策略作为输出。

  假定将来的需求预测存储在 SQL Server 表中，则此类算法的实现有以下特征：

1.


  使用大量的数据作为输入（如需求预测）。

2.


  产生小结果（如在特定的日期内生产的单位数量）。

3.


  需要相当多的计算以便从输入中派生输出。

  在中间层实现这样的算法是可行的，但是在数据库外移动输入集方面有性能损失。在 T-SQL 中将其实现为存储过程也是可行的，但是因为需要复杂的计算，性能损失就显现出来了。性能特征将随着实际的数据量和算法的复杂性的不同而不同。

  为了验证 CLR 集成是否适合于这样的情况，我们举一个特定的生产安排算法的示例 － Wagner-Whitin 算法的动态编程实现。正如所预料的，CLR 集成优于 T-SQL。对于这种情况，使用托管代码还有其他好处。这种算法的实现需要使用大量的一维和多维数组、数据结构，而这些在 T-SQL 中是不可用的。总之，CLR 集成的性能要优于 T-SQL 实现几个数量级。

  假定以下简单的数据库架构跟踪可以生产的产品列表。

下表存储了每周每个产品的需求预测信息。

  给定一组产品，它们的库存和启动成本以及未来需求预测，我们创建了接受如下输入参数的存储过程：1)制订生产进度表的日期，2)按进度表生产所需要的周数。

存储过程返回带有下表中的架构的行集。