При работе с крупными базами данных 1С программисты часто сталкиваются с ситуацией, когда стандартные механизмы, такие как виртуальная таблица ДвиженияССубконто, начинают демонстрировать крайне низкую производительность. В некоторых случаях отчет за небольшой период может выполняться часами, а анализ плана запроса в SQL Server показывает Full Table Scan (полное сканирование) таблицы субконто, несмотря на установленные отборы. Разберем детально, почему это происходит и как переписать запрос для достижения максимального быстродействия.
Для начала проанализируем, как устроена физическая структура данных. Виртуальная таблица ДвиженияССубконто не существует в SQL в готовом виде. Каждый раз, когда мы обращаемся к ней, платформа генерирует сложный SQL-запрос, соединяющий основную таблицу регистра (где лежат суммы, счета и периоды) с таблицей значений субконто (обычно это таблица с суффиксом _AccRgED). Если в запросе фигурируют три субконто, платформа выполнит три соединения с этой таблицей.
Рассмотрим структуру типового индекса таблицы дополнительных значений субконто в SQL Server:
Основная проблема заключается в том, что поле Вид субконто стоит в индексе раньше, чем Значение. Если в запросе 1С мы указываем отбор только по значению (например, по конкретной номенклатуре или контрагенту), но не указываем вид субконто, оптимизатор SQL Server часто не может эффективно использовать индекс для операции Index Seek. Вместо этого он вынужден сканировать миллионы записей, чтобы найти нужные совпадения. Ситуация усугубляется тем, что поле "Значение" в SQL является составным (хранится в колонках типа и ссылки), и отсутствие фильтра по типу значения также «выбивает» запрос из индекса.
Первое, что мы можем сделать для ускорения — это «помочь» платформе правильно построить SQL-запрос. Для этого необходимо явно указывать вид субконто в параметрах виртуальной таблицы. Это позволит SQL-серверу использовать индекс более эффективно.
Проанализируем пример кода с уточняющим отбором:
ВЫБРАТЬ
Движения.Регистратор,
Движения.Сумма
ИЗ
РегистрБухгалтерии.Хозрасчетный.ДвиженияССубконто(
&НачалоПериода,
&КонецПериода,
Счет = &Счет,
,
ВидСубконто1 = &ВидСубконтоОбъект И Субконто1 = &ЗначениеСубконто
) КАК Движения
Такой подход может ускорить запрос, но он не решает проблему Parameter Sniffing (когда SQL-сервер строит неоптимальный план из-за первого запуска с редкими параметрами) и не избавляет от тяжелых соединений внутри самой виртуальной таблицы.
Как показывает практика и опыт экспертов, для действительно высокой скорости на больших объемах данных (десятки миллионов записей) необходимо отказаться от использования ДвиженияССубконто. Вместо этого мы самостоятельно соединим физические таблицы регистра. Рассмотрим этот алгоритм по шагам.
Шаг 1: Выборка ключей записей во временную таблицу.
Сначала мы обращаемся напрямую к физической таблице значений субконто. Нам нужно получить идентификаторы строк (Регистратор и Номер строки), которые соответствуют нашему аналитическому разрезу. Это сработает мгновенно, так как SQL выполнит поиск по индексу ВидСубконто + Значение.
ВЫБРАТЬ РАЗЛИЧНЫЕ
Субконто.Регистратор КАК Регистратор,
Субконто.НомерСтроки КАК НомерСтроки,
Субконто.Период КАК Период
ПОМЕСТИТЬ ВТ_НужныеЗаписи
ИЗ
РегистрБухгалтерии.Хозрасчетный.Субконто КАК Субконто
ГДЕ
Субконто.Период МЕЖДУ &ДатаНачала И &ДатаОкончания
И Субконто.Вид = &ВидСубконтоПроект
И Субконто.Значение = &ЗначениеПроект
ИНДЕКСИРОВАТЬ ПО
Период,
Регистратор,
НомерСтроки
;
Шаг 2: Соединение с основной таблицей регистра.
Теперь, когда у нас есть узкий список идентификаторов (например, 100 строк вместо 77 миллионов), мы соединяем их с основной таблицей регистра бухгалтерии для получения сумм и счетов. Выполняем ВНУТРЕННЕЕ СОЕДИНЕНИЕ, чтобы отсечь лишнее.
ВЫБРАТЬ
Хозрасчетный.Регистратор,
Хозрасчетный.СчетКт,
Хозрасчетный.Сумма,
ВТ_НужныеЗаписи.Период
ИЗ
РегистрБухгалтерии.Хозрасчетный КАК Хозрасчетный
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_НужныеЗаписи КАК ВТ_НужныеЗаписи
ПО Хозрасчетный.Регистратор = ВТ_НужныеЗаписи.Регистратор
И Хозрасчетный.НомерСтроки = ВТ_НужныеЗаписи.НомерСтроки
И Хозрасчетный.Период = ВТ_НужныеЗаписи.Период
ГДЕ
Хозрасчетный.СчетКт = &НужныйСчет
Результат: В реальных условиях такая оптимизация позволяет сократить время выполнения запроса с минут до долей секунды (в обсуждении на форуме зафиксировано ускорение с 2.5 минут до 0.126 секунды).
Разберем подробнее, на что еще стоит обратить внимание при оптимизации таких запросов:
Период в условие связи. Это позволяет SQL-серверу использовать кластерный индекс основной таблицы, который всегда начинается с периода._Fld1287 (разделитель) всегда стоит первым в индексах. Убедитесь, что статистика по этому полю актуальна, иначе оптимизатор может ошибочно выбрать сканирование всей таблицы..Субконто остается единственным надежным способом.ВНУТРЕННЕЕ СОЕДИНЕНИЕ вместо ЛЕВОЕ СОЕДИНЕНИЕ, когда это возможно. Внутреннее соединение позволяет оптимизатору более гибко менять порядок обработки таблиц, выбирая наиболее легкую таблицу в качестве ведущей.Проанализировав ситуацию, мы выяснили, что виртуальная таблица ДвиженияССубконто является «черным ящиком», который не всегда оптимально транслируется в SQL. При возникновении проблем с производительностью переход на работу с физическими таблицами через временные таблицы с индексацией является золотым стандартом оптимизации 1С.