SQL-Ex blog

Стратегия тестирования

Поскольку длина списка часто является проблемой при разбиении или соединении элементов, соединялось и измерялось различное число записей. Чтобы минимизировать влияние на ввод/вывод, была выбрана относительно небольшая таблица, и перед выполнением каждого метода чистился кэш. Поскольку каждое отдельное время выполнения было очень мало, сам код был помещен в цикл из 10000 итераций. Число тестовых записей, которые соединялись в строку, было следующим: 225, 450, 675, 900 и 1125. Код цикла показан ниже.

declare @NumRows int = 1125 -– число строк для соединения

declare @strSeparator nvarchar(20) = ' or '

declare @MaxLoop int = 10000

declare @LoopInx int = 0

declare @strFilter nvarchar(max) = ''

set nocount on

while @LoopInx <= @MaxLoop 

begin

-– Сюда помещается код соединения строк

	set @LoopInx += 1

end

set nocount off

go

Каждый тест выполнялся в собственном пакете на ничем другим не занятой системе. Производительность в реальном времени измерялась с помощью сессии Extended Events, которая была сконфигурирована ТОЛЬКО для захвата событий sqlserver.sql_batch_completed с целью минимизации накладных расходов. Определение сессии, код запуска и останова показаны ниже. Эта сессия собирает информацию для завершенных пакетов, которые выполнялись дольше 10 микросекунд и были исполнены на базе данных #7.

use master;

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='SQLRxPerformanceMonitoring')

    DROP EVENT session SQLRxPerformanceMonitoring ON SERVER

GO

 -- заменить database_id списком

CREATE EVENT SESSION SQLRxPerformanceMonitoring ON SERVER 

ADD EVENT sqlserver.sql_batch_completed(

SET collect_batch_text=(1)

ACTION(sqlserver.server_instance_name, sqlserver.database_id, sqlserver.session_id, sqlserver.client_pid, sqlserver.client_app_name, sqlserver.database_name, sqlserver.server_principal_name, sqlserver.client_hostname, sqlserver.tsql_frame, sqlserver.tsql_stack, sqlserver.sql_text, sqlserver.request_id, sqlserver.query_hash, sqlserver.query_plan_hash, package0.event_sequence)

    	WHERE ([duration] > (10) and (database_id = 7)) -- значения должны быть явно закодированы

) -- заменить database_id

ADD TARGET package0.event_file(SET filename= N'E:\SQLRx\SQLScriptOutput\DevPerf.xel')

WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=5 SECONDS, MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=OFF)

GO

-- запись существует, когда сессия активна

if not exists(select * from [master].[sys].[dm_xe_sessions] where [name] = 'SQLRxPerformanceMonitoring')

	alter EVENT SESSION SQLRxPerformanceMonitoring ON SERVER STATE = START

-- запись существует, когда сессия активна

if exists(select * from [master].[sys].[dm_xe_sessions] where [name] = 'SQLRxPerformanceMonitoring')

	alter EVENT SESSION SQLRxPerformanceMonitoring ON SERVER STATE = STOP

Код

Для тестирования использовались два различных метода T-SQL. Решение FOR XML PATH сочетает функции REVERSE и STUFF с FOR XML PATH. Решение String_agg много проще, но доступно только начиная с версии SQL Server 2017.

Метод FOR XML PATH был с годами основательно задокументирован во многих статьях, поэтому я не буду повторяться, объясняя его здесь. Однако о двух вещах полезно упомянуть. Во-первых, строка @strSeparator (в данном случае ' or ') будет располагаться в конце конструируемой строки (в данном случае @strFilter). Поскольку последнее вхождение @strSeparator требуется убрать во избежание синтаксической ошибки, символы должны быть удалены с конца строки. Хотя кажется очевидным, что следует удалить четыре символа (длина @strSeparator), фактически требуется удалить только три, поскольку сконструированная строка заканчивается на "r", а не " ". Следовательно, аргумент длины спецификации функции LEFT есть длина @strFilter минус три.

Второй момент связан со скобками вокруг оператора SELECT. Они могут показаться необязательными, однако они важны, т.к. без них возникают следующие ошибки:

Msg 156, Level 15, State 1, Line 26

Incorrect syntax near the keyword 'select'.



Msg 102, Level 15, State 1, Line 28

Incorrect syntax near ')'.

Второй метод, String_agg, много проще для применения, но имеет ограничение, что показано ниже:

Msg 9829, Level 16, State 1, Line 62

STRING_AGG aggregation result exceeded the limit of 8000 bytes. 

Use LOB types to avoid result truncation.

Ограничение можно обойти, преобразовав столбец [Full Table Name] к типу nvarchar(max), поэтому общая длина строки теперь не имеет значения.

Код для обоих методов показан ниже:

-- FOR XML PATH

select @strFilter = reverse( stuff( reverse(

	(select 'FullTableName = ' + '''' + [Full Table Name] + '''' +

@strSeparator as [text()]

from IndexColumnAnalysisList 

where RecID <= @NumRows for xml path('')

)

) -- первый reverse

	,1,1,'') -- stuff

	) -- второй reverse

Set @strFilter = left(@strFilter, len(@strFilter) - 3) –- удаление последнего ‘ or ’

-- FOR XML PATH

-- String_agg

select @strFilter = string_agg('FullTableName = ' + '''' + 

cast([Full Table Name] as nvarchar(max)) + '''', @strSeparator)

	from IndexColumnAnalysisList

	where RecID <= @NumRows

–- String_agg

Результаты тестов

Результаты тестов показаны ниже на Рис.1. Ясно, что разница между двумя методами была очень мала, хотя число элементов непосредственно влияло на производительность обоих методов. Длительность была равна времени процессора и пропорциональна числу соединяемых записей. Планы запросов для двух методов были довольно похожи и показаны на Рис.2 и Рис.3 соответственно.


Рис.1: Среднее время выполнения


Рис.2: План выполнения FOR XML PATH


Рис.3: План выполнения String_agg

Заключение

Итак, нет необходимости заменять существующий код T-SQL. Однако по возможности для новой разработки на SQL Server 2017 или выше следует использовать функцию String_agg для упрощения, улучшения читабельности и облегчения сопровождения.