Performance issue with tally solution

create function [dbo].[fnSplit4](
@parameter varchar(Max) -- the string to split
, @Separator Varchar(64) -- the string to use as a separator
)
RETURNS @Items TABLE(
ID INT -- the element number
, item VARCHAR(8000) -- the split-out string element
, OffSet int -- the original offest
--( not entirley accurate if LEN(@Seperator) > 1 because of the Replace() )
)
AS
BEGIN
/*
"Monster" Split in SQL Server 2005

From Jeff Moden, 2008/05/22

BYoung, 2008/06/18: Modified to be a Table-Valued Function
And to handle CL/LF or LF-only line breaks

Test: (scripts all triggers in your database)
Select Lines.Item
From sys.sql_modules M
Join sys.objects O on O.object_id = M.object_id
cross apply dbo.fnSplit1(M.definition, char(13)+char(10)) Lines
Where O.Type = 'TR'
Order by O.create_date, Lines.ID
*/
Declare @Sep char(1)
--our seperator character (convenient, doesn't affect performance)
Set @Sep = char(10)
--NOTE: we make the @Sep character LF so that we will automatically
-- parse out rogue LF-only line breaks.

;WITH cteTally AS
(--==== Create a Tally CTE from 1 to whatever the length
-- of the parameter is
SELECT TOP (LEN(@Parameter))
ROW_NUMBER() OVER (ORDER BY t1.ID) AS N
FROM Master.sys.sysColumns t1
CROSS JOIN Master.sys.sysColumns t2
)
INSERT into @Items
SELECT ROW_NUMBER() OVER (ORDER BY N) AS Number,
SUBSTRING(@Parameter, N+1, CHARINDEX(@Sep, @Parameter, N+1)-N-1) AS Value
, N+1
FROM cteTally
WHERE N < LEN(@Parameter)
AND SUBSTRING(@Parameter, N, 1) = @Sep
--Notice how we find the separator

Return
END

create function [dbo].[fnSplit3](
@parameter varchar(Max) -- the string to split
, @Separator Varchar(64) -- the string to use as a separator
)
RETURNS @Items TABLE(
ID INT -- the element number
, item VARCHAR(8000) -- the split-out string element
, OffSet int -- the original offest
--( not entirley accurate if LEN(@Seperator) > 1 because of the Replace() )
)
AS
BEGIN
/*
"Monster" Split in SQL Server 2005

From Jeff Moden, 2008/05/22

BYoung, 2008/06/18: Modified to be a Table-Valued Function
And to handle CL/LF or LF-only line breaks

Test: (scripts all triggers in your database)
Select Lines.Item
From sys.sql_modules M
Join sys.objects O on O.object_id = M.object_id
cross apply dbo.fnSplit1(M.definition, char(13)+char(10)) Lines
Where O.Type = 'TR'
Order by O.create_date, Lines.ID
*/
Declare @Sep char(1)
--our seperator character (convenient, doesn't affect performance)
Set @Sep = char(10)
--NOTE: we make the @Sep character LF so that we will automatically
-- parse out rogue LF-only line breaks.

--===== Add start and end seprators to the Parameter so we can handle
-- all the elements the same way
-- Also change the seperator expressions to our seperator
-- character to keep all offsets = 1
SET @Parameter = @Sep+ Replace(@Parameter,@Seperator,@Sep) +@Sep
-- This reduces run-time about 10%

;WITH cteTally AS
(--==== Create a Tally CTE from 1 to whatever the length
-- of the parameter is
SELECT TOP (LEN(@Parameter))
ROW_NUMBER() OVER (ORDER BY t1.ID) AS N
FROM Master.sys.sysColumns t1
CROSS JOIN Master.sys.sysColumns t2
--Note: using the sysColumns trick is faster than a permanent Tally table
-- for this case anyway
)
INSERT into @Items
SELECT ROW_NUMBER() OVER (ORDER BY N) AS Number,
SUBSTRING(@Parameter, N+1, CHARINDEX(@Sep, @Parameter, N+1)-N-1) AS Value
, N+1
FROM cteTally
WHERE N < LEN(@Parameter)
AND SUBSTRING(@Parameter, N, 1) = @Sep
--Notice how we find the separator

Return
END

--- ==========================================================
--- -> Tally/fUNCTION solution
PRINT 'Start Tally-function 3 solution (correct line-splitting)'
SELECT @now = GETDATE()

--- Split text into lines
INSERT INTO @result
SELECT l.item
FROM @source s
CROSS APPLY dbo.fnSplit3(s.definition, char(13)+char(10)) l

--- Results
SELECT @duration = DATEDIFF(MILLISECOND, @now, GETDATE())
SELECT @count = COUNT(*) FROM @result
PRINT 'Milliseconds: ' + CONVERT(VARCHAR(10), @duration) + ' | Lines: ' + CONVERT(VARCHAR(10), @count)
--- <- Tally solution
--- ==========================================================

DELETE FROM @result --- Clean up

--- ==========================================================
--- -> Tally/fUNCTION solution
PRINT 'Start Tally-function 4 solution (has same bug as the rest)'
SELECT @now = GETDATE()

--- Split text into lines
INSERT INTO @result
SELECT l.item
FROM @source s
CROSS APPLY dbo.fnSplit4(s.definition, char(13)+char(10)) l

--- Results
SELECT @duration = DATEDIFF(MILLISECOND, @now, GETDATE())
SELECT @count = COUNT(*) FROM @result
PRINT 'Milliseconds: ' + CONVERT(VARCHAR(10), @duration) + ' | Lines: ' + CONVERT(VARCHAR(10), @count)
--- <- Tally solution
--- ==========================================================

DELETE FROM @result --- Clean up

RBarryYoung (4/13/2009)

And finally, here is the inclusion code to inlcude it with the other tests:

--- ==========================================================
--- -> Tally/fUNCTION solution
PRINT 'Start Tally-function 3 solution (correct line-splitting)'
SELECT @now = GETDATE()
 
--- Split text into lines
INSERT INTO @result
 SELECT l.item
  FROM @source s
   CROSS APPLY dbo.fnSplit3(s.definition, char(13)+char(10)) l
 
--- Results
SELECT @duration = DATEDIFF(MILLISECOND, @now, GETDATE())
SELECT @count = COUNT(*) FROM @result
PRINT 'Milliseconds: ' + CONVERT(VARCHAR(10), @duration) + ' | Lines: ' + CONVERT(VARCHAR(10), @count)
--- <- Tally solution
--- ==========================================================
 
DELETE FROM @result  --- Clean up
 
--- ==========================================================
--- -> Tally/fUNCTION solution
PRINT 'Start Tally-function 4 solution (has same bug as the rest)'
SELECT @now = GETDATE()
 
--- Split text into lines
INSERT INTO @result
 SELECT l.item
  FROM @source s
   CROSS APPLY dbo.fnSplit4(s.definition, char(13)+char(10)) l
 
--- Results
SELECT @duration = DATEDIFF(MILLISECOND, @now, GETDATE())
SELECT @count = COUNT(*) FROM @result
PRINT 'Milliseconds: ' + CONVERT(VARCHAR(10), @duration) + ' | Lines: ' + CONVERT(VARCHAR(10), @count)
--- <- Tally solution
--- ==========================================================
 
DELETE FROM @result  --- Clean up

Start tally solution
Milliseconds: 5453 | Lines: 28145
Start Tally-function 3 solution (correct line-splitting)
Milliseconds: 3376 | Lines: 28545
Start Tally-function 4 solution (has same bug as the rest)
Milliseconds: 3316 | Lines: 28145
Start cursor solution
Milliseconds: 1950 | Lines: 28145
Start clr xml solution
Milliseconds: 773 | Lines: 28545
Start clr tvf (Paul White) solution
Milliseconds: 1186 | Lines: 28545

DECLARE @tally TABLE (N INT NOT NULL, PRIMARY KEY CLUSTERED (N))
DECLARE @source TABLE (name NVARCHAR(128), definition VARCHAR(8000))
DECLARE @result TABLE (line nvarchar(max))

SELECT @crlf = CHAR(13) + CHAR(10)

-- //////////////////////////////////////////////////////////
-- -> Test data and tally table

-- Get some system procedures to split into lines
INSERT INTO @source
-- SELECT TOP(200) o.name, @crlf + m.definition + @crlf
SELECT TOP(200) o.name, @crlf + LEFT(m.definition, 7996) + @crlf
FROM master.sys.all_objects o
JOIN master.sys.all_sql_modules m ON o.object_id = m.object_id
WHERE type = 'P'

Start tally solution
Msg 537, Level 16, State 2, Line 37
Invalid length parameter passed to the LEFT or SUBSTRING function.
The statement has been terminated.
Milliseconds: 13 | Lines: 0
Start Tally-function 3 solution (correct line-splitting)
Milliseconds: 2453 | Lines: 21115
Start Tally-function 4 solution (has same bug as the rest)
Milliseconds: 2380 | Lines: 20715
Start cursor solution
Milliseconds: 1466 | Lines: 20715
Start clr xml solution
Milliseconds: 590 | Lines: 21115
Start clr tvf (Paul White) solution
Milliseconds: 880 | Lines: 21115