SQL Server 2008 CLR 与 T-SQL：效率/速度有区别吗？
我是一名 C# 开发人员，在 T-SQL 中完成了一些基本的数据库工作。然而，我需要编写一个非常复杂的存储过程，远远超出我的 T-SQL 知识。
作为 SQL Server 2008 的一部分，使用 .net CLR 用 C# 编写存储过程是否会导致存储过程的效率低于用 T-SQL 编写的存储过程？差异（如果有）显着吗？为什么？
I'm a C# developer who has done some basic database work in T-SQL. However, I need to write a very complicated stored procedure, well above my T-SQL knowledge.
Will writing a stored procedure in C# using the .net CLR as part of SQL Server 2008 cause my stored procedure to be less efficient than if it were written in T-SQL? Is the difference (if any) significant? Why?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
CLR需要一些通信开销（在CLR和SQL Server之间传递数据）经验法则是：
如果您的逻辑主要包括海量数据的转换，可以使用集合操作来执行，那么使用
TSQL。如果您的逻辑主要包含相对少量数据的复杂计算，请使用
CLR。使用集合运算可以做的事情比看起来要多得多。如果您在这里发布您的要求，我们可能会提供帮助。
CLRrequire some communication overhead (to pass data between theCLRandSQL Server)Rule of thumb is:
If your logic mostly includes transformations of massive sets of data, which can be performed using set operations, then use
TSQL.If your logic mostly includes complex computations of relatively small amounts of data, use
CLR.With set operations much more can be done than it seems. If you post your requirements here, probably we'll be able to help.
请参阅CLR 集成的性能：
Please see Performance of CLR Integration:
问题是“使用 .net CLR 作为 SQL Server 2008 的一部分用 C# 编写存储过程是否会导致我的存储过程比用 T-SQL 编写的效率低？”确实太宽泛，无法给出有意义的答案。效率差异很大，不仅取决于您正在执行的操作类型，还取决于您执行这些操作的方式。您可能有一个 CLR 存储过程，它的性能应该优于等效的 T-SQL 过程，但实际上由于编码不当而性能更差，反之亦然。
考虑到问题的一般性质，我可以说“一般来说”，可以在 T-SQL 中完成的事情（没有太多复杂性）很可能应该在 T-SQL 中完成。一个可能的例外可能是 TVF，因为 CLR API 有一个非常有趣的选项来将结果流式传输回来（我为 SQL Server Central 写了一篇文章 - 需要免费注册 - 关于 STVF）。但是，如果没有代码的 CLR 和 T-SQL 版本并使用生产级数据进行测试，就无法确定（即使写得不好的代码通常在 10k 行或更少的情况下也能表现良好）。
所以这里的真正问题可以归结为：
在这种情况下，最好简单地询问如何在 T-SQL 中处理这个特定任务。很可能有一些不复杂的解决方案，您只是还不知道，但在了解功能/技术/等后就能够理解。并且您仍然可以在 SQLCLR 中编写等效的解决方案并比较它们之间的性能。但是，如果在 T-SQL 中处理它没有令人满意的答案，那么就在 SQLCLR 中进行处理。
话虽这么说，我三年前确实做了一项关于 SQLCLR 与 T-SQL 性能的研究，并将结果发布在 简单对话。
The question of "Will writing a stored procedure in C# using the .net CLR as part of SQL Server 2008 cause my stored procedure to be less efficient than if it were written in T-SQL?" is really too broad to be given a meaningful answer. Efficiency varies greatly depending on not just what types of operations are you doing, but also how you go about those operations. You could have a CLR Stored Procedure that should out-perform an equivalent T-SQL Proc but actually performs worse due to poor coding, and vice versa.
Given the general nature of the question, I can say that "in general", things that can be done in T-SQL (without too much complexity) likely should be done in T-SQL. One possible exception might be for TVFs since the CLR API has a very interesting option to stream the results back (I wrote an article for SQL Server Central--free registration required--about STVFs). But there is no way to know for certain without having both CLR and T-SQL versions of the code and testing both with Production-level data (even poorly written code will typically perform well enough with 10k rows or less).
So the real question here boils down to:
And in this case it would be best to simply ask how to tackle this particular task in T-SQL. It could very well be that there are non-complex solutions that you just don't happen to know about yet, but would be able to understand upon learning about the feature / technique / etc. And you can still code an equivalent solution in SQLCLR and compare the performance between them. But if there is no satisfactory answer for handling it in T-SQL, then do it in SQLCLR.
That being said, I did do a study 3 years ago regarding SQLCLR vs T-SQL Performance and published the results on Simple-Talk.