We use the CLR because of the complexity of the processing. It probably could be written in a cursor - but it would be a maintenance headache, especially as there are more .NET skills here than T-SQL. Also it has to run in a 2-minute slot, and cursor performance may not cut it.

The process is designed to generate a sequence of records using several user-definable criteria. Each rule is given a priority and can force a grouping-together (resulting in clumps of similar records) or a spreading apart (to get ratios of 1:N patterns). On top of this there are priority 'queue-jumping' rules to be applied after.

It actually piggy-backs on antoher SQL Server Agent Job, which already gets most of the data in temporary tables, so it seemed the logical place to call the CLR process, as the context connection can access these temporary tables. (instead of creating a new .NET process that calls SQL)

And I now have performance down to 1.2 seconds.