Блог

Главная - Вопросы по программированию - Параметры, оцениваемые таблицей, объединяют производительность

Параметры, оцениваемые таблицей, объединяют производительность

#sql-server #tsql #stored-procedures #sql-execution-plan #sqlperformance

#sql-сервер #tsql #хранимые процедуры #sql-execution-plan #производительность sql

Вопрос:

Я хочу передать список имен в хранимую процедуру, а затем выполнить объединение по левому краю. Я передал список имен в качестве табличного параметра.

 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

Определяемый пользователем тип таблицы:

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

SQL Server не ведет статистику по табличным параметрам, повлияет ли это на производительность объединения в приведенном выше случае. Если производительность низкая, могу ли я передать список имен в виде строки, разделенной запятыми, и написать функцию для разделения и возврата таблицы в хранимую процедуру?

 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

или

 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

Комментарии:

1. Попробуйте и убедитесь — не спрашивайте нас. Лично я бы сделал это правильным способом (используя параметр с табличным значением) и оптимизировал бы производительность только в том случае, если это становится проблемой. Вы можете преждевременно потратить много времени, пытаясь предсказать производительность SQL Server.

2. @DaleK согласен, правильный способ сделать это — использовать параметр, оцениваемый в таблице. Поскольку SQL Server не ведет статистику по табличным параметрам, повлияет ли это на производительность объединения?

3. Как я уже сказал, попробуйте и посмотрите… сколько строк вы передаете?

4. @DaleK <1000 строк за раз.

5. Объявите ограничение первичного ключа для типа таблицы. Даже если у него нет статистики для базового индекса, он предоставляет оптимизатору информацию о мощности, которая может быть полезной.

Ответ №1:

Использовать STRING_SPLIT лучше, чем использовать XML PATH для разделения. Если вы можете использовать STRING_SPLIT , вы можете использовать JSON PATH .

Используя JSON PATH , преобразуйте JSON variable в набор строк и вставьте его #temporary table , а не @table variable как в зависимости от версии вашего SQL Server, #temporary tables выполняется лучше при обработке большого объема данных.

Кроме того, если вы хотите добавить новые поля в JSON variable , не будет необходимости редактировать table type . При редактировании table type затруднено из-за ссылок.

Ответ №2:

ВЫБОР, в котором вы используете табличный параметр, довольно прост. Это просто объединение, и учитывая, что мощность для табличных переменных вычисляется как единица, предполагается, что dbo.Employee.Имя проиндексировано, и типы столбцов совпадают, это объединение будет реализовано с помощью объединения в цикл, которое является самым быстрым вариантом для этого случая.

Просто убедитесь, что dbo.Employee.Имя правильно проиндексировано.