SQL Server CLR 存储过程在数据处理任务中——是好还是坏？
简而言之 - 在 CLR 存储过程 中实现大部分业务逻辑是否是一个很好的设计解决方案？
我最近读了很多关于它们的内容，但我不知道何时应该使用它们，最佳实践是什么，它们是否足够好。
例如，我的业务应用程序需要
- 解析大型 固定长度文本文件，
- 从文件中的每一行中提取一些数字，
- 根据这些数字应用一些复杂的业务规则（涉及正则表达式匹配、针对数据库中许多表的数据的模式匹配等），
- 并作为计算结果更新记录在数据库中。
还有一个 GUI，供用户选择文件、查看结果等。
该应用程序似乎是实现经典 3 层架构的良好候选者：数据层、逻辑层和 GUI 层。
- 数据层将访问数据库。
- 逻辑层将作为 WCF 服务运行并实现业务规则，与数据层交互。GUI
- 层将是逻辑层和用户之间的通信手段。
现在，考虑到这个设计，我可以看到大多数业务规则可能在 SQL CLR 中实现并存储在 SQL Server 中。我可能会将所有原始数据存储在数据库中，在那里运行处理并获取结果。我看到了该解决方案的一些优点和缺点：
优点：
- 业务逻辑靠近数据运行，这意味着网络流量更少。
- 一次处理所有数据，可能利用并行化和最佳执行计划。
缺点：
- 业务逻辑分散：有些部分在这里，有些部分在那里。
- 有问题的设计方案，可能会遇到未知的问题。
- 很难为处理任务实现进度指示器。
我想听听您对 SQL CLR 的所有看法。有人在生产中使用它吗？这样的设计有什么问题吗？这是好事吗？
In short - is it a good design solution to implement most of the business logic in CLR stored procedures?
I have read much about them recently but I can't figure out when they should be used, what are the best practices, are they good enough or not.
For example, my business application needs to
- parse a large fixed-length text file,
- extract some numbers from each line in the file,
- according to these numbers apply some complex business rules (involving regex matching, pattern matching against data from many tables in the database and such),
- and as a result of this calculation update records in the database.
There is also a GUI for the user to select the file, view the results, etc.
This application seems to be a good candidate to implement the classic 3-tier architecture: the Data Layer, the Logic Layer, and the GUI layer.
- The Data Layer would access the database
- The Logic Layer would run as a WCF service and implement the business rules, interacting with the Data Layer
- The GUI Layer would be a means of communication between the Logic Layer and the User.
Now, thinking of this design, I can see that most of the business rules may be implemented in a SQL CLR and stored in SQL Server. I might store all my raw data in the database, run the processing there, and get the results. I see some advantages and disadvantages of this solution:
Pros:
- The business logic runs close to the data, meaning less network traffic.
- Process all data at once, possibly utilizing parallelizm and optimal execution plan.
Cons:
- Scattering of the business logic: some part is here, some part is there.
- Questionable design solution, may encounter unknown problems.
- Difficult to implement a progress indicator for the processing task.
I would like to hear all your opinions about SQL CLR. Does anybody use it in production? Are there any problems with such design? Is it a good thing?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我不这样做 - CLR ins SQL Server 对于很多事情都很有用（计算哈希值、执行 SQL 刚刚吸收的字符串操作、正则表达式来验证字段值等），但复杂的逻辑，恕我直言，在数据库中没有业务。
这是单点性能问题，而且扩展起来也非常昂贵。另外，要么我把它们全部放在那里，要么——好吧——我在维护方面遇到了严重的问题。
I do not do it - CLR ins SQL Server is great for many things (calculating hashes, do string manipulation that SQL just sucks in, regex to validate field values etc.), but complex logic, IMHO, has no business in the database.
It is a single point of performance problems and also VERY expensive to scale up. Plus, either I put it all in there, or - well - I have a serious problem maintenance wise.
就我个人而言，我更喜欢拥有不依赖于数据库的业务功能。我仅在需要高级数据查询（生成在 SQL 中不容易实现的格式）时才使用 CLR 存储过程。根据您所做的事情，无论如何，我倾向于使用标准存储过程获得更好的性能结果，因此我个人只将它们用于我的高级任务。
我的两分钱。
HTH。
Personally I prefer to have business functionality not dependent on the database. I only use CLR stored procedures when I need advanced data querying (to produce a format that is not easy to do in SQL). Depending on what you are doing, I tend to get better performance results with standard stored procs anyway, so I personally only use them for my advanced tasks.
My two cents.
HTH.
一般来说，您可能不想这样做，除非您可以获得显着的性能优势或者有令人信服的技术理由这样做。这种原因的一个例子可能是自定义聚合函数。
使用 CLR 存储过程的一些充分理由：
您可以从该技术的独特功能（例如自定义聚合函数）中受益。
您可以从 CLR Sproc 中获得性能优势 - 也许是一种快速的逐条记录处理任务，您可以从快进游标中读取数据，在核心中缓冲输出并将其批量加载到目标表。
您想要包装一些 .Net 代码或 .Net 库，并使其可供数据库服务器上运行的 SQL 代码使用。这方面的一个例子可能是 OP 问题中的正则表达式匹配器。
您想要欺骗并包装一些非托管且非常不安全的东西，以便可以通过 SQL 代码访问它，而无需使用 XP。从技术上讲，微软已经声明 XP 已被弃用，并且许多安装出于安全原因而禁用它们。
有时您无法选择更改客户端代码（也许您有一个关闭的选项） the-shelf 应用程序），因此您可能需要从数据库内启动外部操作。在这种情况下，您可能需要让触发器或存储过程与外界交互，可能会查询工作流程的状态、将某些内容写入文件系统或（更极端地）通过屏幕将事务发布到远程大型机系统scraper 库。
使用 CLR 存储过程的不良理由：
对通常在中间层完成的操作进行了微小的性能改进。请注意，磁盘流量可能比网络流量慢得多，除非您尝试通过网络连接传输大量数据。
CLR 存储过程很酷，您想将它们放在您的 CV 中
无法编写面向集合的 SQL。
Generally, you probably don't want to do this unless you can get a significant performance advantage or there is a compelling technical reason to do it. An example of such a reason might be a custom aggregate function.
Some good reasons to use CLR stored procedures:
You can benefit from a unique capability of the technology such as a custom aggregate function.
You can get a performance benefit from a CLR Sproc - perhaps a fast record-by-record processing task where you can read from a fast forward cursor, buffer the output in core and bulk load it to the destination table in batches.
You want to wrap a bit of .Net code or a .Net library and make it available to SQL code running on the database server. An example of this might be the Regex matcher from the OP's question.
You want to cheat and wrap something unmanaged and horribly insecure so as to make it accessible from SQL code without using XPs. Technically, Microsoft have stated that XP's are deprecated, and many installations disable them for security reasons.
From time to time you don't have the option of changing the client-side code (perhaps you have an off-the-shelf application), so you may need to initiate external actions from within the database. In this case you may need to have a trigger or stored procedure interact with the outside world, perhaps querying the status of a workflow, writing something out to the file system or (more extremely) posting a transaction to a remote mainframe system through a screen scraper library.
Bad reasons to use CLR stored procs:
Minor performance improvements on something that would normally be done in the middle tier. Note that disk traffic is likely to be much slower than network traffic unless you are attemtping to stream huge amounts of data across a network connection.
CLR sprocs are cool and you want to put them on your C.V.
Can't write set-oriented SQL.