Как ускорить поиск последнего документа по реквизиту с помощью SQLite в 1С 7.7?

← На главную

При переходе на использование прямых запросов с помощью внешней компоненты 1sqlite в среде 1С:Предприятие 7.7 разработчики часто сталкиваются с парадоксальной ситуацией: стандартные методы платформы работают значительно быстрее, чем "высокотехнологичные" прямые SQL-запросы. Особенно ярко это проявляется в задачах поиска последнего документа определенного вида по конкретному контрагенту (или иному аналитическому разрезу).

В этой статье мы подробно проанализируем, почему простой и интуитивно понятный SQL-запрос начинает "тормозить", когда в базе данных нет документов по выбранному контрагенту, выясним внутренние механизмы работы СУБД и построим максимально быстрый и оптимизированный прямой запрос, который будет работать мгновенно в любых ситуациях.

Постановка проблемы: сравнение штатного метода и прямого запроса

Рассмотрим классическую задачу: нам необходимо получить последний документ определенного вида (например, "Реализация") по выбранному контрагенту. В системе настроена графа отбора по реквизиту "Контрагент".

Разработчик сопоставил два подхода к решению этой задачи.

Подход 1. Использование стандартного метода 1С 7.7

Код стандартной выборки выглядит следующим образом:


Док = СоздатьОбъект("Документ");
Док.ОбратныйПорядок(1);       
Док.ВыбратьПоЗначению(, ТекущаяДата(), "Контрагент", пКонтрагент);
Пока Док.ПолучитьДокумент() = 1 Цикл  
    Если Док.Вид() <> пВид Тогда
        Продолжить;    
    КонецЕсли;
    Если Док.ПолучитьАтрибут(ИД_Контрагент) = пКонтрагент Тогда
        Прервать;
    КонецЕсли;
КонецЦикла;

Подход 2. Использование прямого SQL-запроса через 1sqlite

Прямой запрос к таблице конкретного документа выглядит так:


ТекстЗапроса = "SELECT 
               |  Док.IDDOC [Документ  $Документ." + пВид + "] 
               |, Док.Контрагент [Контрагент $Справочник.Контрагенты]
               |FROM [Документ." + пВид + "] AS Док 
               |WHERE (Док.Контрагент = :ВыбКонтрагент)  
               |ORDER BY Док.IDDOC DESC LIMIT 1
               |";

При сравнении скорости работы этих алгоритмов были получены неожиданные результаты:

Если по контрагенту в базе есть хотя бы один документ, оба метода отрабатывают мгновенно — примерно за 1 миллисекунду.
Если документов по контрагенту в базе нет вообще, стандартный метод 1С по-прежнему отрабатывает за 1 миллисекунду, в то время как прямой запрос через SQLite тратит около 150 миллисекунд (в 150 раз медленнее!).

Давайте выясним причину такого поведения СУБД и разберем по шагам физические процессы, происходящие на уровне дисковой подсистемы.

Почему прямой запрос SQLite работает медленно при отсутствии данных?

Для того чтобы понять природу этой задержки, проанализируем план выполнения SQL-запроса движком SQLite.

В физической таблице конкретного документа (например, таблице DH*) реквизит "Контрагент" по умолчанию не имеет собственного индекса. Единственным индексированным полем в этой таблице является IDDOC (уникальный идентификатор документа), который выступает в роли первичного ключа или индекса сортировки.

Когда мы выполняем условие ORDER BY Док.IDDOC DESC LIMIT 1, планировщик SQLite принимает решение использовать существующий индекс по полю IDDOC. Движок начинает сканировать таблицу в обратном порядке (от самых свежих документов к более старым).

Рассмотрим два сценария:

Сценарий А: Документ существует. SQLite читает записи с конца таблицы по индексу IDDOC и для каждой записи проверяет условие WHERE Док.Контрагент = :ВыбКонтрагент. Поскольку нужный документ обычно создан недавно, он находится практически сразу (в первых строках выборки). Благодаря конструкции LIMIT 1 выполнение запроса немедленно прерывается. Время работы — 1 мс.
Сценарий Б: Документа не существует. SQLite точно так же начинает читать записи с конца по индексу IDDOC. Он проверяет первую запись, вторую, третью... и так до тех пор, пока не дойдет до самой первой записи в таблице. Не найдя ни одного соответствия, движок завершает работу. Происходит так называемый Full Index Scan (или полное сканирование таблицы). На базах данных реального объема (сотни тысяч или миллионы документов) этот процесс занимает ощутимое время — те самые 150 мс и более.

Внутреннее устройство базы данных 1С 7.7: секрет быстродействия штатных методов

Почему же стандартный метод 1С ВыбратьПоЗначению() работает быстро в обоих случаях? Секрет кроется в том, что платформа 1С:Предприятие 7.7 никогда не сканирует таблицы документов для поиска отборов. Вместо этого она использует специализированную системную таблицу — таблицу отборов и подчиненности документов 1SCRDOC (в терминах SQLite она обычно представлена как __1S_CRDOC).

Эта таблица содержит информацию обо всех связях и графах отбора. Она имеет следующие ключевые поля:

MDID — идентификатор метаданных графы отбора (указывает, по какому именно реквизиту сделан отбор).
PARENTVAL — типизированная строка (длиной 23 символа), содержащая идентификатор объекта отбора (например, ID контрагента с системным префиксом).
CHILDDATE — дата подчиненного документа.
CHILDTIME — время подчиненного документа.
CHILDID — идентификатор подчиненного документа (IDDOC).

Самое главное — по этой таблице построен мощный составной индекс PARENT:

MDID + PARENTVAL + CHILDDATE + CHILDTIME + CHILDID

Когда вы вызываете штатный метод ВыбратьПоЗначению(), платформа обращается напрямую к индексу PARENT таблицы 1SCRDOC. Она выполняет операцию Index Seek (поиск по точному совпадению первых полей индекса — MDID и `PARENTVAL`). Если записей для данного контрагента нет, индекс сразу же возвращает пустой результат (за 0-1 мс). Сканирования физической таблицы документов не происходит.

Решение: оптимизируем прямой запрос с использованием таблицы отборов 1SCRDOC

Чтобы заставить SQLite работать с максимальной скоростью в любых условиях, нам необходимо переписать запрос так, чтобы он использовал системную таблицу отборов и ее эффективные составные индексы.

Рассмотрим правильный вариант SQL-запроса для получения последнего документа по контрагенту:


SELECT 
    Отбор.CHILDID [Документ $Документ.Реализация]
FROM __1S_CRDOC AS Отбор
WHERE Отбор.MDID = :ГрафаОтбора.Контрагент
  AND Отбор.PARENTVAL = :ВыбКонтрагент
ORDER BY Отбор.CHILDDATE DESC, Отбор.CHILDTIME DESC, Отбор.CHILDID DESC
LIMIT 1

Давайте подробно проанализируем, почему данный запрос будет работать мгновенно:

Условия в блоке WHERE накладывают жесткие ограничения на первые два поля составного индекса: MDID (идентификатор графы отбора) и PARENTVAL (ссылка на контрагента). Это позволяет СУБД мгновенно позиционироваться на нужной ветке индексного дерева B-Tree.
Сортировка в блоке ORDER BY в точности повторяет оставшиеся поля составного индекса (CHILDDATE, CHILDTIME, CHILDID), но в обратном порядке (DESC). SQLite достаточно умен, чтобы не производить физическую сортировку записей в памяти. Он просто считывает индекс в обратном направлении (Index Backward Scan).
Инструкция LIMIT 1 останавливает чтение на первой же записи индекса.
Если документов по контрагенту нет, поиск по индексу мгновенно возвращает пустой результат, не тратя время на холостые циклы сканирования.

Передача параметров и особенности типизации в 1sqlite

При написании запросов к таблице 1SCRDOC крайне важно правильно передавать параметры. Поле PARENTVAL имеет фиксированную длину 23 символа и содержит типизированную строку неопределенного вида. Для справочника "Контрагенты" эта строка имеет следующий формат:

O1 + [ID вида справочника] + [ID элемента справочника] (дополненная пробелами до 23 символов).

Если вы используете современный метапарсер компоненты 1sqlite, вам не нужно вручную формировать эту сложную строку. Парсер сделает это автоматически, если правильно описать типизацию параметров. Посмотрим на практический пример реализации в коде 1С 7.7:


База = СоздатьОбъект("SQLiteDatabase");
Запрос = База.НовыйЗапрос();

// Задаем параметры запроса
Запрос.УстановитьПараметр("ВыбКонтрагент", пКонтрагент); // Передаем ссылку на элемент справочника

ТекстЗапроса = "
|SELECT 
|    Отбор.CHILDID [Документ $Документ.Реализация]
|FROM __1S_CRDOC AS Отбор
|WHERE Отбор.MDID = :ГрафаОтбора.Контрагент
|  AND Отбор.PARENTVAL = :ВыбКонтрагент
|ORDER BY Отбор.CHILDDATE DESC, Отбор.CHILDTIME DESC, Отбор.CHILDID DESC
|LIMIT 1
|";

Таб = Запрос.Выполнить(ТекстЗапроса);
Если Таб.КоличествоСтрок() > 0 Тогда
    ПоследнийДок = Таб.ПолучитьЗначение(1, "Документ");
    Сообщить("Последний документ найден: " + ПоследнийДок);
Иначе
    Сообщить("У данного контрагента нет документов выбранного вида.");
КонецЕсли;

Альтернативный вариант: соединение таблицы отборов с журналом документов

Иногда требуется не просто получить идентификатор документа, но и проверить его состояние (например, проведен ли документ, не помечен ли на удаление) или получить общие реквизиты (номер, дату, время). В этом случае таблицу отборов необходимо связать с общим журналом документов 1SJOURN (в SQLite — __1S_JOURN).

Проанализируем пример такого запроса:


SELECT 
    Журнал.IDDOC [Документ :Документ],
    Журнал.IDDOCDEF AS ВидДокумента,
    Журнал.DOCNO AS НомерДок
FROM __1S_CRDOC AS Отбор
INNER JOIN __1S_JOURN AS Журнал ON Журнал.IDDOC = Отбор.CHILDID
WHERE Отбор.MDID = :ГрафаОтбора.Контрагент
  AND Отбор.PARENTVAL = :ВыбКонтрагент
  AND Журнал.CLOSED & 1 = 1 -- Фильтр: только проведенные документы
ORDER BY Отбор.CHILDDATE DESC, Отбор.CHILDTIME DESC, Отбор.CHILDID DESC
LIMIT 1

Обратите внимание на конструкцию Журнал.CLOSED & 1 = 1. В системе 1С 7.7 состояние проведения документа хранится в виде битовой маски в поле CLOSED таблицы журнала. Использование побитового "И" позволяет быстро отсечь непроведенные документы прямо на уровне СУБД, сохраняя колоссальную скорость работы.