logo标签列表

在 SQL 2005+ 中，CLR 存储过程是否优于 TSQL 存储过程？

.netsql-servert-sqlsqlclr
16

我的观点是不喜欢使用CLR存储过程，我更偏向于使用Transact SQL存储过程，因为它们更轻量且可能性能更高，而CLR存储过程允许开发人员进行各种捣蛋。

但最近我需要调试一些非常糟糕编写的TSQL存储过程。通常情况下，我发现很多问题是由于原始开发人员没有真正的TSQL经验，他们只关注于ASP.NET / C#。

因此，使用CLR存储过程首先提供了一个更熟悉的工具集给这种类型的开发人员，其次，调试和测试工具更加强大（即使用Visual Studio而不是SQL Management Studio）。

我非常感兴趣听到你的经验，因为似乎这并不是一个简单的选择。

12个回答
15

既有精心编写、深思熟虑的 T-SQL，也有 CLR。如果某些函数不经常被调用，并且在 SQL Server 2000 中需要扩展程序，则可以考虑使用 CLR。此外，将计算与数据直接运行可能很有吸引力。但是，通过引入新技术来解决糟糕的程序员听起来并不是一个好主意。

1
“但是通过引入新技术来解决糟糕的程序员问题似乎不是一个好主意。” - 很好的观点，谢谢。当我看到开发人员在ASP.NET上如此专业化而几乎忽略其他方面时，我感到担忧。 - Ash
12

CLR存储过程并不意味着要取代基于集合的查询。如果您需要查询数据库，仍然需要将SQL放入您的CLR代码中，就像嵌入常规代码一样。这将是浪费精力。

CLR存储过程主要用于两个方面：1）与操作系统交互，例如从文件中读取或在MSMQ中丢弃消息，以及2）执行复杂计算，特别是当您已经有用于进行计算的.NET语言编写的代码时。

3
我会尽力为您翻译：我想补充一句，当你需要使用正则表达式时，它们特别好用。 - HLGEM
7
托管CLR在SQL Server中旨在为数据库开发人员提供更灵活的选项来完成任务。正如其他人所提到的，SQL非常适用于对数据集进行操作和修改。任何经验丰富的大型应用程序开发者都可能会告诉您，试图使用纯SQL（有时是单个宏查询）来执行某些复杂业务/领域规则可能会变得非常困难。
有些任务最好以过程性或面向对象的方式处理。通过选择使用.NET代码来分解逻辑序列，查询操作可以更容易阅读和调试。我使用过CLR存储过程，我可以告诉你，通过调试器逐步跟踪确实使跟踪数据库级别上正在发生的事情更容易。
只举一个例子，我们经常在这里使用CLR存储过程作为动态搜索查询的“网关”。假设一个搜索请求可以具有多达30个不同的搜索参数。用户显然不会使用其中的所有30个，因此传递的数据结构将具有30个参数，但大多数为DBNULL。出于明显的安全原因，客户端没有生成动态语句的选项。结果的动态语句是在内部生成的，而不必担心外部的“额外”内容。
4

一般而言，如果您有某些不需要与数据库进行大量交互的内容，那么可以使用CLR。例如，解析或解码值。这在CLR中更容易完成并返回值。

试图在CLR中执行复杂查询并不是正确的方法。

顺便提一句，这在2008年也没有改变。

4
我相信这两者并不等同......不能够直接进行比较。 CLR 集成旨在淘汰过去的“扩展存储过程”。我们的工作场所有一些扩展存储过程，它们本质上是对 SQL 数据进行处理/逻辑的块，这些块无法通过常规 DB 存储过程/T-SQL 进行处理。所以他们将其编写为 C++ DLL 中的扩展存储过程，可以类似地调用。 现在它们已经被淘汰，CLR 集成是替代品
  • DB 存储过程：如果可以使用 T-SQL 存储过程完成，则使用它。
  • CLR 存储过程：如果逻辑过于复杂或繁琐，无法通过 T-SQL 完成...如果需要较少的 CLR 代码行数来解决（字符串操作、复杂/自定义排序或过滤等），请使用此方法。
但是性能呢？性能有什么区别吗？ - asyncwait
@asyncwait：如果你对性能差异感到好奇，我在这里发布了一份研究报告（https://www.simple-talk.com/sql/t-sql-programming/clr-performance-testing/），该报告使用了SQL Server 2008，并已针对确定性标量CLR函数进行了性能改进。 - Solomon Rutzky
4

除了文件系统访问（CLR过程具有非常明显的优势），我会使用T-SQL过程。如果您有特别复杂的计算，您可能可以将该部分放入CLR函数中，并从您的过程内调用此函数（我发现UDF是CLR集成真正闪耀的地方）。然后，您就可以获得CLR集成在任务的特定部分的好处，但尽可能多地保留存储过程逻辑。

3

根据您的说法，我更希望您让开发人员受过适当的 t-SQl 和数据库培训，而不是允许他们在 CLRs 中执行 t-sql 任务，以避免他们可能会对性能造成更大的破坏。不理解数据库的开发人员往往把这当作借口，避免按照最佳数据库性能方式执行任务，因为他们想要走看似更简单的路线。

1
一位开发者对另一位说：“嘿，我有个酷炫的想法！让我们把对象 GUID 用作主键！” - Valentino Vranken
1
@ValentinoVranken - 或者像微软的一些开发人员一样 - 嘿，让我们从对象中获取GUID，取前半部分，然后反转它们并将其用作键（在注册表中）。是的，写安装程序部分的人 - 我们正在看着你。 - gbjbaanb
2

工作总是需要合适的工具，所以这取决于您想要完成什么任务。

然而，一般来说，您是正确的，CLR存储过程的开销更大，并且在像T-SQL这样的集合操作上永远不会表现出色。我的建议是除非在T-SQL中变得过于复杂，否则全部使用T-SQL。然后，尽力让T-SQL方法起作用。 :-)

CLR存储过程非常好，确实有其用武之地，但它们的使用应该是例外而不是规则。

2
SQL Server Books Online的主题页面列出了以下好处：
更好的编程模型。.NET框架语言在许多方面比Transact-SQL更加丰富，提供了以前SQL Server开发人员所不具备的结构和功能。开发人员还可以利用.NET框架库的强大功能，该库提供了一组广泛的类，可用于快速高效地解决编程问题。
更安全和可靠。托管代码在由数据库引擎托管的公共语言运行时环境中运行。SQL Server利用这一点，提供了一个比早期版本的SQL Server中可用的扩展存储过程更安全和可靠的替代方案。
定义数据类型和聚合函数的能力。用户定义的类型和用户定义的聚合是两种新的托管数据库对象，它们扩展了SQL Server的存储和查询能力。
通过标准化环境简化开发。数据库开发集成到Microsoft Visual Studio .NET开发环境的未来版本中。开发人员使用与编写中间层或客户端层.NET框架组件和服务相同的工具来开发和调试数据库对象和脚本。
潜在的性能和可伸缩性改进。在许多情况下，.NET框架语言编译和执行模型提供了比Transact-SQL更好的性能。
1
不要相信炒作。在某些情況下，CLR 函數/存儲過程是更好的選擇，但“更好的編程模型”不是其中之一，改進安全性也不是（您知道它們在哪個執行上下文中運行，對吧），而且性能只有在某些情況下才會更好 - 而且只有在代碼是迭代使用命令式邏輯的行集時才會更好。 SQL Server 和 .NET 運行時之間的上下文切換也會給您帶來巨大的性能損失。 - gbjbaanb
1
@gbjbaanb：CLR-based代码的安全模型绝对比其他扩展T-SQL功能的选项更安全，即：扩展存储过程、OLE自动化过程（即sp_OA*）和SQLCMD。CLR模型提供了更精细的控制以及使用模拟用户的能力。我已经写了两篇文章（SQLCLR系列的一部分），详细介绍了安全性问题（需要免费注册）：http://www.sqlservercentral.com/articles/SQLCLR/109905/ 和 http://www.sqlservercentral.com/articles/SQLCLR/112888/。 - Solomon Rutzky
2
我们遇到了一个问题，一个CLR函数在常规SQL proc中被调用了数千次。这是一个从另一个系统导入数据的过程。该函数验证了数据并很好地处理了null值。
如果我们在TSQL中执行操作，该过程大约需要15秒完成。如果我们使用CLR函数，则该过程需要20-40分钟才能完成。CLR函数看起来更优雅，但据我们所知，每次使用CLR函数时都会有启动时间的开销。因此，如果您使用一个CLR函数完成大量操作，那么这是可以接受的，因为启动时间与操作时间相比较小。或者如果您适度调用CLR函数，那么所有调用函数的总启动时间将很小。但是要注意循环。
此外，出于可维护性的考虑，最好不要使用比实际需要更多的语言。
假设您的CLR函数是确定性的，那么在"SqlFunction"属性中标记为“IsDeterministic=true”了吗？如果没有，那么这可能是执行时间长达20-40分钟的主要因素。另外，您使用的SQL Server版本是什么？关于确定性标量CLR函数，在SQL Server 2012中进行了性能改进。此外，将NVARCHAR(MAX)传入或传出，而不是NVARCHAR(4000)，会导致性能下降。 - Solomon Rutzky
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接