Оптимизация фильтрации по спискам |
|
В процессе работы с базой данных очень часто возникает задача отбора данных из регистров с фильтром по списку значений. Как правило, такая необходимость возникает в модулях проведения документов при выполнении временного расчета, во время построения отчетов с указанием в качестве одного из параметров фильтрации списка элементов справочника. Фильтрация, особенно, во время проведения документа позволяет снизить нагрузку на сервер и на сеть между сервером и клиентов за счет уменьшения объема передаваемых данных, а также, что немаловажно, ускорить проведение документа и тем самым снизить время внутри транзакции. Это большой плюс, который влечет за собой еще несколько положительных эффектов, таких как уменьшение времени ожидания пользователей при проведении документов, уменьшение средней длительности блокировок ввиду уменьшившегося времени прохождения транзакции, уменьшение вероятности взаимоблокировок. Фильтрация по единичному значению выполняется в 1С 7.7 для SQL простым условием равенства реквизита значению. Фильтрация по списку выполняется следующим образом:  В приведенном примере видна фильтрация и по единичному значению и по списку. Фильтрация по списку происходит по условию «IN» по которому указывается заранее подготовленная временная таблица, которая содержит в себе список элементов справочников для фильтрации. Данный тип запроса хорошо воспринимается SQL-сервером, тем более, что таблица фильтрации имеет первичный ключ, по совместительству кластерный индекс. Таким образом никаких проблем с построением плана запроса у сервера не возникает.
Все это хорошо, но если анализировать время проведения документов с установлением фильтра по списку элементов (например фильтр по списку товаров из табличной части документа) и без установки фильтра, то можно заметить интересную особенность. Устанавливаем фильтр по содержимому табличной части документа. Замеряем время с фильтром и без фильтра. Начинаем добавлять товары в табличную часть. Сначала, когда табличная часть содержит мало строк – быстрее происходит проведение с фильтром. Потом, по мере достижения определенного значения, примерно от 30 до 100 элементов (зависит от множества параметров, таких как количество элементов в справочнике, размера таблицы, количества уровней иерархии и т.д. и т.п.) – происходит выравнивание времени. Если продолжить добавлять строки, то быстрее будет проведение без фильтра. Для табличных частей с количеством строк от 200 и более – разница будет еще больше. Теперь пришло время рассмотреть ситуацию и понять, почему же так получается. Теоретически время выполнения запроса не должно сильно зависеть от количества строк в таблице фильтрации. По крайней мере, пока речь не идет о сотнях тысяч и миллионах строк в таблице фильтрации. Попробуем разобраться в этой ситуации. Для этого снимем трассировку с процесса проведения документа в котором используется фильтрация по списку значений. В трассировке процесса проведения при использовании списка фильтрации мы видим следующую ситуацию:  Сначала идет создание временной таблицы, которая будет использована для фильтрации, затем происходит цикл для каждого элемента списка значений, состоящий из создания временной хранимой процедуры, добавляющей элемент или иерархию элементов в таблицу фильтрации, выполнение процедуры с передачей ей в качестве параметра ID элемента, затем удаление процедуры.
Все логично за исключением необходимости создавать и удалять процедуру для каждого элемента справочника. Она не меняется. Создание процедуры отнимает некоторое время, необходимое для построения SQL-сервером плана запроса и компиляции процедуры, при удалении процедуры все начинается с начала. Логично было бы создать процедуру, выполнить ее нужное количество раз и только уже затем удалить. Исходя из этих выводов был разработан механизм перехвата и оптимизации обращений к временным процедурам, выражающийся в следующем: происходит анализ количества элементов в списке, создается процедура, затем выполняется для каждого элемента. Запросы на удаление и повторное создание процедуры перехватываются и игнорируются (заменяются на комментарии). Результаты можно увидеть ниже:  Можно видеть, как первый раз создается процедура, затем идет просто выполнение ее для каждого элемента списка. Вместо запросов по созданию процедур идет комментарий
Теперь рассмотрим эффект от оптимизации. Для примера возьмем большой документ на 800 строк. Фильтр по списку товаров из табличной части применяется для фильтрации двух регистров. Документ имеет стандартную структуру – временный рассчет регистров, контроль остатков, проведение по регистрам. Итак, 800 строк, два регистра, это означает 1600 итераций, так как для каждого регистра таблица фильтрации создается по-новому. Было сделено проведение одного и того же документа много раз с целью вычислить среднее время проведения и избежать сильного разброса параметров. Итак, документ с фильтрацией по списку значений без использования механизма оптимизации проводился в среднем от 25 до 28 секунд. Тот же документ с использованием оптимизации проводился примерно 4,2 - 4,5 секунд. Т.е. имеет место уменьшение времени проведения документа более чем в 5 раз! Можно конечно сказать, что среднетипичный документ не имеет такого большого количества строк. Тем не менее, обычно в документе задействовано большее количество регистров. Если взять документ на 100-200 строк, который проводится по 3-4 регистрам с фильтрацией по списку, то количество итераций «Создание-выполнение-удаление» составит от 300 до 800. Т.е. при использовании оптимизации, можно добиться хорошего эффекта. Следует иметь в виду, что сокращение времени проведения документов имеет также очень хороший косвенный эффект в виде меньшего количества блокировок в базе, так как блокировки будут длиться меньше. |
| Статья: Оптимизация фильтрации по спискам |
Перейти на главную страницу компании "Софтпоинт" |