Parallelize TSQL CLR Procedure

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?

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.