转帖地址:http://www.cnblogs.com/sufei/archive/2010/04/08/1707377.html
这一节咱们来说说ClR的性能,我们不能只使用它而不去考虑到低 为什么要使用它或是在什么时候应该使用它,像我之前写的函数得到一个字符的长度的方法就没有太大必要了,但如果是像拆分字符这样的方法应该就有必要了,比 如c#里的Split ()方法,在Sql里就没有这样的函数这个时候 我们就可以使用Clr来完成了,
其实实现 的方法是跟上一节SQL Server 2005中的CLR(1) 里的没有任何分别只是把函数的代码换成下面的代码就可以了
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
// 表示注册为Sql中的函数
[Microsoft.SqlServer.Server.SqlFunction]
public static string sqlSplit( string str, int x)
{
// 返回字符串的长度
return str.Split( ' , ' )[x].ToString ().Trim();
}
};
这里是简单的实现,大家可以根据自己的需要来实现
说到他的性能我们先来和T-SQl语言比较一下吧
1. 编程模型
T-Sql
过程代码内部嵌入查询语言
意思就是说,在T-Sql里我们一般是把查询语句写在存储过程里的,这样的效率很高,因为它是直接访问数据库,但是如果出现不同数据库间的移植就很不方便了
CLR
intp-proc ado.net数据访问(这里要导航一个using System.Data.SqlClient;)
注: 相比T-Sql肯定是在执行效率上有些不足了,因为它毕竟是经过一层的封装的,而最后还是通过Ado来执行T-SQl语句,但也不是完全没有用武之地
容易在各层之间移动代码和利用现有技术
注: 如果 我们各层之间的业务需要转换或是移植时就会很方便了,我们只要把业务层的代码部署到数据库,或是把数据的Clr程序调用到业务层,但这是在早期数据库版本 中所不能实现 的,如果是在2000的话我们可能要把大理的存储过程改写,或是把大理的业务层代码改成存储过程
2.性能
T-Sql
数据访问方面性能好
适合编写数据访问密集的代码
CLR
过程代码,计算,统计
用CLR编写计算,统计和逻辑密集的代码
注:T-Sql是直接和数据库打交道的当然从数据访问性能上讲CLR是无法与之相比的,但是有些复杂的逻辑,或是计算,统计之类的T-Sql代码就显示的很辍了,
这个时候 我们就应该使用CLR来实现了.因为CLR是代码托管的方法来实现 的,所以他里面有很多c#的类库,实现一些逻辑就会很方便;
3.执行流程
T-Sql
过程代码SQl语句往返,T-SQl具有优势
有直接的返回结果集的语句,直接返回到客户端像Select
CLR
额外的代码层造成性能低下
SqlPiple对象将结果发送到客户端
注:这里就很明显了,T-Sql是直接往返客户端的所以性能要高的多,而CLR有一是托管代码它有一个中间层在效率上显得低下了;
3.数据导航
T-Sql
通过只进只读 光标实现
可使用更新光标当前位置行来实现
适合于,执行一系列语句(Insert/Delete/update/select)带有几个或不带有返回到客户端的行,并且不导航产生的行
CLR
使用 SqlDataReader实现导航数据
适合每行都有复杂的处理
注:SqlDataReader微软话说对这块的更新不是很明示,SqlDataReader 是不能更新当前行的,是一种只读的方法,如果没有复杂的业务处理我们不建议使用这样的方法,一般的运算可以使用 游标 的方法来实现游标 是数据库运算里常用的方法,我建议大家一定要运用熟练。这里 有上小例子可以参考下;
简单的几个对比相信大家应该会有些了解了,下面看一下CLR与扩展存储过程(xp---Extended Stored Procedure),大家不要误会这里说的Xp可不是操作系统,是说的扩展存储过程;
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 的原始性能优势。
下面我以一个实例的方式来说明它的用法,我们来处理一下一个字符串是否是数字字符;
我们还是打开上次的项目,右击项目---添加--新建项
我们添加一个Xp,名称为RegexNumber
生成的代码如下
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void RegexNumber()
{
// 在此处放置代码
}
};
我们做一下修改就可以变成我们想要的代码了,修改完成的代码如下
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static int RegexNumber( string inputData)
{
// 存储过程只能返回int类型在这里
if (IsNumber(inputData))
{
return 1 ;
}
else
{
return 0 ;
}
}
/// <summary>
/// 是否数字字符串
/// </summary>
/// <param name="inputData"> 输入字符串 </param>
/// <returns></returns>
public static bool IsNumber( string inputData)
{
Regex RegNumber = new Regex( " ^[0-9]+$ " );
Match m = RegNumber.Match(inputData);
return m.Success;
}
};
我们现在右击项目选择部署项目,然后打开数据库查看一下吧
当然执行这个存储过程跟我们平时执行过程 的方法是一样的,
下面看看执行结果吧
大家还可定义一下其它 的身份证码号,邮件等,在这里只是做一下存储的使得方法;对于程序本身是否有介值不做审议;
接下来简单介绍一下关于Clr其它方面的东西
代码位置:数据库与中间层
通过在数据库中提供丰富的编程模型,CLR 集成提供了将逻辑从其他层移动到数据库层的选择。然而,这显然并不意味着所有或大部分逻辑应该移到数据库中。
将逻辑移到数据库层可以减少网络中的数据流量,但是增加了服务器上宝贵的 CPU 资源的负荷。因此,在应用程序中做出代码放置的决定之前,要慎重权衡。以下注意事项适用于将数据库层作为首选的代码位置:
• 数据验证:在数据层进行数据验证的逻辑可以更好地将数据和逻辑封装在一起。这样避免了在不同数据接触点(如:后端处理、批量上载和来自中间层的数据更新等)中重复验证逻辑。
• 减少网络流量:对于需要处理大量的数据而产生很少的数据的数据处理任务(如数据分析应用程序中的需求预测、基于需求预测的生产安排等)来说,将逻辑放在数据库层中是合适的。
注即使在引入 CLR 支持之前,上面的注意事项也是有效的。数据库层中的 CLR 支持意味着编程语言的选择没有妨碍代码位置的正确选择。
示例:生产安排
生产安排是制造企业的常见任务。在高层次上,它包括制订何时生产多少单位数量的产品的计划,以便能够满足需求、最大程度的降低库存成本,同时将生产成本降到最低。有几个算法将需求预测、库存成本和生产线安装成本作为输入,而将制造策略作为输出。
假定将来的需求预测存储在 SQL Server 表中,则此类算法的实现有以下特征:
1.使用大量的数据作为输入(如需求预测)。
2.产生小结果(如在特定的日期内生产的单位数量)。
3.需要相当多的计算以便从输入中派生输出。
在中间层实现这样的算法是可行的,但是在数据库外移动输入集方面有性能损失。在 T-SQL 中将其实现为存储过程也是可行的,但是因为需要复杂的计算,性能损失就显现出来了。性能特征将随着实际的数据量和算法的复杂性的不同而不同。
为了验证 CLR 集成是否适合于这样的情况,我们举一个特定的生产安排算法的示例 - Wagner-Whitin 算法的动态编程实现。正如所预料的,CLR 集成优于 T-SQL。对于这种情况,使用托管代码还有其他好处。这种算法的实现需要使用大量的一维和多维数组、数据结构,而这些在 T-SQL 中是不可用的。总之,CLR 集成的性能要优于 T-SQL 实现几个数量级。
处理常见数据库编程任务和问题
在高层次上对基于 CLR 的编程与 T-SQL、中间层和扩展存储过程 (XP) 进行了比较。在这一节中,我们将考虑数据库应用程序开发人员经常遇到的一些编程任务和模型,并且讨论如何使用 CLR(以及在一些情况下如何不使用)进行处理。
使用 Framework 库进行数据验证
SQL Server 2005 中的 CLR 集成允许用户利用 .NET Framework 类库提供的丰富功能来解决其数据库编程问题。
常规表达式的使用可以很好地说明 CLR 集成如何增强了验证和过滤功能。在处理数据库中存储的文本数据方面,常规表达式提供的模式匹配功能比通过 T-SQL 查询语言中的 LIKE 运算符可用的模式匹配功能多。考虑以下 C# 代码,它只是 System.Text.RegularExpressions 命名空间中的 RegEx 类的一个简单包装:
这些大家可以参考官方网站http://www.microsoft.com/china/msdn/library/data/sqlserver/sqlclrguidance.mspx?mfr=true
因为这上面都 有实例我就不再班门弄斧了,呵呵