Оптимизированная замена подстроки в строке с помощью регулярных выражений в MS SQL Server

В MS SQL Server отсутствуют регулярные выражения. Как включить их использование? Замена подстроки в строке выполняется долго. Как сократить время выполнения замены? Для выявления тяжелых запросов часто используют данные трассировки. Чтобы получить суммированные данные, необходимо сгруппировать одинаковые запросы, а из-за разных имен временных таблиц они не группируются. Статья - пример замены имен временных таблиц в результатах трассировки.


Для выявления тяжелых запросов часто используют данные трассировки. Подробнее можно узнать в статье: //infostart.ru/public/303656/ как собрать данные трассировки. Что ж, данные собрали, в таблицу загрузили. Выполняем запрос с группировкой по полю Duration для выявления наидлительнейших запросов:

SELECT

substring([TextData], 1, 1000) sqltext

,Sum([Duration]) DurationTotal

,Sum([Duration]/1000) DurationInSecondsTotal

,Sum([Duration]/(1000*60*60)) DurationInHoursTotal

,sum([CPU]) CPUTotal

,sum([Reads]) ReadsTotal

FROM trace06072017

where TextData is not null and Duration <> 0

group by substring([TextData], 1, 1000)

order by DurationTotal Desc

Однако вот, что мы получаем:

В первых строках видно, что запросы одинаковые, но они не сгруппировались из-за разных имен временных таблиц #ttЧисло и параметров; замену параметров не рассматриваем, так как замена аналогична. Чтобы все же сгруппировать строки, необходимо заменить различающиеся подстроки одинаковыми. Сначала я сам взялся за обдумывание и попытки замены этих самых «#tt… », и не просто замены, а быстрой замены, но вскоре понял, что не все так просто, заменить-то можно, но быстро это никак не сделать — сканирование таблицы в любом случае. Эксперты 1С рекомендуют использовать функцию fn_GetSQLHash, приводящую строку к некоему хеш-виду, нам из нее необходима лишь часть, остальное удалим, поэтому я привожу только некоторый код из этой функции, который заменяет подстроку «#tt… »:

CREATE FUNCTION fn_GetSQLHash(@TSQL nvarchar(4000))  

RETURNS varchar(4000)  

AS  

BEGIN  

 DECLARE @TmpTableName nvarchar(4000)  

 DECLARE @i int  

  

 SET @TmpTableName = ''  

  

 IF (CHARINDEX('#', @TSQL) > 0)  

 BEGIN  

  SET @TmpTableName = SUBSTRING(@TSQL, CHARINDEX('#', @TSQL), 4000)  

  IF (CHARINDEX(' ', @TmpTableName) > 0)  

  BEGIN

    SET @TmpTableName = SUBSTRING(@TmpTableName, 1, CHARINDEX(' ', @TmpTableName) - 1)  

  END

 END  

 SET @TSQL = REPLACE ( @TSQL , @TmpTableName , 'TEMPTABLE')  

 RETURN(@TSQL);  

END

В этом варианте мне сразу не понравилось использование циклов для каждой строки. Я, конечно, рассматривал цикл, но только в самом последнем безнадежном случае. Теперь, когда сам сделать что-то устраивающее не смог, а официальное решение не совсем оптимальное, задал этот же вопрос на форуме: http://www.sql.ru/forum/1265293/kak-zamenit-tekst-mezhdu-dvumya-raznymi-simvolami

Предложенные ответы не решали полностью задачу, к тому же являлись неоптимальными. Однако один из ответов привел меня к решению, хотя и не универсальному — это использование CLR-библиотеки. По ответу, в интернете таких библиотек предостаточно, но мне хватило и той, что предложили: http://www.sql.ru/forum/1144247-a/faq-regex-parsim-zamenyaem-razbivaem-krutim-vertim

Итак, первым делом, проверяем, включена ли опция упрощенных пулов (lightweight pooling). Если включена, тогда надо разбираться, почему, нужна ли. Если обязательна, тогда на этом все закончено, придется использовать официальную функцию и забыть про регулярные выражения в MS SQL (некоторыми конструкциями регулярных выражений в like пренебрегаю, там все слишком ограничено не относится к нашей задаче). Подробнее: https://docs.microsoft.com/en-us/sql/relational-databases/clr-integration/clr-integration-enabling

Когда опция упрощенных пулов отключена, можно приступать к подключению CLR:

sp_configure 'show advanced options', 1;
go
reconfigure;
go
sp_configure 'clr enabled', 1;
go
reconfigure;
go 

Затем в контексте определенной базы, в которой необходимо работать с регулярными выражениями, выполняем скрипт install.sql

Еще нюанс: при выполнении подобных изменений в настройках предполагается, что у пользователя SQL-сервера есть достаточно прав, а именно sysadmin и serveradmin.













Теперь результаты.













Вариант типовой.

use PerformanceAnalises

UPDATE [trace060720172] SET [TextData] = dbo.fn_GetSQLHash(SUBSTRING([TextData], 1, 4000));

(строк обработано: 2481423) время: 00:37:39

План выполнения:

Вариант с регулярным выражением

use PerformanceAnalises

update tr1 set TextData = ext.Regex_Replace(TextData, '#tt[0-9]+ ', 'TempTable ', '')

(строк обработано: 2481423) время: 00:21:04

План выполнения:



Если у Вас подобный план запроса, значит, быстрее уже некуда.

Выводы:












Замена подстроки в строке в любом случае является неоптимальной операцией, т. к. выполняется сканирование таблицы — оператор Table Scan, даже при наличии индекса. Поэтому лучше всего, чтобы был только один Table Scan.


В MS SQL нет регулярных выражений — это минус. Но можно подключить CLR-библиотеку, реализующую возможность использования регулярных выражений.


Производительность использования регулярных выражений существенно выше.


Не всегда есть возможность подключения CLR-библиотек. Необходимы права sysadmin и serveradmin. Не поддерживается при использовании упрощенных пулов (lightweight pooling).



Оригинал статьи: https://infostart.ru/public/656224/