Заметки с фронта оптимизации

Logo_1c_8_expert

Добрый день, друзья! Сегодня я вам расскажу историю про оптимизацию типового запроса УПП при восстановлении последовательности расчетов. Будет много букв, но интересно. Оптимизировать будем тремя путями: Beginner, medium, high. Поехали!

 

 

 

1. Beginner level

Поступила жалоба, на обработку одного документа при восстановлении последовательности расчетов уходит до 10 секунд. Простым замером был найден запрос, который съедает 98% времени. Я его немного упростил для удобства. Вот наш виновник:

zametki-s-fronta-optimizacii_01

Первая ВТ в оригинале другая, но там, как правило, 1 строка. В регистре РасчетыПоРеализацииВУсловныхЕдиницахОрганизации 3.5 млн строк. Запрос отрабатывает за 7-10 секунд.

Кто читал мои прошлые заметки, мог обратить внимание на упорядочивание

zametki-s-fronta-optimizacii_14

Т.к. «Документ» составного типа, то серверу придется подтягивать все типы документов.

Но в данном случае это не сильно замедляет запрос – удачно используется поиск по кластерному индексу таблиц документов.

В профайлере SQL запрос выдал такие значения:

zametki-s-fronta-optimizacii_02

В плане запроса есть интересная строка

zametki-s-fronta-optimizacii_03

Что мы здесь видим? А видим мы 967126 чтений нашей временной таблицы. Это происходит вот здесь:

zametki-s-fronta-optimizacii_04

Когда мы делаем такое условие, SQL сервер при проверки условия каждый раз обращается к временной таблице. Здесь мы и ждем 10 секунд. Что мы можем сделать?

А вот сейчас мы вспомним, что временные таблицы можно индексировать! И, конечно, все мы это делаем! Берем и индексируем ВТ по полям, используемым в отборе:

zametki-s-fronta-optimizacii_05

… и выполняем наш запрос…

zametki-s-fronta-optimizacii_06

Запрос теперь стабильно выполняется за 2-2.5 секунды, против 6-10. Отличный результат с минимумом усилий. Поэтому, старайтесь индексировать временные таблицы по полям соединений, условий.

Beginner level пройден!

2. Medium level

Но в нашей ситуации 2-2.5 секунды тоже много. К тому же очевидна неоптимальность запроса. Нет смысла нам читать ВТ почти миллион раз.

И сейчас мы сделаем интересный поворот. Нам много раз говорили, что надо максимально использовать параметры виртуальной таблицы. Здесь так и сделано. А ещё некоторые из вас знают, что соединения с виртуальными таблицами – это плохо, т.к. виртуальная таблица, по сути, это вложенный запрос, а соединения с вложенными запросами нестабильны по производительности.

И сейчас мы игнорируем эти 2 правила :). И пишем следующее:

zametki-s-fronta-optimizacii_07

Внезапно, запрос отрабатывает за 0.1 – 0.6 секунды. Давайте разберемся, почему так. Вот данные профайлера:

zametki-s-fronta-optimizacii_08

Посмотрите, насколько меньше стало чтений. Отсюда и время в 123 мс и слабая нагрузка на CPU. А вот и разгадка:

zametki-s-fronta-optimizacii_09

Мы 1 раз читаем нашу ВТ при внутреннем соединении. Поэтому и такая высокая скорость. Но надо сказать про 2 нюанса:

  1. Внутреннее соединение и конструкция «В» это не одно и то же. Не забываем, что конструкция «В» ищет только строки из 1-ой таблицы. А соединение перемножает таблицы. И если по условию во 2-ой таблицы найдется больше одной строки, то в результат также пойдет больше 1 строки. Этот момент надо учесть обязательно.
  2. Получившаяся конструкция удовлетворяет нашим условиям полностью, но только с небольшим количеством строк в ВТ, т.к. вся конструкция нестабильна. А нестабильна она из-за соединения виртуальной таблицы остатков и временной таблицы. При такой ситуации оптимизатор СУБД может выбрать неверный план запроса и мы можем уйти в те же 10 секунд, а то и гораздо больше. Это может произойти не сразу, а при росте размеров таблиц, например. Вот такой случай был при выполнении этого же запроса, но с другим периодом:
    zametki-s-fronta-optimizacii_10
    76 секунд… Это говорит от том, что оптимизатор в этот раз выбрал неверный план выполнения запроса. Далее мы подскажем ему, как всегда выбирать верный план! Но это уже будет high level!

 

Medium level пройден!

3. High level

Ну что, друзья, дальше мы будем использовать совсем черную магию. Особо впечатлительным читать не рекомендую 🙂 .

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

Все мы знаем, что виртуальные таблицы остатков, оборотов и т.д. формируются во время запроса и не хранятся в БД. Однако, это не совсем так. В БД хранятся рассчитанные итоги на каждый месяц. Выглядят они примерно так:

zametki-s-fronta-optimizacii_11

И когда мы хотим получить остатки, скажем, на 14 июля, 1С получает из таблицы итогов остатки на 01.08 и отнимает данные за дни с 14.07 по 31.07. Или может получить данные на 01.07 и прибавить данные за 14 дней.

Так вот, мы сейчас попробуем сделать свою таблицу остатков с блэкджеком и грамотными соединениями. Вот первая часть запроса:

zametki-s-fronta-optimizacii_12

Этот запрос выглядит точно как прошлый, но обратите внимание на параметр НачалоМесяца. Т.е. мы берем данные не на искомую дату, а на начало месяца искомой даты. А на эту дату как раз есть рассчитанные итоги.

В итоге в данном запросе идет соединение нашей проиндексированной временной таблицы и проиндексированной реальной таблицы итогов на начало месяца. Время выполнения 10-20 мс.

Дальше мы подтянем объединением из реальной таблицы данные за недостающие дни:

zametki-s-fronta-optimizacii_13

В этом запросе мы соединяем реальную таблицу регистра с нашей ВТ, что тоже хорошо и понятно для оптимизатора. Далее объединяем, группируем и т.д.

Тут надо быть внимательным и использовать границы, чтобы не потерять секунду, или не учесть секунду дважды. А также не забываем про «активность», т.к. используем реальную таблицу.

В итоге мы получили запрос, который стабильно выполняется за 100-300 мс при нашем количестве данных. А также, предсказуемо ведет себя при различных условиях и росте таблиц БД.

High level пройден!

 

Спасибо всем, кто дочитал  🙂

Помогла ли вам данная статья?
Да, спасибо, помогла.
Немного помогла.
Совсем не помогла.
Не то, что я искал(а).

P.S. Смотрите также:

Запись опубликована в рубрике Эксперт 1С с метками , . Добавьте в закладки постоянную ссылку.


Добавить комментарий

Ваш e-mail не будет опубликован.