Has anyone tried variations on 8K Splitter?

We have managed to complete the 10000 rows, 1133 elements i.e. near to 8k characters performance test (described in the above-mentioned pointer) in 19 secs approximately. Which is quite a drastic improvement for us.

The major change was the conversion of tally table column's data type from BIGINT to INT. Another change (which shows marginally increased performance) was to add pre-calculated column which have the value for N + 1.

Following performance test includes the two versions we have come up with

USE [tempdb]
GO
SET NOCOUNT ON;
GO
IF OBJECT_ID('dbo.CsvTest') IS NOT NULL
DROP TABLE CsvTest
GO

DECLARE @NumberOfElements INT,
       @NumberOfRows INT


/*======== PARAMETER VALUES ==============================*/
SELECT @NumberOfElements    = 1133
,      @NumberOfRows   = 10000
/*========================================================*/


SELECT TOP (@NumberOfRows) --Controls the number of rows in the test table        
    ISNULL(ROW_NUMBER() OVER (ORDER BY(SELECT NULL)),0) AS RowNum,
    (
        SELECT CAST(STUFF( --=== STUFF get's rid of the leading comma                
                ( --=== This builds CSV row with a leading comma                 
                SELECT TOP (@NumberOfElements) --Controls the number of CSV elements in each row                        
                    ','+CAST(ABS(CHECKSUM(NEWID()))%100000 AS VARCHAR(10))
                FROM sys.All_Columns t3      --Classic cross join pseudo-cursor                  
                CROSS JOIN sys.All_Columns t4 --can produce row sets up 121 million.                 
                WHERE t1.Object_ID <> t3.Object_ID --Without this line, all rows would be the same                    
                FOR XML PATH('')
                )
                ,1,1,'') AS VARCHAR(8000))
                ) AS Csv
INTO CsvTest
FROM sys.All_Columns t1      --Classic cross join pseudo-cursor
   CROSS JOIN sys.All_Columns t2 --can produce row sets up 16 million rows

GO
ALTER TABLE CsvTest
    ADD PRIMARY KEY CLUSTERED (RowNum) WITH FILLFACTOR = 100;
    GO
     IF OBJECT_ID('dbo.Tally','U') IS NOT NULL
        DROP TABLE dbo.Tally;

--===== Create and populate the Tally table on the fly.
     -- This ISNULL function makes the column NOT NULL
 SELECT TOP 11000
        IDENTITY(INT,1,1) AS N, 
        ISNULL(ISNULL(CAST(ROW_NUMBER() OVER ( ORDER BY (SELECT NULL)) AS INT),0)+ 1, 0) N1
   INTO dbo.Tally
   FROM sys.all_columns ac1
  CROSS JOIN sys.all_columns ac2
;
GO

--===== Add a CLUSTERED Primary Key to maximize performance
  ALTER TABLE dbo.Tally
    ADD CONSTRAINT PK_Tally_N 
        PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
;
--===== Update Stats
UPDATE STATISTICS dbo.Tally WITH FULLSCAN
;
GO

--=====================================================================================================================
--      Conditionally drop and recreate the Split8KTally_With_Column_N1 splitter function.
--=====================================================================================================================
IF OBJECT_ID(N'dbo.Split8KTally_With_Column_N1', N'IF') IS NOT NULL
DROP FUNCTION dbo.Split8KTally_With_Column_N1;
GO
--===== Tally Table (Split8KTally_With_Column_N1 ITVF) ===============================================================================
/*===== THIS FUNCTION USES COLUMN "N1" OF TALLY TABLE HAVING PRE-CALCULATED VALUES FOR N+1 ===========*/
CREATE FUNCTION dbo.Split8KTally_With_Column_N1 (
    @pString VARCHAR(8000), @pDelimiter VARCHAR(1)
)
RETURNS TABLE 
AS
RETURN
    /*======= FIND THE FIRST DELIMITED ITEM EXPLICITLY, AS WE SKIPPED THE FIRST ITEM TO AVOID "OR" CONDITION IN THE WHERE CLAUSE=====*/
    SELECT ItemNumber = CAST(0 AS BIGINT) --STARTING WITH ZERO FOR SORT AND TO AVOID ROW_NUMBER + 1 LATER
    ,       ItemValue = SUBSTRING(@pString, 1,
                             ISNULL(NULLIF(CHARINDEX(@pDelimiter,
                                          @pString COLLATE Latin1_General_BIN), 0)
                                    - 1, 8000 ))

            UNION ALL   -- CONCATENATE THE OUTPUTS
    /*======= FIND REST OF THE DELIMITED ITEMS USING TALLY TABLE  ======================================*/
            SELECT ItemNumber = ROW_NUMBER() OVER ( ORDER BY [N] )
    ,       ItemValue = SUBSTRING(@pString, [N1],
                             ISNULL(NULLIF(CHARINDEX(@pDelimiter ,
                                          @pString COLLATE Latin1_General_BIN, [N1]), 0)
                                    - [N1], 8000) )
            FROM     [dbo].[Tally] WITH ( NOLOCK ) --NOLOCK HINT IS NOT NECESSARY
            WHERE    [N] BETWEEN 1 AND LEN(@pString) 
            AND      SUBSTRING(@pString, [N], 1)  = @pDelimiter  

GO
--=====================================================================================================================
--      Conditionally drop and recreate the Split8KTally_With_N_PLUS_1 splitter function.
--=====================================================================================================================
IF OBJECT_ID('dbo.Split8KTally_With_N_PLUS_1','IF') IS NOT NULL
DROP FUNCTION [dbo].Split8KTally_With_N_PLUS_1
GO
--===== Tally Table (Split8KTally_With_N_PLUS_1 ITVF) ===============================================================================
/*===== THIS FUNCTION USES RUNTIME CALCULATION "N + 1" INSTEAD OF USING THE PRE-CALCULATED COLUMN "N1" OF THE TALLY TABLE===========*/
CREATE FUNCTION dbo.Split8KTally_With_N_PLUS_1 (
    @pString VARCHAR(8000), @pDelimiter VARCHAR(1)
)
RETURNS TABLE 
AS
RETURN

    /*======= FIND THE FIRST DELIMITED ITEM EXPLICITLY, AS WE SKIPPED THE FIRST ITEM TO AVOID "OR" CONDITION IN THE WHERE CLAUSE=====*/
    SELECT ItemNumber = CAST(0 AS BIGINT) --STARTING WITH ZERO FOR SORT AND TO AVOID ROW_NUMBER + 1 LATER
    ,       ItemValue = SUBSTRING(@pString, 1,
                             ISNULL(NULLIF(CHARINDEX(@pDelimiter,
                                          @pString COLLATE Latin1_General_BIN), 0)
                                    - 1, 8000 ))

            UNION ALL -- CONCATENATE THE OUTPUTS
    /*======= FIND REST OF THE DELIMITED ITEMS USING TALLY TABLE  ======================================*/
            SELECT ItemNumber = ROW_NUMBER() OVER ( ORDER BY [N] )
    ,       ItemValue = SUBSTRING(@pString, (N+1),
                             ISNULL(NULLIF(CHARINDEX(@pDelimiter ,
                                          @pString COLLATE Latin1_General_BIN, (N+1)), 0)
                                    - (N+1), 8000) )
            FROM     [dbo].[Tally] WITH ( NOLOCK )--NOLOCK HINT IS NOT NECESSARY
            WHERE    [N] BETWEEN 1 AND LEN(@pString) 
            AND      SUBSTRING(@pString, [N], 1)  = @pDelimiter  

GO

--Tally Test
PRINT '/*====== dbo.Split8KTally_With_N_PLUS_1 =================*/'
DBCC FREEPROCCACHE WITH NO_INFOMSGS
SET STATISTICS TIME ON
DECLARE @ItemNumber BIGINT
,   @Item VARCHAR(8000) ;
SELECT  @ItemNumber = V.ItemNumber
,       @Item = V.ItemValue
FROM    dbo.CsvTest D
        CROSS APPLY dbo.Split8KTally_With_N_PLUS_1(D.Csv, ',') V
SET STATISTICS TIME OFF
PRINT '/*========================================================*/'
PRINT CHAR(10) + CHAR(13)
GO
PRINT '/*====== dbo.Split8KTally_With_Column_N1 =================*/'
DBCC FREEPROCCACHE WITH NO_INFOMSGS
SET STATISTICS TIME ON
DECLARE @ItemNumber BIGINT
,   @Item VARCHAR(8000) ;
SELECT  @ItemNumber = V.ItemNumber
,       @Item = V.ItemValue
FROM    dbo.CsvTest D
        CROSS APPLY dbo.Split8KTally_With_Column_N1(D.Csv, ',') V
SET STATISTICS TIME OFF
PRINT '/*========================================================*/'
PRINT CHAR(10) + CHAR(13)
GO

As I said, we did some testing on VARCHAR(MAX) versions as well. As tally table solution does not behave well with wider strings. Two of them performed really well

1. Inline Recursive CTE solution (Split_RCTE, Tweaked a little bit for better performance)
2. TVF XML solution by Oleg (Split_XML_Solution_By_Oleg)

But surprisingly, RCTE was winner on some occasions with good margin as compared to losing. If I do the same 10000 rows, 1133 elements with 125-150 characters width, RCTE beat the XML solution marginally. But if the numbers of elements are decreased to 500, the RCTE was almost twice as fast as XML. But I would not count out any of the solutions, as they could fit according the requirement and environment.

For RCTE solution, I must say it is more resource intensive. The index spool and SORT operators in the execution plan clearly indicates that it would hit the memory, tempdb and processors hard. It also reflects that with more CPUs, memory and capacity planned tempdb, the solution is viable to a better performance as compared to other solutions. Having said that, I still may tilt towards the XML solution.

To me, why the RCTE performs much better than the tally table solution (both used the Charindex and Substring), is because the tally table solution compare each character with the delimiter, and due to the Out of Row phenomenon, this does not scale well. Whereas, RCTE solution does it for only the required number of times. Moreover, till VARCHAR(8000), the

SUBSTRING(@pString, N,1) = @pDelimiter

is dealt as a predicate in addition to the Seek predicate. But for VARCHAR(MAX), this is divided into two steps i.e. A Seek predicate followed by a Filter predicate which decreases the performance quite a bit.

Now the performance test for RCTE and XML solutions

USE [tempdb]
GO
SET NOCOUNT ON;
GO
IF OBJECT_ID(N'dbo.iFunction', N'V') IS NOT NULL
DROP VIEW iFunction
GO

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.iFunction AS 
/**********************************************************************************************************************
 Purpose:
 This view is callable from UDF's which allows us to indirectly get a NEWID() within a function where we can't do such
 a thing directly in the function.  This view also solves the same problem for GETDATE().

 Usage:
 SELECT MyNewID FROM dbo.iFunction; --Returns a GUID
 SELECT MyDate  FROM dbo.iFunction; --Returns a Date


 Revision History:
 Rev 00 - 06 Jun 2004 - Jeff Moden - Initial creation
 Rev 01 - 06 Mar 2011 - Jeff Moden - Formalize code.  No logic changes. 
**********************************************************************************************************************/
SELECT MyNewID = NEWID(),
       MyDate  = GETDATE();
GO

IF OBJECT_ID(N'dbo.CsvTest', N'U') IS NOT NULL
DROP TABLE CsvTest
GO

DECLARE @MaxElementWidth INT, 
 @MinElementWidth INT, 
 @NumberOfElements INT,
 @NumberOfRows INT


/*======== PARAMETER VALUES ==============================*/
SELECT @MaxElementWidth = 150 
,    @MinElementWidth = 125 
,    @NumberOfElements = 500
,    @NumberOfRows = 10000
/*========================================================*/


SELECT TOP (@NumberOfRows) --Controls the number of rows in the test table
        ISNULL(ROW_NUMBER() OVER (ORDER BY(SELECT NULL)),0) AS RowNum,
        CSV =
        (--==== This creates each CSV
         SELECT CAST(
                    STUFF( --=== STUFF get's rid of the leading comma
                         ( --=== This builds CSV row with a leading comma
                          SELECT TOP (@NumberOfElements) --Controls the number of CSV elements in each row
                                 ','
                               + LEFT(--==== Builds random length variable within element width constraints
                                      LEFT(REPLICATE('1234567890',CEILING(@MaxElementWidth/10.0)), @MaxElementWidth),
                                      ABS(CHECKSUM((SELECT MyNewID FROM dbo.iFunction)))
                                            % (@MaxElementWidth - @MinElementWidth + 1) + @MinElementWidth
                                     )
                            FROM sys.All_Columns ac3            --Classic cross join pseudo-cursor
                           CROSS JOIN sys.All_Columns ac4       --can produce row sets up 16 million.
                           WHERE ac3.Object_ID <> ac1.Object_ID --Without this line, all rows would be the same.
                             FOR XML PATH('')
                         )
                    ,1,1,'')
                AS VARCHAR(MAX))
        )
   INTO CsvTest
   FROM sys.All_Columns ac1      --Classic cross join pseudo-cursor
   CROSS JOIN sys.All_Columns ac2 --can produce row sets up 16 million rows

GO
PRINT '/*====== dbo.CSVTest Population completed ================*/'
PRINT CHAR(10) + CHAR(13)
GO
ALTER TABLE CsvTest
    ADD PRIMARY KEY CLUSTERED (RowNum) WITH FILLFACTOR = 100;
    GO

IF OBJECT_ID(N'dbo.Split_RCTE', N'IF') IS NOT NULL
DROP FUNCTION dbo.Split_RCTE
GO
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
--Create Split_RCTE function VARCHAR(MAX) version
CREATE FUNCTION dbo.Split_RCTE
    (
     @pString VARCHAR(MAX)
    ,@pDelimiter VARCHAR(1)
    )
RETURNS TABLE
    WITH SCHEMABINDING
AS
RETURN
    WITH    cteSplit
              AS ( SELECT   StartPosition = 0
                   ,        EndPosition = CONVERT(INT, CHARINDEX(@pDelimiter,
                                                              @pString COLLATE Latin1_General_BIN))
                   UNION ALL
                   SELECT   StartPosition = EndPosition + 1
                   ,        EndPosition = CONVERT(INT, CHARINDEX(@pDelimiter,
                                                   @pString COLLATE Latin1_General_BIN,
                                                   EndPosition + 1))
                   FROM     cteSplit
                   WHERE    EndPosition > 0
                 )
    SELECT  [ItemNumber] = ROW_NUMBER() OVER ( ORDER BY StartPosition )
    ,       SUBSTRING(@pString, StartPosition,
   CASE EndPosition
 WHEN 0 THEN CONVERT(INT, LEN(@pString)) + 1
 ELSE EndPosition - StartPosition
   END) ItemValue
    FROM    cteSplit
GO

IF OBJECT_ID(N'dbo.Split_XML_Solution_By_Oleg', N'TF') IS NOT NULL
DROP FUNCTION dbo.Split_XML_Solution_By_Oleg
GO

--Create Split_XML function VARCHAR(MAX) version

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.Split_XML_Solution_By_Oleg
    (
     @Parameter VARCHAR(MAX)
    ,@Delimiter VARCHAR(1)
    )
RETURNS @Result TABLE
    (
     ItemNumber INT
    ,ItemValue VARCHAR(MAX)
    )
AS 
    BEGIN
        DECLARE @XML XML ;
        SET @Parameter = ( SELECT   @Parameter
                         FOR XML PATH('')
                         ) ;
        SELECT  @XML = '<r>' + REPLACE(@Parameter, @Delimiter, '</r><r>') + '</r>' ;

        INSERT  INTO @Result
                (
                 ItemNumber
                ,ItemValue
                )
                SELECT  ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL) ) AS ItemNumber
                ,       Item.value('text()[1]', 'VARCHAR(MAX)') AS ItemValue
                FROM    @XML.nodes('//r') R ( Item ) ;
        RETURN ;
    END ;
GO
PRINT '/*====== dbo.Split_RCTE ==================================*/'
DBCC FREEPROCCACHE WITH NO_INFOMSGS
SET STATISTICS TIME ON
DECLARE @ItemNumber BIGINT
,   @Item VARCHAR(MAX) ;
SELECT  @ItemNumber = V.ItemNumber
,       @Item = V.ItemValue
FROM    dbo.CsvTest D
        CROSS APPLY dbo.Split_RCTE(D.Csv, ',') V
OPTION  ( MAXRECURSION 0 )
SET STATISTICS TIME OFF
PRINT '/*========================================================*/'
PRINT CHAR(10) + CHAR(13)
GO
PRINT '/*====== dbo.Split_XML ===================================*/'
DBCC FREEPROCCACHE WITH NO_INFOMSGS
SET STATISTICS TIME ON
DECLARE @ItemNumber BIGINT
,   @Item VARCHAR(MAX) ;
SELECT  @ItemNumber = V.ItemNumber
,       @Item = V.ItemValue
FROM    dbo.CsvTest D
        CROSS APPLY dbo.Split_XML_Solution_By_Oleg(D.Csv, ',') V
SET STATISTICS TIME OFF
PRINT '/*========================================================*/'
PRINT CHAR(10) + CHAR(13)
GO

As always, everyone's mileage may differ. Please note that testing was done on SQL Server 2005. For SQL 2008, the conversion to INT may not be needed in RCTE solution.