Table valued parameters join performance

Question

I want to pass a list of names to a stored procedure and then perform a left join. I have passed the list of names as a table-valued parameter.

CREATE PROCEDURE [DBO].[INSERTANDGETLATESTNAMES]
    (@list [dbo].[NamesCollection] READONLY)
AS
BEGIN
    INSERT INTO [dbo].[Employee](NAME)
    OUTPUT INSERTED.NAME
        SELECT NamesCollection.Name 
        FROM @list AS NamesCollection
        LEFT JOIN [dbo].[Employee] AS emp ON NamesCollection.Name = emp.Name 
        WHERE emp.Name IS NULL
END

User-defined table type:

CREATE TYPE [dbo].[NamesCollection] AS TABLE
                                       (
                                            [NAME] [varchar](50) NULL
                                       )
GO

SQL Server does not maintain statistics on table-valued parameters will that effect join performance in above case. If performance is slow then can I go for passing the list of names in comma separated string and write a function to split and return a table to the stored procedure?

CREATE FUNCTION split_string_XML
    (@in_string VARCHAR(MAX),
     @delimiter VARCHAR(1))
RETURNS @list TABLE(NAMES VARCHAR(50))
AS
BEGIN
    DECLARE @sql_xml XML = Cast('<root><U>'+ Replace(@in_string, @delimiter, '</U><U>')+ '</U></root>' AS XML)
    
    INSERT INTO @list(NAMES)
        SELECT f.x.value('.', 'VARCHAR(50)') AS NAMES
        FROM @sql_xml.nodes('/root/U') f(x)
        WHERE f.x.value('.', 'VARCHAR(50)') <> ''

    RETURN
END
GO

or

CREATE FUNCTION split_string_delimiter
    (@in_string VARCHAR(MAX),
     @delimiter VARCHAR(1))
RETURNS @list TABLE(NAME VARCHAR(50))
AS
BEGIN
    INSERT INTO @list(NAME)
        SELECT value AS NAMES
        FROM STRING_SPLIT(@in_string, @delimiter);

    RETURN
END
GO

Try it and see - don't ask us. Personally I would do it the *correct* way (using a table valued parameter) and only optimise for performance if it becomes an issue. You can waste a lot of time prematurely trying to predict the performance of SQL Server. — Dale K, Sep 16 '20 at 04:14
@DaleK agreed, the correct way of doing this is using a table valued parameter. As SQL server does not maintain statistics on table-valued parameters will it effect JOIN performance ? — Shani Bhati, Sep 16 '20 at 04:28
As I say, try it and see... how many rows are you passing in? — Dale K, Sep 16 '20 at 04:32
You hardly likely to have performance issues with that small a number of rows. — Dale K, Sep 16 '20 at 04:46
Declare a primary key constraint on the table type. Even though it doesn't have stats for the underlying index, it does provide the optimizer with cardinality info that may be useful. — Dan Guzman, Sep 16 '20 at 10:13

score 1 · Answer 1 · answered Sep 16 '20 at 05:33

Using STRING_SPLIT is better then using XML PATH for splitting. If you can use STRING_SPLIT, you can use JSON PATH.

Using JSON PATH, transform the JSON variable into row set and insert it #temporary table, not @table variable as depending on your SQL Server version, #temporary tables performed better when large amount of data is proceed.

Also, if you want to add new fields to the JSON variable there will be no need to edit the table type. While editing the table type is difficult because of referencing.

score 0 · Answer 2 · answered Sep 19 '20 at 00:23

The SELECT in which you're using the table-valued parameter is pretty straightforward. It's just a join and given that the cardinality for table variables is calculated as one, assuming that dbo.Employee.Name is indexed and that column types match, that join is going to be implemented with a loop join that is the quickest option for that case.

Just make sure that dbo.Employee.Name is properly indexed.

Table valued parameters join performance

2 Answers2