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

Публикация № 656224

Администрирование - Оптимизация БД (HighLoad)

замена подстроки в строке MS SQL оптимизация запросов регулярные выражения в MS SQL регулярные выражения CLR CLR-библиотека Table Scan

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

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

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

Несгруппированные строки трассировки

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

В этом варианте мне сразу не понравилось использование циклов для каждой строки. Я, конечно, рассматривал цикл, но только в самом последнем безнадежном случае. Теперь, когда сам сделать что-то устраивающее не смог, а официальное решение не совсем оптимальное, задал этот же вопрос на форуме: 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:

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

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

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

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

use PerformanceAnalises

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

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

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

Типовая замена "#tt... "

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

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

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

Замена "#tt... " регулярным выражением

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

Выводы:

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

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

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

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

5

См. также

Специальные предложения

Вознаграждение за ответ
Показать полностью
Комментарии
Избранное Подписка Сортировка: Древо
1. starik-2005 1864 01.08.17 20:42 Сейчас в теме
2. info1i 31 23.09.17 20:56 Сейчас в теме
Сделал регулярное выражение универсальнее, оптимизировав тем самым замену:
upd ate tr1 set TextData = ext.Regex_Replace(TextData, '#tt[0-9]+ ', 'TempTable ', '')

И вариант с заменой параметров, лишних "exec...":
update tr1 se t TextData =
ext.Regex_Replace(
	ext.Regex_Replace(
		ext.Regex_Replace(
			ext.Regex_Replace(TextData, 'exec sp_executesql N''', '', '')
			,'#tt[0-9]+ ','TempTable ', '')
		,' [0-9]+x[0-9]+ ',' @p ', '')
	,' [0-9]+x[0-9]+[A-F]+ ',' @p ', '')
3. МихаилМ 23.09.17 21:55 Сейчас в теме
автор молодец. но эта задача в выигрыше 10 секунд. не так интересна.
тк 1с разбивает запросы на части сохраняя промежуточные значения в временные таблицы.
и задача объединить эти запросы для выявления часто повторяющихся запросов значительно более ресурсоёмкая.
4. МихаилМ 23.09.17 22:26 Сейчас в теме
+(3)
но если номализованным текстам запросов сопоставить хэш - то задача объединения тестов сильно упростится.
Оставьте свое сообщение