CLR vs T-SQL vs stored procedure
本来复习做过的case是应该当天完成的任务,不过最近忙得够呛。。。当case量开始上去的现在,看来要好好学习下如何分清任务的先后主次。
SQL Server 2005(包括08)引入了CLR编程模型,使用普通编程语言(C#, VB.NET, C++)来写SQL函数触发器等。
CLR介绍:
1. 在数据库中注册和运行托管代码:
编写assembly,使用CREATE ASSEMBLY来放到数据库中。最终CLR函数能被T-SQL查询调用,CLR过程也能被客户端调用。
2. 使用VS来Building, Deploying, and Debugging
有特定模板来新建项目。
到底选择CLR,T-SQL还是stored procedure呢?
A. CLR vs. Transact-SQL
T-SQL包含了数据操作和数据定义特征。数据操作包括说明式查询语言(SELECT/INSERT/UPDATE/DELETE)和过程语言(WHILE, assignment, triggers, cursors等等)。CLR提供了过程语言的替代性方案。
能用说明式查询语言就要尽量用,因为容易优化+能大批量操作。只有要表达的逻辑不能用说明式查询语言来完成了才用CLR。(T-SQL还有很多新增特性,例如递归查询,RANK和ROW_NUMBER等分析性函数,EXCEPT, INTERSECT, APPLY, PIVOT和UNPIVOT 等关系型操作。这些都最好用T-SQL来执行)
那么CLR可以在什么时候用呢?
1. 对每行的数据进行复杂计算,如下范例:
SELECT <complex-calculation>(<column-name>,...)
FROM <table>
WHERE <complex-calculation>(<column-name>,...) = ...
举一个实际的例子。上周有一个需求要对我们Newgroup所有的case进行统计,其中Newsgroup存在数据库中的中文标题都被用Base64编码了,需要在取出来后进行解码。这个时候虽然也可能可以用T-SQL写,不过因为有现成的解码assembly,因此直接引用assembly了。SSIS2005中script还一定要用VB来写,最后用Renjie Yu推荐的Reflector解决了。
2. 对表格式数据操作(顺便说一句,SQL Server 2008中也允许对传递表格了,所以这一条还是要看具体情况了)
CLR数据访问
CLR通过Sqlclient来访问数据,因此代码相对冗长,但最终查询所使用的查询语句和T-SQL都是相同的。
就大多数的过程性的计算而言,CLR占决定性优势,但只有数据访问T-SQL占优。
B. CLR vs. XPs
CLR相比比存储过程的优点如下:
1. 粒状控制
这个名字听起来怪怪的。解释一下的话这里是指安全性控制。CAS在这里得到应用。
2. 数据访问
XPs需要新开个连接。而CLR可以更有效地利用现有连接。
3. 特别的数据类型支持
这里指xml,nvarchar(max)之类,我猜现在的存储过程应该支持了吧
4. Scalability
这个词语我一直不知道怎么样才能最确切的翻译。可测量性?可扩展性?
托管的API使能够控制内存线程同步等。不过08新特性之Resouce Governor也可以做到了。
总而言之,数据访问和发送结果集,CLR表现占优;除此之外,托管代码因为多了一步转换自然性能相对差。
中间层编程
某些逻辑可以放到数据库而非程序中解决。常用的有以下两个:
数据验证
减少网络传输
下面我们来看个范例:
生产计划
该任务包括对未来需求的预测,有以下几个特点:
1. 有大量的数据输入
2. 结果集很小
3. 需要相当多的计算
把算法代码放到中间层会导致大量的需求数据的传输,pass。
因为可能包含复杂数据类型和复杂的计算,所以存储过程pass。
所以这个时候就需要用CLR和T-SQL结合了。
常用应用实例:
1. 使用.NET framework进行数据验证
这里需要用到System.Text.RegularExpressions命名空间的RegEx类(Regular expression),这个比在T-SQL中一个个写like操作更有效。
2. 产生结果集
从数据库对象(存储过程或视图)中获得结果集,如果简单的话可以通过使用视图或inline table-valued function来解决。复杂的话就要靠CLR的ADO.NET provider(如SQLDataReader)了。好像还有个SQLPipe的东西,不过不怎么了解,还是略过算了。
总结
以下的这张图总结了使用场合:http://www.sqlskills.com/resources/Whitepapers/SQL%20Server%20DBA%20Guide%20to%20SQLCLR.htm#_Toc110615508
参考资料:
how to weigh the strengths and weakness of CLR over T-SQL you might consider reading the following resources from Microsoft:
Using CLR Integration in SQL Server 2005
<http://msdn.microsoft.com/en-us/library/ms345136.aspx>
Performance of CLR Integration
<http://technet.microsoft.com/en-us/library/ms131075.aspx>
other resources that may be helpful:
The Database Administrator’s Guide to the SQL Server Database Engine .NET Common Language Runtime Environment
<http://www.sqlskills.com/resources/Whitepapers/SQL%20Server%20DBA%20Guide%20to%20SQLCLR.htm#_Toc110615508>