最近在尝试做个图像检索的实验品, 需要在数据库中存储大量图像的特征数据, 大多又是以Binary形式存储的, 对于这种数据肯定就不能用T-SQL里的WHERE...LIKE等有限的语法来检索了, 而数据库的客户端所需要的只是检索到的结果, 而不是那些大量的特征数据, 对于特征数据往往又需要比较复杂的运算(用T-SQL来写那些运算的话, 估计会头大的.. 况且我对T-SQL也不熟), 为了减少客户端和数据库于是想到了SQL Server 2005的CLR集成的特性, 想试试使用托管存储过程, 第一次用这东西还是碰到了一些问题, 通过Google和MSDN都一一解决了, 结果发现这个特性用起来还不错.
1. 关于部署
其实在Visual Studio 2005的Database项目中对部署和调试都有自动化的功能, 这在相关文章和文档里都看得到(但由于某些原因, 我还一次都没用过..), 但下面讲的是手工部署以及后面的手动配置调试.
SQL Server的CLR集成特性对于安全性的要求比较高, 对于将部署的程序集的代码有严格的审核, 对于程序集分为三个Permission Set: Safe, External Access 和 Unsafe, 由于我将要部署的程序集存在一些"不安全"的代码以至于不能被部署为Safe的程序集, 而直接执行下面的T-SQL代码
from 'E:\
\Adrian.ICanSee.Data.dll'with permission_set = unsafe
失败, 报错信息为
CREATE ASSEMBLY for assembly 'Adrian.ICanSee.Data' failed because assembly 'Adrian.ICanSee.Data' is not authorized for PERMISSION_SET = UNSAFE. The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.
可以看出将一个程序集部署为Unsafe必须满足以下两个条件中的一个:
1. the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on;
2. the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.
第一个条件看上去似乎简单不少, 于是我选择了第一个, 通过SQL Server Management Studio的可视化操作很容易就设置好了权限, 当然也让人越来越不会去记那些T-SQL语句. 然后使用
ALTER DATABASE ICanSeeDB SET TRUSTWORTHY ON 设置好了TrustWorthy属性.
再执行create assembly, 运行成功. 但并没有看到有存储过程加进去. 从程序集中注册存储过程还得写T-SQL:
create procedure Retrieve@histogram binary(288),@avgx binary(288),@avgy binary(288),@count intasexternal name ICanSeeCLR.[Adrian.ICanSee.Data.SQLCLR].Retrieve 存储过程参数对应的是C#方法里的参数类型:
[SqlProcedure]public static void Retrieve(SqlBinary histogram, SqlBinary avgx, SqlBinary avgy, SqlInt32 count) external name后的依次是<SQL Server程序集名称>.<类名>.<方法名称>
到这里一个存储过程就部署完了, 调用它和调用普通的存储过程一样.
2.关于调试存储过程
调试托管存储过程也需要几个前提, 详见SQL Server的文档, 但我安装的SQL Server 2005 Enterprise后没有经过任何设置就可以直接采用下面的方法调试了:
调试存储过程采用的方法是用Visual Studio 2005 attach process到sqlservr.exe, 但对于手工部署的程序集必须还得上传调试信息文件(.pdb):
alter assembly ICanSeeCLRadd file from 'E:\\Adrian.ICanSee.Data.pdb' 在源文件中设置好断点, attach process, 没有其他问题的话任何成功进入存储过程的请求都能被捕捉到.
如果需要同时调试客户端程序和托管存储过程的话, 就得另开一个Visual Studio来调试它了.
3. 关于性能
我通过在托管存储过程中和数据库客户端中分别运行运算逻辑发现它们之间的性能几乎相同. 托管存储过程的方案只带来了不超过15%的性能提升. 但我只是在本地的测试, 数据库和客户端都在我的本本上, 数据量也不是很大, 看不到我开始认为的托管存储过程的减少数据传输所带来的性能优势, 但在实际的大量数据的分布式系统里, 也许.. 可能会带来一些性能优势. 更多有关CLR集成性能的内容在文档里有所描述.
但托管代码的最大优势是它功能的强大, T-SQL无法比拟的强大, 既然性能不会差, 在适当的情况下选择它还是不错的.