Last week I had to make a SQL Procedure go faster and a new join in the same operation. From the execution plan I could see that the thing that cost most time was a call to an external function made as a .NET CLR that got all the groups a person was member of, and returned it as a comma separated string. This tok a couple of milliseconds, each time, and with some of the users input resulting in over 8000 calls in a single statement, the procedure took to long time. I also had the requirement to add an other column in the result, showing a comma separated list of functions each persons had. Doing this the same way as before, would turn the procedure almost twice as sluggish as before.

To get the result wanted, a small trick was used. Here is a simplified version of what I wrote:

SELECT TOP 10
        substring(O.Functions,2,500)
FROM    dbo.Tp_Person P
CROSS APPLY ( SELECT    ',' + CAST(FunctionName AS varchar) AS [text()]
              FROM      dbo.Tp_Function F
              WHERE     F.Personid = P.Personid
              FOR XML PATH('')
             ) O( Functions )


This trick actually reduced the time of the procedure on 5000 people from about 30 seconds to 1,5 seconds. Made my day.