Стандартный встроенный язык запросов 1С:Предприятия 7.7 (так называемый «черный запрос») при работе с большими объемами данных и длинными периодами может выполняться крайне медленно. Для ускорения отчетов в базах формата DBF и MS SQL традиционно используют прямые запросы с помощью компоненты 1С++ и библиотеки 1SQLite. Разберем, как преобразовать типовой запрос по начальным остаткам, оборотам и конечным остаткам регистра в быстрый прямой SQL-запрос.
Рассмотрим исходный стандартный запрос, который требуется оптимизировать:
ТекстЗапроса = "
|ПЕРИОД С ДатаНачала По ДатаКонца;
|Фирма = Регистр.БанковскийКредит.Фирма;
|Клиент = Регистр.БанковскийКредит.Клиент;
|Остаток = Регистр.БанковскийКредит.Остаток;
|Док = Регистр.БанковскийКредит.ТекущийДокумент;
|
|Группировка Фирма Без Групп;
|Группировка Клиент Без Групп;
|Группировка Док Упорядочить по Док.ДатаДок;
|Функция Начало = НачОст(Остаток);
|Функция Приход = Приход(Остаток);
|Функция Расход = Расход(Остаток);
|Функция Конец = КонОст(Остаток);
|";
Прежде чем писать прямой запрос, выясним, как именно платформа 1С 7.7 сохраняет данные регистров в таблицах базы данных:
RG): хранит рассчитанные остатки на начало каждого расчетного месяца и срез на момент точки актуальности (ТА). Основные системные поля — PERIOD (дата среза) и поля измерений с ресурсами.RA): содержит все записи документов, двигающих регистр. Каждая строка содержит идентификатор документа IDDOC, позицию документа DATE_TIME_IDDOC, значения измерений, ресурсов и признак движения DEBRED (где 1 — приход, а 0 — расход)._1SJOURN): содержит метаданные документов. Чтобы исключить непроведенные или помеченные на удаление документы, обязательно проверяются флаг CLOSED & 1 = 1 (проведен) и ISMARK = ' ' (не помечен на удаление).Метапарсер 1С++ предоставляет удобную виртуальную таблицу $РегистрОстаткиОбороты, которая берет на себя всю сложную логику «докрутки» остатков от границ месяцев до произвольной даты документа.
Посмотрим на пример построения прямого запроса через класс ODBCRecordset или 1SQLite:
БазаДанных = СоздатьОбъект("ODBCRecordset");
ТекстЗапроса = "
|SELECT
| Рег.Фирма AS [Фирма $Справочник.Фирмы],
| Рег.Клиент AS [Клиент $Справочник.Клиенты],
| Рег.ТекущийДокумент AS [Док $Документ],
| Рег.ОстатокНачальныйОстаток AS НачОстаток,
| Рег.ОстатокПриход AS Приход,
| Рег.ОстатокРасход AS Расход,
| Рег.ОстатокКонечныйОстаток AS КонОстаток
|FROM
| $РегистрОстаткиОбороты.БанковскийКредит(
| :ДатаНачала,
| :ДатаКонца~,
| Документ,
| ,
| ,
| (Фирма, Клиент),
| (Остаток)
| ) AS Рег
|ORDER BY
| Рег.Фирма,
| Рег.Клиент,
| Рег.DATE_TIME_IDDOC
|";
БазаДанных.УстановитьТекстовыйПараметр("ДатаНачала", ДатаНачала);
БазаДанных.УстановитьТекстовыйПараметр("ДатаКонца", ДатаКонца);
ТЗ = БазаДанных.ВыполнитьИнструкцию(ТекстЗапроса);
Разберем параметры виртуальной таблицы $РегистрОстаткиОбороты:
~ в :ДатаКонца~ указывает на включение границы секунды конца дня).Документ указывает, что выборка оборотов детализируется до уровня отдельных документов.(Фирма, Клиент).(Остаток).Если виртуальные таблицы метапарсера не используются, остатки и обороты собираются классическим прямым SQL-запросом через объединение трех потоков данных:
$Регистр.БанковскийКредит (таблица итогов RG) на начало месяца периода.$Регистр.БанковскийКредит (таблица движений RA) от начала месяца до ДатаНачала со знаком минус (корректировка входящего остатка).RA в интервале между ДатаНачала и ДатаКонца (расчет прихода и расхода).Посмотрим на структуру такого запроса:
SELECT
Вложенный.Фирма AS [Фирма $Справочник.Фирмы],
Вложенный.Клиент AS [Клиент $Справочник.Клиенты],
Вложенный.Док AS [Док $Документ],
SUM(Вложенный.НачОст) AS Начало,
SUM(Вложенный.Приход) AS Приход,
SUM(Вложенный.Расход) AS Расход,
SUM(Вложенный.НачОст + Вложенный.Приход - Вложенный.Расход) AS Конец
FROM (
-- 1. Срез итогов на начало месяца
SELECT
$Рег.Фирма AS Фирма,
$Рег.Клиент AS Клиент,
NULL AS Док,
$Рег.Остаток AS НачОст,
0 AS Приход,
0 AS Расход,
'' AS Позиция
FROM $РегистрИтоги.БанковскийКредит AS Рег
WHERE Рег.PERIOD = :НачалоМесяца
UNION ALL
-- 2. Докрутка остатка движениями до даты начала отчета
SELECT
$РегДв.Фирма,
$РегДв.Клиент,
NULL,
CASE WHEN РегДв.DEBRED = 1 THEN $РегДв.Остаток ELSE -$РегДв.Остаток END,
0,
0,
''
FROM $Регистр.БанковскийКредит AS РегДв
INNER JOIN _1SJOURN AS Жур ON Жур.IDDOC = РегДв.IDDOC
WHERE (РегДв.DATE_TIME_IDDOC >= :НачалоМесяцаПозиция)
AND (РегДв.DATE_TIME_IDDOC < :ДатаНачалаПозиция)
AND (Жур.CLOSED & 1 = 1) AND (Жур.ISMARK = ' ')
UNION ALL
-- 3. Обороты за выбранный интервал отчета
SELECT
$РегДв.Фирма,
$РегДв.Клиент,
РегДв.IDDOC,
0,
CASE WHEN РегДв.DEBRED = 1 THEN $РегДв.Остаток ELSE 0 END,
CASE WHEN РегДв.DEBRED = 0 THEN $РегДв.Остаток ELSE 0 END,
РегДв.DATE_TIME_IDDOC
FROM $Регистр.БанковскийКредит AS РегДв
INNER JOIN _1SJOURN AS Жур ON Жур.IDDOC = РегДв.IDDOC
WHERE (РегДв.DATE_TIME_IDDOC >= :ДатаНачалаПозиция)
AND (РегДв.DATE_TIME_IDDOC <= :ДатаКонцаПозиция)
AND (Жур.CLOSED & 1 = 1) AND (Жур.ISMARK = ' ')
) AS Вложенный
GROUP BY
Вложенный.Фирма,
Вложенный.Клиент,
Вложенный.Док
ORDER BY
Вложенный.Фирма,
Вложенный.Клиент,
MAX(Вложенный.Позиция)
При составлении прямых запросов к таблицам DBF через SQLite или OLE DB учитываем следующие правила:
DATE_TIME_IDDOC, которое содержит метку времени и позицию документа в системе.WHERE на измерения регистра перечисляем их строго в том порядке, в котором они заданы в метаданных конфигуратора.[Поле $Справочник.ИмяСправочника] позволяет драйверу 1С++ автоматически преобразовать внутренние 9-значные идентификаторы в полноценные объекты платформы 1С при выгрузке в ТаблицуЗначений.