В процессе разработки отчетов и алгоритмов закрытия периода разработчики часто сталкиваются с ситуацией, когда простой по структуре запрос к регистру бухгалтерии выполняется непозволительно долго — вплоть до нескольких часов. Разберем конкретный практический пример, выясним архитектурные причины такого поведения платформы и СУБД, а также рассмотрим по шагам, как исправить ошибку и оптимизировать выборку данных.
Проанализируем текст запроса, вызвавший зависание выполнения на два часа (7200 секунд):
Выбрать
ХозрасчетныйОборотыДтКт.СчетДт,
ХозрасчетныйОборотыДтКт.СчетКт,
ХозрасчетныйОборотыДтКт.СубконтоДт1,
ХозрасчетныйОборотыДтКт.СубконтоДт2,
ХозрасчетныйОборотыДтКт.СубконтоДт3,
ХозрасчетныйОборотыДтКт.СубконтоКт1
Из
РегистрБухгалтерии.Хозрасчетный.ОборотыДтКт(&НачалоПериода, &КонецПериода, СчетДт В (&Счет20), СчетКт В (&Счет10)) КАК ХозрасчетныйОборотыДтКт
Здесь допущены две критические ошибки: синтаксическая ошибка передачи параметров виртуальной таблицы и архитектурная особенность выборки субконто из оборотов между счетами.
Параметры виртуальных таблиц в языке запросов 1С являются строго позиционными. В отличие от функций встроенного языка, в тексте запроса нельзя передать параметр по имени, пропуская промежуточные разделители. Вспомним точную сигнатуру виртуальной таблицы РегистрБухгалтерии.<ИмяРегистра>.ОборотыДтКт:
НачалоПериодаКонецПериодаПериодичностьУсловиеСчетДтСубконтоДтУсловиеСчетКтСубконтоКтОрганизацияУсловиеПосмотрим, что произошло в исходном запросе. Разработчик передал условие СчетДт В (&Счет20) на третью позицию — туда, где ожидается параметр Периодичность. А условие СчетКт В (&Счет10) попало на четвертую позицию — в УсловиеСчетДт. Позиция УсловиеСчетКт вовсе осталась пустой.
В результате трансляции на уровень MS SQL Server фильтрация по счетам дебета и кредита была воспринята некорректно либо полностью проигнорирована на этапе отбора первичных записей. СУБД была вынуждена поднять и просканировать движения по всей базе за указанный период.
Исправим вызов параметров виртуальной таблицы с сохранением всех необходимых запятых-разделителей:
ВЫБРАТЬ
ХозрасчетныйОборотыДтКт.СчетДт,
ХозрасчетныйОборотыДтКт.СчетКт,
ХозрасчетныйОборотыДтКт.СубконтоДт1,
ХозрасчетныйОборотыДтКт.СубконтоДт2,
ХозрасчетныйОборотыДтКт.СубконтоДт3,
ХозрасчетныйОборотыДтКт.СубконтоКт1
ИЗ
РегистрБухгалтерии.Хозрасчетный.ОборотыДтКт(
&НачалоПериода,
&КонецПериода,
,
СчетДт В (&Счет20),
,
СчетКт В (&Счет10),
,
) КАК ХозрасчетныйОборотыДтКт
Разберем внутреннее устройство регистра бухгалтерии. Физическая таблица агрегированных итогов оборотов между счетами хранит данные исключительно в разрезе счетов и общих измерений регистра. В таблице итогов оборотов между счетами субконто не хранятся.
Как только в секции ВЫБРАТЬ запрашиваются поля субконто (СубконтоДт1, СубконтоКт1 и т.д.), платформа 1С полностью теряет возможность использовать агрегаты итогов. Запрос автоматически транслируется к физической таблице движений (проводок) с множественными операциями LEFT JOIN к таблице аналитики субконто. Если за выбранный месяц в информационной базе сформированы сотни тысяч или миллионы проводок, сканирование и последующая группировка соединений требуют колоссальных ресурсов процессора и дисковой подсистемы.
Рассмотрим, как можно переписать данный запрос для достижения максимальной производительности.
Вариант 1. Использование виртуальной таблицы «ДвиженияССубконто»
Если нам необходима детальная корреспонденция с аналитикой по дебету и кредиту, обращение к таблице ДвиженияССубконто работает прозрачнее и позволяет оптимизатору СУБД более эффективно использовать индекс по периоду и счету:
ВЫБРАТЬ
Хозрасчетный.СчетДт КАК СчетДт,
Хозрасчетный.СчетКт КАК СчетКт,
Хозрасчетный.СубконтоДт1 КАК СубконтоДт1,
Хозрасчетный.СубконтоДт2 КАК СубконтоДт2,
Хозрасчетный.СубконтоДт3 КАК СубконтоДт3,
Хозрасчетный.СубконтоКт1 КАК СубконтоКт1,
СУММА(Хозрасчетный.Сумма) КАК СуммаОборот
ИЗ
РегистрБухгалтерии.Хозрасчетный.ДвиженияССубконто(
&НачалоПериода,
&КонецПериода,
СчетДт В (&Счет20) И СчетКт В (&Счет10),
,
) КАК Хозрасчетный
СГРУППИРОВАТЬ ПО
Хозрасчетный.СчетДт,
Хозрасчетный.СчетКт,
Хозрасчетный.СубконтоДт1,
Хозрасчетный.СубконтоДт2,
Хозрасчетный.СубконтоДт3,
Хозрасчетный.СубконтоКт1
Вариант 2. Использование виртуальной таблицы «Обороты»
Если аналитика субконто в действительности требуется только по затратному счету (счет 20), гораздо эффективнее использовать таблицу Обороты, где счет 20 указывается в качестве основного счета, а счет 10 — как корреспондирующий счет. В этом случае 1С сможет задействовать таблицу итогов по счету 20:
ВЫБРАТЬ
ХозрасчетныйОбороты.Счет КАК СчетДт,
ХозрасчетныйОбороты.КорСчет КАК СчетКт,
ХозрасчетныйОбороты.Субконто1 КАК СубконтоДт1,
ХозрасчетныйОбороты.Субконто2 КАК СубконтоДт2,
ХозрасчетныйОбороты.Субконто3 КАК СубконтоДт3,
ХозрасчетныйОбороты.СуммаОборотДт КАК СуммаОборот
ИЗ
РегистрБухгалтерии.Хозрасчетный.Обороты(
&НачалоПериода,
&КонецПериода,
,
Счет В (&Счет20),
,
КорСчет В (&Счет10),
,
) КАК ХозрасчетныйОбороты
Проанализируем техническое окружение, указанное в исходной проблеме: MS SQL Server 2014 совместно с платформами 1С:Предприятие 8.2.19 и 8.3.8. В этой связке кроются известные проблемы неоптимальных планов выполнения:
9481.4199 в параметрах запуска службы СУБД.DBCC FREEPROCCACHE в SQL Server Management Studio, а также обновить статистику таблиц регистра с полным сканированием (UPDATE STATISTICS ... WITH FULLSCAN).