Оптимизация удаления с проверкой ссылочной целостности

Как известно операция удаления документов с проверкой ссылочной целостности достаточно длительная, т.к. серверу данных (далее ТБ.Сервер) требуется проконтролировать нет ли ссылок на удаляемые документы, что приводит к выдачи на СУБД дополнительных агрегирующих запросов. Ситуация ещё больше усугубляется на большом объёме данных.

В последних версиях ТБ.Сервера проблема частично решена тем, что если в транзакции присутствуют только документы на удаление с проверкой ссылок, то блок проверки системных и пользовательских ограничений а так же сама проверка ссылок выведен в «не блокирующую» секцию, что позволило не блокировать других пользователей во время проведения данной операции.

Так же существует возможность ускорить саму проверку ссылочной целостности для MS SQL. Особенно это актуально для подтаблицы «Процесс.Позиции». Для этого нужно добавить индексы на каждое ссылочное поле «Процесс.Позиции», которое ссылаются на «Процесс». Это можно сделать через «Просмотр записей». Важно отметить, что должны быть проиндексированы все ссылочные поля в «Процесс.Позиции» ссылающиеся на «Процесс». В противном случае оптимизатор MS SQL не будет использовать индексы и ускорения не произойдет. Так же оптимизатор MS SQL сочтёт невозможным использование индексов в том случае, если в транзакции на удаление больше 4-х записей, каждая из кот. содержит много ссылочных полей на один и тот же класс записи. Это как раз характерно для «Процесс.Позиции», который содержит от 15 и более ссылочных полей на «Процесс».

Для решения данной проблемы в последних версиях ТБ.Сервера при проверке ссылок сервер определяет, что если в записи все необходимые поля проиндексированы, то проверка будет осуществлять по одной записи. Увеличение объема базы после индексации полей в «Процесс.Позиции» составляет приблизительно 200 МБ на 1 млн. записей.

Для удобства «тюнинга» проверки ссылочной целостности в отладочную версию добавлены сообщения:
«Check safe delete - field(s) not have index: <список полей>» - Показывает список полей, по которым производится проверка ссылок, а индексов нет.
«Check safe delete - all fields indexed…» - Необходимые поля проиндексированы. Проверка будет осуществляться по одной записи.

Для выключения описанного выше режима в DBTypes.ini имеется опция OptimCheckCanSafeDelete расположенная в основной секции соответствующего базового драйвера СУБД. По умолчанию данный режим включен (OptimCheckCanSafeDelete=1).

На базе в 2 млн. записей в «Процесс» и 9 млн. записей в «Процесс.Позиции» (система 64-х разрядная, 2-х процессорная. 6 ГБ оперативной памяти. MS SQL 2005 EE 64-bit) раньше удаление 5 записей с проверкой ссылочной целостности занимало 287594 ms. После индексации ссылочных полей на «Процесс» в самой «Процесс» и «Процесс.Позиции», а так же включение опции OptimCheckCanSafeDelete=1 удаление тех же 5 записей заняло 4781 ms. Ускорение составило приблизительно 60 раз!

 
doc/mssql_tune_checkcansafedelete.txt · Последние изменения: 2016/04/15 15:26 (внешнее изменение)
 
За исключением случаев, когда указано иное, содержимое этой вики предоставляется на условиях следующей лицензии:CC Attribution-Noncommercial-Share Alike 3.0 Unported
Recent changes RSS feed Donate Powered by PHP Valid XHTML 1.0 Valid CSS Driven by DokuWiki