并行化 TSQL CLR 过程
我试图弄清楚如何并行化一些过程代码以在表中创建记录。
情况是这样的（抱歉，我无法以实际代码的方式提供太多信息）：
我必须根据之前的服务日期、当前里程、计划的每日里程和差异来预测何时需要车辆服务每次服务之间的里程。
总而言之 - 这是非常程序化的，对于每辆车，我需要考虑它的历史、当前的维修状态、每日里程（可以根据里程计划中定义的范围而变化）以及维修顺序。
目前我正在用 PHP 计算所有这些，100 辆车大约需要 20 秒。由于未来可能会扩展到几千秒，20秒就太长了。
所以我决定尝试在 CLR 存储过程中执行此操作。起初我想尝试多线程，但我很快发现在 TSQL 主机中这并不容易。有人建议我让 TSQL 自行解决并行化问题。但我不知道怎么办。如果不是因为代码需要创建记录，我可以将其定义为函数并执行以下操作：
SELECT dbo.PredictServices([FleetID]) FROM Vehicles
TSQL 应该发现它可以并行化，但我知道过程没有其他选择。
我能做些什么来并行化这个吗？
I'm trying to figure out how I can parallelize some procedural code to create records in a table.
Here's the situation (sorry I can't provide much in the way of actual code):
I have to predict when a vehicle service will be needed, based upon the previous service date, the current mileage, the planned daily mileage and the difference in mileage between each service.
All in all - it's very procedural, for each vehicle I need to take into account it's history, it's current servicing state, the daily mileage (which can change based on ranges defined in the mileage plan), and the sequence of servicing.
Currently I'm calculating all of this in PHP, and it takes about 20 seconds for 100 vehicles. Since this may in future be expanded to several thousand, 20 seconds is far too long.
So I decided to try and do it in a CLR stored procedure. At first I thought I'd try multithreading it, however I quickly found out it's not easy to do in the TSQL host. I was recommended to allow TSQL to work out the parallelization itself. Yet I have no idea how. If it wasn't for the fact the code needs to create records I could define it as a function and do:
SELECT dbo.PredictServices([FleetID]) FROM Vehicles
And TSQL should figure out it can parallelize that, but I know of no alternative for procedures.
Is there anything I can do to parallelize this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您收到的建议是正确的。您的 CLR 存储过程中根本没有用于并行性的 .NET 框架工具。另请记住，CLR 存储过程的市场范围相当狭窄，它们会对 SQL Server 的性能和可伸缩性产生不利影响。
如果我正确理解该任务，您需要计算一些记录的函数 PredictServices 并将结果存储回数据库。在这种情况下，如果 PredictServices 只是数据访问/直接的数据转换，那么 CLR 存储过程可能是您的选择。最佳实践是创建 WWF (Windows Workflow Foundation) 服务来执行计算并从 PHP 调用它。在工作流服务中，您可以实现任何解决方案，包括涉及并行性的解决方案。
The recommendation you received is a correct one. You simply don't have .NET framework facilities for parallelism available in your CLR stored procedure. Also please keep in mind that the niche for CLR Stored Procedures is rather narrow and they adversely impact SQL Server's performance and scalability.
If I understand the task correctly you need to compute a function PredictServices for some records and store the results back to database. In this case CLR Stored procedures could be your option provided PredictServices is just data access/straightforward transformation of data. Best practice is to create WWF (Windows Workflow Foundation) service to perform computations and call it from PHP. In Workflow Service you can implement any solution including one involving parallelism.