Table Valued Function [XML Reader] Very Slow - Alternatives?

I have the following query that really kills performance and want to know what alternatives their are to an xml reader subquery. The purpose of this query is to export data with some html code.

An example of the table data is as follows.

  p_s_id | p_c_id | notes
  -----------------------
     1   |   1    | this note is really long.
     2   |   1    | This is fun.
     3   |  null  | long note here 
     4   |   2    | this is not fun
     5   |   2    | this is not fun
     6   |   3    | long note here

I want to take all distinct notes that have the same p_c_id and join them together as shown below.

Any additional information can be provided so feel free to comment.

select distinct
     p_c_id
    ,'<br/><br/>'+(select distinct '&bull; ' +cast(note as nvarchar(max)) + ' <br/> '
      from dbo.spec_notes_join m2
      where m.p_c_id = m2.p_c_id
      and isnull(note,'') <> ''
      for xml path(''), type).value('.[1]', 'nvarchar(max)') as notes_spec
from dbo.spec_notes_join m

so the export would look as follows:

  p_c_id | notes
 --------------
   开发者_StackOverflow中文版 1    | <br/><br/> &bull; this note is really long.  <br/> &bull This is fun <br/>
    2    | <br/><br/> &bull; This is not fun.  <br/>
    3    | <br/><br/> &bull; long note here.   <br/>

I think you will get slightly better performance you skip the distinct in the outer query and do a group by p_c_id instead.

select p_c_id,
      '<br/><br/>'+(select distinct '&bull; ' +cast(note as nvarchar(max)) + ' <br/> '
                    from dbo.spec_notes_join m2
                    where m.p_c_id = m2.p_c_id and 
                          isnull(note,'') <> ''
                    for xml path(''), type).value('.', 'nvarchar(max)') as notes_spec
from dbo.spec_notes_join m     
group by p_c_id

You could also try concatenating with a CLR User-Defined Aggregate Function.

Other alternatives can be found here Concatenating Row Values in Transact-SQL.

While this alternative skips the XML, I don’t know if it improves performance—if you could test and post results as a comment, I’d apreciate it. (It worked on my quick mock up, you may need to do some minor debugging on your own structures.)

Start with this function:

CREATE FUNCTION dbo.Testing
(
    @p_c_id  int
)
RETURNS varchar(max)
AS
 BEGIN
    DECLARE @ReturnString  varchar(max)

    SELECT @ReturnString = isnull(@ReturnString + ' <br/> , <br/><br/>&bull; ', '<br/><br/>&bull; ') + Name
     from (select distinct note
            from spec_notes_join
            where p_c_id = @p_c_id
             and isnull(note, '') <> '') xx

    SET @ReturnString = @ReturnString + ' <br/> '

    RETURN @ReturnString
 END
GO

and then embed it in your query:

SELECT p_c_id, dbo.Testing(p_c_id)
 from (select distinct p_c_id
        from dbo.spec_notes_join) xx

This may perform poorly because of the function called required for each row. A possibly quicker variant would be to write the function as a table-valued function, and reference it by a CROSS APPLY in the join clause.