Table-Valued Functions and Cursors Performance

Another performance issue identified was the use of Cursors when applying Table-Valued Functions to large datasets, whereby the TVF was applied to each individual record using a cursor, e.g. to split delimited strings into lists:

DECLARE @string_table TABLE (
   string VARCHAR(MAX)
);

INSERT INTO @string_table
VALUES
   ('the,first,string,of,several')
   ,('this,is,another,string')
   ,('the,last,string')
   ,(...);

-- Cursor method:

DECLARE @string VARCHAR(MAX);

DECLARE mycursor CURSOR FOR
   SELECT string FROM @string_table;

OPEN mycursor

FETCH NEXT FROM mycursor
   INTO @string

WHILE @@FETCH_STATUS = 0
BEGIN
   SELECT @string, value FROM dbo.comma_delimited_string_to_list(@string)

   FETCH NEXT FROM mycursor
      INTO @string
END

CLOSE mycursor
DEALLOCATE mycursor

The alternative & more performant option is to use CROSS APPLY to apply a TVF to the entire dataset, e.g.:

-- Cross Apply Method:

SELECT b.string, c.value FROM @string_table b
CROSS APPLY dbo.comma_delimited_string_to_list(string) c