Для того, чтобы разрабатывать производительные запросы, важно понимать все этапы выполнения, начиная от кода 1С: Предприятие, заканчивая извлечением данных в СУБД.
1. Текст в 1С: Предприятие.
Первым делом, мы пишем код в 1С. Для написания используется встроенный конструктор запросов, который помогает строить их декларативно. При написании текста, в 1С используется объектная модель, т.е. обращаемся к объектам, а не напрямую к таблицам, в которых хранятся данные.
Так же используем и виртуальные таблицы, такие как Срез последних Регистра сведений, Остатки или Обороты Регистра накопления и т.д. Они называются виртуальными, потому что они либо вообще физически не существуют в самой СУБД, либо имеют отличную структуру.
Виртуальные таблицы помогают обращаться к данным СУБД более простым способом.
Другими словами, если бы мы обращались к СУБД напрямую, мы бы не смогли обратиться к данным именно в том виде, как мы напишем запрос через виртуальную таблицу.
2. Запрос в СУБД.
При выполнении запроса, платформа 1С: Предприятие переводит код 1С в код T-SQL. Т.е. происходит переход от объектной модели 1С: Предприятия в табличную модель хранения данных в СУБД. Вследствие чего, реально выполняемый запрос может иметь намного более сложный код, чем мы писали изначально через объектную модель 1С. Мы в этом можем легко убедиться, например, выполнив код с использованием виртуальной таблицы обороты регистра накопления с типом Остатки.
3. Обработка запроса в СУБД (Оптимизатор).
Когда СУБД получает от 1С: Предприятия код для выполнения, в работу включается Оптимизатор. Код на языке SQL – это всего лишь инструкция: какие данные и из каких таблиц нам следует извлечь. Но сам процесс извлечения данных может быть разным в разных ситуациях:
- Можно просто сканировать всю таблицу с основными данными (кластерный индекс или куча).
- Можно использовать поиск по кластерному индексу.
- Можно использовать поиск по дополнительному индексу.
- Можно изменять порядок выполнения подзапросов и временных таблиц (если запрос сложный), в зависимости от того, как они взаимосвязаны.
- Можно накладывать различные условия. Например, если у нас идет соединение со сложным вложенным запросом, то можно накладывать условия соединения внутри вложенного запроса.
- Можно соединять таблицы различным способами (о способах соединения будет в другой статье).
- Можно выполнять арифметические действия в разном порядке, например 123*20*0,5 -> (123*20)*0,5 или 123*(20*0,5).
Все эти решения принимает внутренний механизм СУБД, который называется Оптимизатор. Оптимизатор – это алгоритм, который учитывает очень много факторов и составляет все возможные стратегии реально выполняемых этапов извлечения данных для требуемого запроса.
Для анализа и выбора лучшей стратегии извлечения данных (= оценка минимальной стоимости), Оптимизатор использует информацию о таблицах: Количество строк, Размеры, Размеры строк, Наличие индексов.
Вся эта информация хранится в данных статистики (таблицы статистики).
DBCC SHOW_STATISTICS (table_name, index_name); 
На основании данных статистики Оптимизатор формирует предварительную стоимость выполнения каждого этапа. Таким образом, если по какой-то причине статистика устарела и расходится с реальными данными, то Оптимизатор может выбрать неправильную стратегию извлечения данных.
Статистика по возможности (но не всегда!) актуализируется автоматически, если настройка «AUTO_UPDATE_STATISTICS» установлена в «ON». А при реструктуризации таблиц, статистика тоже полностью обновляется.
Чтобы ее обновить принудительно, можно использовать команду:
USE ИМЯ_БД;
EXEC sp_UpdateStats; ИЛИ
UPDATE STATISTICS table_name WITH FULLSCAN; 4. План запроса в СУБД.
После того, как Оптимизатор проанализировал всю информацию, обратился к статистике и сформировал стратегию извлечения данных, создается План запроса. План запроса – это детальная схема стратегии выполнения (извлечения данных), который содержит в себе все этапы с предварительной оценкой стоимости данного этапа.
План запроса в СУБД
На основании Плана запроса, Оптимизатор может сложить стоимости каждого из этапов плана (стратегии извлечения данных) и выбрать самый оптимальный, который будет использоваться. Например, было составлено 10 планов выполнения, из этих 10 будет выбран план с наименьшей стоимостью, т.е. самый быстрый.
5. Процедурный кэш.
Важным этапом работы Оптимизатора является кэширование планов запросов. Если какой-то код выполняется повторно, то Оптимизатор возьмет План запроса из процедурного кэша, и не будет рассчитывать оптимальную стратегию заново.
Процедурный кэш обновляется автоматически при обновлении статистики и, конечно же, при выполнении реструктуризации и реиндексации.
Для принудительного обновления процедурного кэша, можно использовать команду:
DBCC FLUSHPROCINDB('database_name') Данная тема подробнее рассматривается в пакете видео-курса «Секреты 1С: Эксперта» Шаг 3. Занятие 08-01 Оптимизация запросов.
