При разработке отчетов и CRM-панелей в среде 1С:Предприятие 7.7 с использованием внешней компоненты 1SQLite часто возникает задача: выбрать полный список контрагентов и для каждого из них определить самый последний документ определенного вида (например, событие, реализацию или заказ). Если решать эту задачу перебором с отдельными запросами по каждому клиенту или неоптимальным прямым соединением таблиц, выполнение запроса может занимать десятки минут или приводить к ошибкам нехватки оперативной памяти.
Разберем причины возникновения медленной работы запросов к базам DBF через SQLite и рассмотрим правильные варианты построения SQL-запросов для максимального быстродействия.
Рассмотрим типичную ошибку при формировании запроса. Разработчик пытается объединить справочник контрагентов, табличную часть документа и журнал документов _1S.JOURN (или Журнал), после чего выполняет группировку:
SELECT
Контрагенты.ID [Ссылка :Справочник.Контрагенты],
Контрагенты.Менеджер [Менеджер :Справочник.Менеджеры],
срм_Событие.IDDOC [срм_СобытиеСсылка :Документ.срм_Событие],
Max(Журнал.DATE) AS МаксимумДокумент_дата
FROM [Справочник.Контрагенты] AS Контрагенты
LEFT OUTER JOIN [Документ.срм_Событие] AS срм_Событие ON Контрагенты.ID = срм_Событие.шКонтрагент
LEFT OUTER JOIN [_1S.JOURN] AS Журнал ON срм_Событие.IDDOC = Журнал.IDDOC
GROUP BY Контрагенты.ID
Проанализируем, почему такой запрос работает крайне медленно (от 10 минут и дольше):
Max(Журнал.DATE) без правильной привязки к позиции документа заставляет движок просматривать все строки без использования готовых индексов журнала.IDDOCDEF приводит к полному сканированию огромного объема данных.В платформе 1С 7.7 хронологическая позиция документа однозначно определяется составным системным индексом: idx_IDDOCDEF_DATE_TIME_IDDOC или idx_DATE_TIME_IDDOC. Применение агрегатной функции MAX() к этому составному полю позволяет гарантированно определить самый поздний документ без риска коллизий одинаковых дат.
Посмотрим на структуру эффективного запроса, где во вложенной таблице выбираются только последние документы нужного вида, а затем результат соединяется со справочником контрагентов:
SELECT
Контрагент.ID [Контрагент $Справочник.Контрагенты],
Контрагент.Менеджер [Менеджер $Справочник.Менеджеры],
Выборка.IDDOC [Документ $Документ.срм_Событие]
FROM [Справочник.Контрагенты] AS Контрагент
LEFT JOIN (
SELECT
срм_Событие.шКонтрагент AS Контрагент,
срм_Событие.IDDOC AS IDDOC
FROM Журнал
INNER JOIN [Документ.срм_Событие] AS срм_Событие ON срм_Событие.IDDOC = Журнал.IDDOC
WHERE Журнал.IDDOCDEF = :ВидДокумента.срм_Событие
GROUP BY срм_Событие.шКонтрагент
HAVING max(Журнал.idx_DATE_TIME_IDDOC)
) AS Выборка ON Выборка.Контрагент = Контрагент.ID
WHERE Контрагент.ISFOLDER = 2
Выясним преимущества этого подхода:
WHERE Журнал.IDDOCDEF = :ВидДокумента.срм_Событие и INNER JOIN сразу отсекает все лишние записи журналов.LEFT JOIN клиенты, по которым еще не было создано ни одного документа, также попадут в итоговую выборку с пустым значением реквизита документа.Контрагент.ISFOLDER = 2 оставляет в выборке только элементы справочника, пропуская группы.В некоторых сценариях (особенно на старых версиях движка SQLite) для извлечения идентификатора документа из строкового представления максимального индекса позиции используется функция взятия подстроки substr(). Так как идентификатор документа IDDOC занимает последние 9 символов составного индекса позиции, запрос можно записать следующим образом:
SELECT
Спр.ID [Клиент $Справочник.Контрагенты],
substr(Таб.МаксПозиция, -9, 9) [Док $Документ.срм_Событие]
FROM [Справочник.Контрагенты] AS Спр
LEFT JOIN (
SELECT
Док.шКонтрагент AS Контрагент,
MAX(Жур.idx_IDDOCDEF_DATE_TIME_IDDOC) AS МаксПозиция
FROM Журнал AS Жур
INNER JOIN [Документ.срм_Событие] AS Док ON Док.IDDOC = Жур.IDDOC
WHERE Жур.IDDOCDEF = :ВидДокумента.срм_Событие
GROUP BY Док.шКонтрагент
) AS Таб ON Таб.Контрагент = Спр.ID
WHERE Спр.ISFOLDER = 2
Обратите внимание: в SQLite вместо стандартной функции RIGHT(строка, длина) используется функция substr(строка, смещение, длина) с отрицательным смещением.
Важно помнить о спецификации движка SQLite (Bare Columns): если в запросе с GROUP BY вызывается агрегат MAX(поле), все незаагрегированные поля, указанные в SELECT (например, Журнал.IDDOC), берутся непосредственно из той строки, для которой значение агрегата оказалось максимальным. Поэтому конструкция с прямым указанием IDDOC и MAX(idx) работает корректно и компактно.
Скорость выполнения запросов с составными индексами и агрегацией напрямую зависит от версии библиотеки SQLite, используемой внешней компонентой:
MAX() в сочетании с группировками, выполняя сканирование всей таблицы. Запрос мог выполняться 20–30 секунд и более.Если запрос по-прежнему выполняется долго, рекомендуется обновить используемую DLL-библиотеку 1SQLite на более свежую сборку.