При работе с системой 1С:Предприятие, особенно с конфигурациями, содержащими большой объем данных, разработчики и пользователи часто сталкиваются с проблемой низкой производительности запросов. Одной из наиболее распространенных причин такого замедления является неоптимальное обращение к виртуальным таблицам регистра бухгалтерии. Мы рассмотрим, как можно диагностировать и решить эту проблему, обеспечивая высокую скорость выполнения запросов даже при значительном объеме данных.
Виртуальные таблицы регистра бухгалтерии, такие как Обороты, Остатки, ОстаткиИОбороты, являются мощным инструментом для получения агрегированных данных. Однако их удобство может обернуться серьезными проблемами с производительностью, если не учитывать внутреннюю механику их работы и особенности формирования запросов к ним. Давайте выясним, почему возникают такие проблемы и как мы можем их эффективно устранить.
Основная причина медленной работы с виртуальными таблицами кроется в том, что платформа 1С на лету формирует сложный SQL-запрос для их заполнения. Этот запрос может включать множество соединений, группировок и условий, что при больших объемах данных приводит к колоссальной нагрузке на СУБД. Часто разработчики забывают, что эти таблицы строятся на основе физических таблиц базы данных, хранящих проводки (движения), а также данные измерений и ресурсов регистра.
Рассмотрим типовую ситуацию: необходимо получить остатки по счету за определенный период. Если мы обращаемся к виртуальной таблице ОстаткиИОбороты, платформа выполняет следующие действия:
_AccRg*).Каждый из этих шагов может быть ресурсоемким. Особенно критично использование большого количества параметров в виртуальной таблице, если эти параметры не покрыты индексами или приводят к сканированию всей таблицы движений.
Существует несколько подходов к оптимизации запросов, каждый из которых имеет свои преимущества и недостатки. Мы рассмотрим наиболее эффективные из них.
Перед тем как прибегать к сложным решениям, необходимо убедиться, что мы корректно используем параметры виртуальных таблиц. Многие проблемы с производительностью можно решить, просто правильно настроив виртуальную таблицу.
Передача параметров в виртуальную таблицу:
Мы должны передавать только те параметры, которые действительно необходимы для формирования выборки. Например, если мы работаем с определенным периодом, обязательно указываем его, чтобы СУБД могла эффективно использовать временные индексы. Не стоит передавать избыточные или слишком широкие периоды.
Пример правильного использования виртуальной таблицы ОстаткиИОбороты:
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| ОстаткиИОбороты.Счет,
| ОстаткиИОбороты.Субконто1,
| ОстаткиИОбороты.СуммаНачальныйОстатокДт,
| ОстаткиИОбороты.СуммаНачальныйОстатокКт,
| ОстаткиИОбороты.СуммаОборотДт,
| ОстаткиИОбороты.СуммаОборотКт,
| ОстаткиИОбороты.СуммаКонечныйОстатокДт,
| ОстаткиИОбороты.СуммаКонечныйОстатокКт
|ИЗ
| РегистрБухгалтерии.Хозрасчетный.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, Авто, , , , , ) КАК ОстаткиИОбороты
|ГДЕ
| ОстаткиИОбороты.Счет = &Счет";
Запрос.УстановитьПараметр("НачалоПериода", НачалоМесяца(Дата));
Запрос.УстановитьПараметр("КонецПериода", КонецМесяца(Дата));
Запрос.УстановитьПараметр("Счет", ПланыСчетов.Хозрасчетный.НайтиПоКоду("60"));
Результат = Запрос.Выполнить();
В этом примере мы четко задаем границы периода и конкретный счет, что позволяет платформе генерировать более эффективный SQL-запрос.
Если оптимизация параметров виртуальных таблиц не дает желаемого результата, мы можем прибегнуть к прямому обращению к физическим таблицам базы данных. Это наиболее сложный, но и наиболее эффективный способ получить максимальную производительность.
Для работы с регистром бухгалтерии мы будем использовать следующие таблицы:
_AccRg* (например, _AccRg2368): таблица движений регистра бухгалтерии (проводок). Здесь хранятся основные данные, такие как ссылка на документ, период, счет Дт, счет Кт, суммы и количество._AccRgEDSC*: таблица, хранящая значения измерений (субконто) для дебета._AccRgECSC*: таблица, хранящая значения измерений (субконто) для кредита._AccRgRRC*: таблица, хранящая значения ресурсов._AccRgD*: таблица с описанием регистра (для получения метаданных, например, кодов счетов).Важно: При работе с прямыми таблицами мы должны быть максимально внимательны, так как любые ошибки в запросе могут привести к неверным данным или неработоспособности системы. Кроме того, внутренние имена таблиц (например, _AccRg2368) могут меняться между версиями конфигурации или платформ, хотя структура в основном остается стабильной.
Разберем по шагам процесс создания прямого запроса для получения оборотов по счету.
Шаг 1: Определение имен физических таблиц.
Для определения имен физических таблиц можно использовать отладчик 1С, изучив свойства объекта РегистрБухгалтерии.Хозрасчетный, или специализированные обработки для просмотра структуры базы данных. Например, таблица движений может называться _AccRg2368, а таблица измерений дебета – _AccRgEDSC2368.
Шаг 2: Создание запроса к таблице движений.
Начнем с запроса к основной таблице движений, фильтруя ее по периоду и интересующему нас счету.
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Движения.PERIOD AS Период,
| Движения.DT AS СчетДт,
| Движения.KT AS СчетКт,
| Движения.SUMMA AS Сумма
|ИЗ
| _AccRg2368 КАК Движения
|ГДЕ
| Движения.PERIOD >= &НачалоПериода
| И Движения.PERIOD <= &КонецПериода
| И (Движения.DT = &Счет ИЛИ Движения.KT = &Счет)";
Запрос.УстановитьПараметр("НачалоПериода", НачалоМесяца(Дата));
Запрос.УстановитьПараметр("КонецПериода", КонецМесяца(Дата));
Запрос.УстановитьПараметр("Счет", ПланыСчетов.Хозрасчетный.НайтиПоКоду("60"));
Результат = Запрос.Выполнить();
Здесь мы используем псевдонимы PERIOD, DT, KT, SUMMA, которые соответствуют физическим полям в таблице движений. Внимание: эти имена могут отличаться в зависимости от версии платформы или используемой СУБД, но часто остаются схожими.
Шаг 3: Добавление субконто.
Для получения значений субконто нам потребуется соединение с таблицами _AccRgEDSC* и _AccRgECSC*. Каждое субконто имеет свой индекс, например, SP1, SP2, SP3. Эти индексы соответствуют видам субконто, определенным в плане счетов.
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Движения.PERIOD AS Период,
| Движения.DT AS СчетДт,
| Движения.KT AS СчетКт,
| Движения.SUMMA AS Сумма,
| СубконтоДт.SP1 AS СубконтоДт1,
| СубконтоКт.SP1 AS СубконтоКт1
|ИЗ
| _AccRg2368 КАК Движения
| ЛЕВОЕ СОЕДИНЕНИЕ _AccRgEDSC2368 КАК СубконтоДт
| ПО Движения.ID = СубконтоДт.ID
| ЛЕВОЕ СОЕДИНЕНИЕ _AccRgECSC2368 КАК СубконтоКт
| ПО Движения.ID = СубконтоКт.ID
|ГДЕ
| Движения.PERIOD >= &НачалоПериода
| И Движения.PERIOD <= &КонецПериода
| И (Движения.DT = &Счет ИЛИ Движения.KT = &Счет)";
// Параметры остаются те же
Обратите внимание на ЛЕВОЕ СОЕДИНЕНИЕ, так как не для всех проводок могут быть субконто. ID – это уникальный идентификатор проводки, который связывает таблицы движений и субконто.
Шаг 4: Агрегация и расчет остатков/оборотов.
Для получения конечных остатков и оборотов нам потребуется выполнить группировку и агрегацию полученных данных. Это можно сделать как на стороне запроса, так и в цикле обхода результата.
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| СчетДт,
| СчетКт,
| СУММА(ВЫБОР КОГДА СчетДт = &Счет Тогда Сумма ИНАЧЕ 0 КОНЕЦ) КАК ОборотДт,
| СУММА(ВЫБОР КОГДА СчетКт = &Счет Тогда Сумма ИНАЧЕ 0 КОНЕЦ) КАК ОборотКт
|ИЗ
| (ВЫБРАТЬ
| Движения.DT AS СчетДт,
| Движения.KT AS СчетКт,
| Движения.SUMMA AS Сумма
| ИЗ
| _AccRg2368 КАК Движения
| ГДЕ
| Движения.PERIOD >= &НачалоПериода
| И Движения.PERIOD <= &КонецПериода
| И (Движения.DT = &Счет ИЛИ Движения.KT = &Счет)) КАК ВложенныйЗапрос
|СГРУППИРОВАТЬ ПО
| СчетДт,
| СчетКт";
Запрос.УстановитьПараметр("НачалоПериода", НачалоМесяца(Дата));
Запрос.УстановитьПараметр("КонецПериода", КонецМесяца(Дата));
Запрос.УстановитьПараметр("Счет", ПланыСчетов.Хозрасчетный.НайтиПоКоду("60"));
Результат = Запрос.Выполнить();
Мы используем вложенный запрос для первичной выборки и затем агрегируем данные во внешнем запросе с помощью СУММА и ВЫБОР КОГДА. Это позволяет нам получить обороты отдельно по дебету и кредиту. Для расчета начальных/конечных остатков нам придется выполнить аналогичный запрос для начального периода или использовать более сложные конструкции с временными таблицами.
Для сложных аналитических отчетов, когда нам нужно получить данные по нескольким счетам, субконто и периодам, эффективно использовать временные таблицы. Это позволяет разбить сложный запрос на несколько более простых шагов.
Шаг 1: Сформируем временную таблицу с необходимыми проводками.
Мы выбираем все нужные проводки за интересующий период и помещаем их во временную таблицу. Это позволяет СУБД работать с меньшим объемом данных и избежать повторного сканирования основной таблицы.
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ ПОМЕСТИТЬ ВТ_Движения
| Движения.PERIOD AS Период,
| Движения.DT AS СчетДт,
| Движения.KT AS СчетКт,
| Движения.SUMMA AS Сумма,
| Движения.ID AS ИДДвижения
|ИЗ
| _AccRg2368 КАК Движения
|ГДЕ
| Движения.PERIOD >= &НачалоПериода
| И Движения.PERIOD <= &КонецПериода
| И (Движения.DT = &Счет ИЛИ Движения.KT = &Счет)";
Запрос.УстановитьПараметр("НачалоПериода", НачалоМесяца(Дата));
Запрос.УстановитьПараметр("КонецПериода", КонецМесяца(Дата));
Запрос.УстановитьПараметр("Счет", ПланыСчетов.Хозрасчетный.НайтиПоКоду("60"));
Запрос.Выполнить(); // Создаем временную таблицу
// Теперь можем работать с ВТ_Движения
Запрос2 = Новый Запрос;
Запрос2.Текст =
"ВЫБРАТЬ
| ВТ_Движения.СчетДт,
| ВТ_Движения.СчетКт,
| ВТ_Движения.Сумма
|ИЗ
| ВТ_Движения КАК ВТ_Движения";
Результат = Запрос2.Выполнить();
Шаг 2: Создадим индексы для временной таблицы (по необходимости).
Если мы планируем многократно соединять временную таблицу с другими таблицами или выполнять по ней сложные группировки, имеет смысл создать индексы. В платформе 1С это делается автоматически для полей, используемых в соединениях и условиях ГДЕ, но явно указать ИНДЕКСИРОВАТЬ ПО может быть полезно.
Запрос.Текст =
"ВЫБРАТЬ ПОМЕСТИТЬ ВТ_Движения
| Движения.PERIOD AS Период,
| Движения.DT AS СчетДт,
| Движения.KT AS СчетКт,
| Движения.SUMMA AS Сумма,
| Движения.ID AS ИДДвижения
|ИЗ
| _AccRg2368 КАК Движения
|ГДЕ
| Движения.PERIOD >= &НачалоПериода
| И Движения.PERIOD <= &КонецПериода
| И (Движения.DT = &Счет ИЛИ Движения.KT = &Счет)
|
|ИНДЕКСИРОВАТЬ ПО
| СчетДт,
| СчетКт,
| ИДДвижения";
Это подскажет СУБД, по каким полям создавать индексы, что ускорит дальнейшие операции.
Шаг 3: Соединяем временные таблицы с субконто.
После создания временной таблицы с основными движениями, мы можем присоединить к ней данные субконто, используя ИДДвижения.
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ ПОМЕСТИТЬ ВТ_Движения
| Движения.PERIOD AS Период,
| Движения.DT AS СчетДт,
| Движения.KT AS СчетКт,
| Движения.SUMMA AS Сумма,
| Движения.ID AS ИДДвижения
|ИЗ
| _AccRg2368 КАК Движения
|ГДЕ
| Движения.PERIOD >= &НачалоПериода
| И Движения.PERIOD <= &КонецПериода
| И (Движения.DT = &Счет ИЛИ Движения.KT = &Счет)
|
|ИНДЕКСИРОВАТЬ ПО
| ИДДвижения
|;
|
|ВЫБРАТЬ
| ВТ_Движения.Период,
| ВТ_Движения.СчетДт,
| ВТ_Движения.СчетКт,
| ВТ_Движения.Сумма,
| СубконтоДт.SP1 AS СубконтоДт1,
| СубконтоКт.SP1 AS СубконтоКт1
|ИЗ
| ВТ_Движения КАК ВТ_Движения
| ЛЕВОЕ СОЕДИНЕНИЕ _AccRgEDSC2368 КАК СубконтоДт
| ПО ВТ_Движения.ИДДвижения = СубконтоДт.ID
| ЛЕВОЕ СОЕДИНЕНИЕ _AccRgECSC2368 КАК СубконтоКт
| ПО ВТ_Движения.ИДДвижения = СубконтоКт.ID";
// Параметры для первого запроса
Запрос.УстановитьПараметр("НачалоПериода", НачалоМесяца(Дата));
Запрос.УстановитьПараметр("КонецПериода", КонецМесяца(Дата));
Запрос.УстановитьПараметр("Счет", ПланыСчетов.Хозрасчетный.НайтиПоКоду("60"));
Результат = Запрос.Выполнить();
Этот подход позволяет нам контролировать каждый шаг выполнения запроса и минимизировать количество операций чтения с диска, что существенно увеличивает производительность.
При необходимости получения оборотов или остатков по нескольким измерениям, мы должны тщательно подходить к группировке. Используйте только те поля, по которым действительно требуется группировка, чтобы не увеличивать объем промежуточных данных.
Для сложных группировок, особенно когда мы работаем с несколькими субконто, мы можем использовать вложенные запросы или несколько временных таблиц для последовательного агрегирования данных.
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ ПОМЕСТИТЬ ВТ_ДвиженияССубконто
| Движения.Период,
| Движения.СчетДт,
| Движения.СчетКт,
| Движения.Сумма,
| СубконтоДт.SP1 AS СубконтоДт1,
| СубконтоКт.SP1 AS СубконтоКт1
|ИЗ
| (ВЫБРАТЬ
| Движения.PERIOD AS Период,
| Движения.DT AS СчетДт,
| Движения.KT AS СчетКт,
| Движения.SUMMA AS Сумма,
| Движения.ID AS ИДДвижения
| ИЗ
| _AccRg2368 КАК Движения
| ГДЕ
| Движения.PERIOD >= &НачалоПериода
| И Движения.PERIOD <= &КонецПериода
| И (Движения.DT = &Счет ИЛИ Движения.KT = &Счет)
| ИНДЕКСИРОВАТЬ ПО
| ИДДвижения) КАК Движения
| ЛЕВОЕ СОЕДИНЕНИЕ _AccRgEDSC2368 КАК СубконтоДт
| ПО Движения.ИДДвижения = СубконтоДт.ID
| ЛЕВОЕ СОЕДИНЕНИЕ _AccRgECSC2368 КАК СубконтоКт
| ПО Движения.ИДДвижения = СубконтоКт.ID
|ИНДЕКСИРОВАТЬ ПО
| СчетДт,
| СубконтоДт1,
| СчетКт,
| СубконтоКт1
|;
|
|ВЫБРАТЬ
| ВТ_ДвиженияССубконто.СчетДт,
| ВТ_ДвиженияССубконто.СубконтоДт1,
| СУММА(ВТ_ДвиженияССубконто.Сумма) КАК СуммаОборотДт
|ИЗ
| ВТ_ДвиженияССубконто КАК ВТ_ДвиженияССубконто
|ГДЕ
| ВТ_ДвиженияССубконто.СчетДт = &Счет
|СГРУППИРОВАТЬ ПО
| ВТ_ДвиженияССубконто.СчетДт,
| ВТ_ДвиженияССубконто.СубконтоДт1
|;
|
|ВЫБРАТЬ
| ВТ_ДвиженияССубконто.СчетКт,
| ВТ_ДвиженияССубконто.СубконтоКт1,
| СУММА(ВТ_ДвиженияССубконто.Сумма) КАК СуммаОборотКт
|ИЗ
| ВТ_ДвиженияССубконто КАК ВТ_ДвиженияССубконто
|ГДЕ
| ВТ_ДвиженияССубконто.СчетКт = &Счет
|СГРУППИРОВАТЬ ПО
| ВТ_ДвиженияССубконто.СчетКт,
| ВТ_ДвиженияССубконто.СубконтоКт1";
// Параметры
Запрос.УстановитьПараметр("НачалоПериода", НачалоМесяца(Дата));
Запрос.УстановитьПараметр("КонецПериода", КонецМесяца(Дата));
Запрос.УстановитьПараметр("Счет", ПланыСчетов.Хозрасчетный.НайтиПоКоду("60"));
Результат = Запрос.ВыполнитьПакет();
В этом примере мы сначала формируем временную таблицу с движениями и присоединенными субконто, а затем выполняем два отдельных запроса для получения дебетовых и кредитовых оборотов, группируя по соответствующим измерениям. Это позволяет СУБД работать более эффективно, так как каждая операция выполняется над уже подготовленными и, возможно, проиндексированными данными.
PERIOD, DT, KT, ID) покрыты соответствующими индексами. Платформа 1С создает множество индексов по умолчанию, но иногда может потребоваться создание дополнительных индексов для специфических запросов.ВЫБРАТЬ *, особенно при работе с большими таблицами.Мы видим, что оптимизация запросов к регистру бухгалтерии в 1С — это многогранный процесс, требующий глубокого понимания как архитектуры платформы, так и принципов работы СУБД. Начиная с корректного использования параметров виртуальных таблиц и заканчивая прямыми запросами к физическим таблицам с применением временных таблиц и индексирования, мы можем значительно повысить производительность наших систем и обеспечить быструю и стабильную работу даже при колоссальных объемах данных.
Помните, что каждый случай уникален, и оптимальное решение может варьироваться в зависимости от конкретной задачи, структуры данных и требований к производительности. Экспериментируйте, профилируйте и анализируйте, чтобы найти наиболее подходящий подход для вашей ситуации.