При работе с крупными базами данных, где размер таблиц исчисляется десятками миллионов записей, стандартные подходы к написанию запросов могут приводить к значительным задержкам или полной остановке системы. Особенно остро проблема проявляется при миграции на новый сервер, когда статистика СУБД еще не обновлена, а ресурсы распределяются иначе. В данной статье мы разберем, как оптимизировать тяжелый запрос, использующий ЛЕВОЕ СОЕДИНЕНИЕ и фильтрацию по отсутствующим записям (Anti-Join).
Рассмотрим ситуацию: у нас есть огромная таблица документов Документ.Транзакция и регистр сведений РегистрСведений.Нумератор. Нам необходимо выбрать первые 1000 транзакций, которых еще нет в регистре. Первоначальный алгоритм автора предполагал помещение данных во временную таблицу (ВТ), а затем выборку из нее с условием ЕСТЬ NULL и сортировкой. На больших объемах этот подход "глохнет", так как создание ВТ без индексов и последующее разыменование полей при сортировке создают колоссальную нагрузку на дисковую подсистему и процессор сервера.
Одной из главных ошибок в запросах к большим данным является неоправданное использование временных таблиц для промежуточного хранения. Если нам нужно получить ограниченный набор данных (например, ПЕРВЫЕ 1000), помещение всего массива во временную таблицу заставляет СУБД сначала сформировать полный результат в tempdb, проиндексировать его (если указано) и только потом отсечь лишнее.
Разберем, почему лучше объединить запрос. Когда мы используем единую конструкцию ВЫБРАТЬ ПЕРВЫЕ, оптимизатор SQL может применить стратегию Top Scan. Он прекратит выполнение запроса сразу, как только найдет первую тысячу подходящих записей, не сканируя всю таблицу целиком. Посмотрим на оптимизированный вариант кода, который "взлетел" у автора:
ВЫБРАТЬ ПЕРВЫЕ 1000
Таблица2.transaction_id,
Таблица2.amount,
Таблица2.Номер,
Таблица1.Номер КАК Номер2
ИЗ
Документ.Транзакция КАК Таблица2
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Нумератор КАК Таблица1
ПО Таблица2.Ссылка = Таблица1.Документ
ГДЕ
Таблица2.Проведен
И Таблица2.transaction_id <> ""
И Таблица1.Документ ЕСТЬ NULL
УПОРЯДОЧИТЬ ПО
Таблица2.Номер
Важный нюанс: Обратите внимание, что в итоговом коде условие Таблица1.Номер ЕСТЬ NULL (или проверка по ключевому полю соединения Таблица1.Документ ЕСТЬ NULL) должно находиться в секции ГДЕ, чтобы корректно отработать логику исключения уже существующих записей.
Для эффективного соединения огромных таблиц критически важно наличие правильных индексов. Проанализируем структуру регистра Нумератор. Если поле Документ является измерением, 1С автоматически создает по нему индекс. Однако, если соединение идет медленно, стоит проверить следующее:
Документ), должно стоять на первом месте в списке измерений регистра, либо иметь признак Индексировать.Документ имеет составной тип (ссылается на разные виды документов), это существенно усложняет жизнь СУБД. На уровне SQL создается несколько колонок для хранения типа и ссылки. Для ускорения желательно в условии соединения или в фильтрах явно указывать тип через ССЫЛКА.В исходном запросе автора присутствовала конструкция УПОРЯДОЧИТЬ ПО Т1.Ссылка.Номер. В языке запросов 1С обращение через точку к полям ссылочного типа приводит к неявному (скрытому) левому соединению с основной таблицей объекта. Если таких обращений много, и они происходят внутри временных таблиц или в условиях, СУБД генерирует крайне тяжелый SQL-план.
Проанализируем ситуацию: чтобы отсортировать данные по номеру, СУБД вынуждена "сходить" в таблицу документа по каждой ссылке. Чтобы этого избежать, всегда выбирайте нужные примитивные поля (строки, числа, даты) в основном запросе и сортируйте уже по ним, как это сделано в исправленном коде: УПОРЯДОЧИТЬ ПО Таблица2.Номер. Здесь Номер — это уже реквизит самой таблицы транзакции, и дополнительного соединения не требуется.
Часто программисты сталкиваются с тем, что запрос, работавший быстро на старом сервере, "умирает" на новом. Выясним причину этого явления. Основная причина — неактуальная статистика. Оптимизатор СУБД опирается на данные о распределении значений в таблицах. После переноса базы или массовой загрузки данных статистика может быть пустой, и сервер выберет Table Scan (полный обход таблицы) вместо Index Seek (поиск по индексу).
Для решения этой проблемы выполните следующие действия:
Max Degree of Parallelism (MAXDOP) на новом сервере — иногда слишком высокая степень параллелизма мешает простым запросам.Если специфика задачи все же требует использования временных таблиц (например, данные из них используются в нескольких последующих запросах пакета), никогда не забывайте про индексацию. Рассмотрим пример:
ВЫБРАТЬ
Таблица2.Ссылка КАК СсылкаДок
ПОМЕСТИТЬ ВТ_Транзакции
ИЗ
Документ.Транзакция КАК Таблица2
ГДЕ
Таблица2.Проведен
ИНДЕКСИРОВАТЬ ПО
СсылкаДок
;
Без инструкции ИНДЕКСИРОВАТЬ ПО последующее соединение этой ВТ с физической таблицей в 50 миллионов строк превратится в катастрофу, так как сервер будет выполнять вложенные циклы (Nested Loops) без возможности быстрого поиска соответствий.
Для успешной работы с огромными объемами данных в 1С придерживайтесь следующих правил:
ПЕРВЫЕ только вместе с УПОРЯДОЧИТЬ ПО по индексированному полю.ЛЕВОЕ СОЕДИНЕНИЕ для поиска отсутствующих данных, убедитесь, что условие ЕСТЬ NULL проверяет ключевое поле (измерение регистра или ссылку).Эти шаги позволят вашим запросам работать стабильно независимо от объема накопленных данных и мощности серверного оборудования.