SQL中CLR函数的性能和使用 [英] Performance and usage of CLR functions in SQL
问题描述
SQL Server 允许您创建 CLR 函数、存储过程、用户类型和其他对象，用于在 SQL 中完成的非常复杂的目的.
SQL Server allows you to create CLR functions, stored procedures, user types and other objects, for purpose that are really complex to be done inside SQL.
但是，有人可以比较这两个东西:TSQL 对象和 CLR 对象，在性能、好处等方面.
But, can someone compare those two things: TSQL Object and CLR Object, in sense of performance, benefits, and so on.
使用 CLR 对象的实际情况是什么?
What are real situations for usage CLR objects?
是否有使用它们的最佳实践建议?
Is there any best practices proposition for their usage?
推荐答案
这里有两个问题需要单独解决.
There are two questions here that need to be addressed separately.
在功能方面&好处是，我写了一篇文章(关于 SQLCLR 主题的系列文章的一部分)，该文章着眼于 SQLCLR 的哪些用途是合适的"，主要是通过查看它可以做什么，否则无法完成，或者几乎不能轻松完成.那篇文章是通往 SQLCLR 级别 1 的阶梯:什么是 SQLCLR?"(免费需要注册)，并且在对问题的评论中链接的问题的回答中进行了总结，SQL SERVER CLR 的优势.
In terms of functionality & benefits, I wrote an article (part of a series on the topic of SQLCLR) that looks at what uses of SQLCLR are "appropriate", mainly by looking at what it can do that cannot be done otherwise, or not done nearly as easily. That article is "Stairway to SQLCLR Level 1: What is SQLCLR?" (free registration required) and it is summarized in an answer to the question that was linked in a comment on the question, Advantage of SQL SERVER CLR.
在性能方面，我几年前(2011 年 7 月)发表了一项研究，其中详细介绍了各种场景并测试了原始 SQL、T-SQL 函数中的该 SQL 以及 CLR 中的该算法-基于函数.我测试了标量函数和表值函数.那篇文章是CLR 性能测试"(无需注册).请记住，测试是在 SQL Server 2008 上完成的，并且在 SQL Server 2012 中对基于 CLR 的确定性标量函数进行了性能改进.这意味着，如果至少在 SQL Server 2012 上重新运行这些测试(如果不是较新版本)，则 CLR 函数的性能结果会更好.但结论是，即使在没有这些改进的 SQL Server 2008 上，它取决于许多因素，有时 CLR 更快，有时 T-SQL 更快.但是，在大多数情况下，可以用简单到中等的 T-SQL 作为查询的一部分表达的公式，而不是抽象为任何类型的函数，是迄今为止最快的.
In terms of performance, I published a study a few years ago (July, 2011) that detailed various scenarios and tested the raw SQL, that SQL in a T-SQL function, and that algorithm in a CLR-based function. I tested both scalar functions and table-valued functions. That article is "CLR Performance Testing" (no registration required). Please keep in mind that the testing was done on SQL Server 2008 and there were performance improvements made in SQL Server 2012 regarding deterministic scalar CLR-based functions. Meaning, the performance results of the CLR functions would be better if re-running those tests on at least SQL Server 2012 if not a newer version. But the conclusion, even on SQL Server 2008 without having those improvements, is that it depends on many factors and sometimes CLR is faster and sometimes T-SQL is faster. But, most often a formula that can be expressed in simple to moderate T-SQL as part of the query, and not abstracted to a function of either type, is by far the fastest.
这篇关于SQL中CLR函数的性能和使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
