Dung Tran Dung Tran - 7 days ago 5
SQL Question

SQL Server: Correct Estimation of Rows from String Split Function

I am using SQL Server 2014. I have a multi-statement table value function for splitting a string into a table via a delimiter.

I am not splitting long strings but I use this function in stored procedures like this:

ALTER PROCEDURE dbo.example
@parameters
AS
Begin
SELECT *
FROM TableA
LEFT JOIN Table B on B.ID = A.FID
WHERE B.ID IN (SELECT Data FROM dbo.fn_Split(@parameters, ',')
END


The real sproc has multi-joins and many parameters. When I looked at the execution plan, the operator Table Scan (fn_split) Cost 0% always returned an inaccurate estimation of rows. For 11 parameters, it will estimated 100 rows.

I heard multi-statement table value function are slow, but using inline,
XML, or Jeff Moden's splitter are slower that my orginial one when used in the WHERE clause. They have horrible execution plans and worst estimations of rows

Is there a way to get correct estimation of rows when splitting a string into a table?

My function:

ALTER FUNCTION dbo.fn_Split(
@RowData NVARCHAR(MAX),
@Delimeter NVARCHAR(MAX)
)
RETURNS @RtnValue TABLE (
ID INT IDENTITY(1,1),
Data NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @Iterator INT
SET @Iterator = 1
DECLARE @FoundIndex INT
SET @FoundIndex = CHARINDEX(@Delimeter,@RowData)
WHILE (@FoundIndex>0)
BEGIN
INSERT INTO @RtnValue (data)
SELECT
Data = LTRIM(RTRIM(SUBSTRING(@RowData, 1, @FoundIndex - 1)))
SET @RowData = SUBSTRING(@RowData,
@FoundIndex + DATALENGTH(@Delimeter) / 2,
LEN(@RowData))
SET @Iterator = @Iterator + 1
SET @FoundIndex = CHARINDEX(@Delimeter, @RowData)
END
INSERT INTO @RtnValue (Data)
SELECT Data = LTRIM(RTRIM(@RowData))
RETURN
END

Answer

How about using a temporary table?

Begin
    create table #split (data varchar(255) primary key);

    insert into #split(data)
        select data
        from dbo.fn_Split(@parameters, ',');

    SELECT *
    FROM TableA LEFT JOIN
         Table B
         on B.ID = A.FID
    WHERE B.ID IN (SELECT Data FROM #split);

END;
Comments