Как ускорить работу запросов SQLite в 1С 7.7 при соединении больших таблиц?

← На главную

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

Разберем причину: почему запрос начинает тормозить?

Проанализируем типичную ситуацию. Допустим, у нас есть справочник Клиенты, в котором около 100 000 записей, и небольшая таблица значений тзЗамены на 200–300 строк. Мы хотим найти клиентов по их идентификационному номеру (ИИН). Рассмотрим пример запроса, который вызывает серьезные задержки:


ТекстЗапроса = "
    |SELECT 
    |    Substr(тзЗамены.СтароеЗначение,7,9) as [СтарыйЭлемент :Справочник.Клиенты],
    |    СпрК.ID as [НовыйЭлемент :Справочник.Клиенты]
    |FROM
    |    тзЗамены
    |    INNER JOIN [Справочник.Клиенты] as СпрК 
    |        ON (тзЗамены.ИИН = СпрК.ИИН) AND (Trim(СпрК.ИИН) <> '')
    |";

Главная проблема здесь кроется в условии Trim(СпрК.ИИН) <> ''. Давайте выясним, что происходит "под капотом" SQLite. Чтобы выполнить функцию Trim(), движку базы данных необходимо прочитать каждую строку из таблицы справочника, применить к значению функцию и только потом сравнить результат. Это полностью блокирует использование индекса по полю ИИН. Вместо быстрого поиска по индексу (Index Seek), система переходит к полному сканированию таблицы (Full Table Scan), что для 100 000 записей является крайне тяжелой операцией.

Важный момент: использование любых функций (Trim, Substr, Upper и др.) или арифметических операций над полями таблиц в секциях ON или WHERE делает условие "не-SARGable" (Search Argumentable), то есть непригодным для поиска по индексу.

Решение №1: Предварительная подготовка данных

Вместо того чтобы заставлять SQL-движок фильтровать пустые значения в процессе соединения, мы можем сделать это заранее средствами 1С в исходной таблице значений. Рассмотрим, как изменить алгоритм:

Удалим из таблицы значений тзЗамены все строки, где ИИН пустой, еще до того, как передадим ее в УложитьТЗ().
В самом SQL-запросе уберем лишние проверки, оставив только прямое сравнение полей.

Посмотрим на исправленный код 1С:


тз = СоздатьОбъект("ТаблицаЗначений");
ТаблицаЗамен.Выгрузить(тз,,,"СтароеЗначение");
тз.НоваяКолонка("ИИН","Строка",12);

// Предварительная обработка и фильтрация
тз.ВыбратьСтроки();
Пока тз.ПолучитьСтроку() = 1 Цикл
    _Элемент = тз.СтароеЗначение;
    тз.ИИН = _Элемент.ИИН;
КонецЦикла;

// Удаляем пустые ИИН, чтобы не фильтровать их в SQL
тз.ВыбратьСтроки();
Колво = тз.КоличествоСтрок();
Пока Колво > 0 Цикл
    тз.ПолучитьСтрокуПоНомеру(Колво);
    Если ПустаяСтрока(тз.ИИН) = 1 Тогда
        тз.УдалитьСтроку(Колво);
    КонецЕсли;
    Колво = Колво - 1;
КонецЦикла;

глБД_SQLite.УложитьТЗ(тз, "тзЗамены");

Теперь SQL-запрос станет максимально простым и быстрым:


ТекстЗапроса = "
    |SELECT 
    |    Substr(тзЗамены.СтароеЗначение,7,9) as [СтарыйЭлемент :Справочник.Клиенты],
    |    СпрК.ID as [НовыйЭлемент :Справочник.Клиенты]
    |FROM
    |    тзЗамены
    |    INNER JOIN [Справочник.Клиенты] as СпрК 
    |        ON тзЗамены.ИИН = СпрК.ИИН
    |WHERE 
    |    СпрК.ismark <> '*'
    |";

В данном случае, так как в тзЗамены уже нет пустых ИИН, условие тзЗамены.ИИН = СпрК.ИИН автоматически отсечет пустые ИИН в справочнике, используя при этом индекс.

Решение №2: Управление планом запроса через типы соединений

Проанализируем ситуацию, когда оптимизатор SQLite ошибается с порядком таблиц. При использовании INNER JOIN движок сам решает, какую таблицу просматривать первой. Если он решит сначала сканировать справочник Клиенты, а потом искать соответствия в маленькой тзЗамены, запрос будет медленным.

Для управления этим процессом можно использовать LEFT JOIN. В SQLite LEFT JOIN жестко фиксирует порядок: левая таблица всегда обрабатывается первой. Если мы поставим тзЗамены слева, SQLite будет брать каждую строку из нашей маленькой ТЗ (всего 200 итераций) и мгновенно находить клиента в справочнике по индексу.


ТекстЗапроса = "
    |SELECT 
    |    Substr(тзЗамены.СтароеЗначение,7,9) as [СтарыйЭлемент :Справочник.Клиенты],
    |    СпрК.ID as [НовыйЭлемент :Справочник.Клиенты]
    |FROM
    |    тзЗамены
    |LEFT JOIN [Справочник.Клиенты] as СпрК 
    |    ON СпрК.ИИН = тзЗамены.ИИН
    |WHERE 
    |    СпрК.ID IS NOT NULL AND СпрК.ismark <> '*'
    |";

Решение №3: Использование подзапросов для изоляции сложных условий

Иногда нам нужно отфильтровать помеченные на удаление объекты (ismark = '*'), но это условие также может сбивать оптимизатор. Чтобы гарантировать, что поиск по ИИН произойдет первым, можно вынести фильтрацию пометки удаления во внешний запрос или использовать подзапрос.

Рассмотрим пример с подзапросом:


ТекстЗапроса = "
    |SELECT 
    |    T.СтарыйЭлемент, 
    |    T.НовыйЭлемент
    |FROM (
    |    SELECT
    |        Substr(тзЗамены.СтароеЗначение,7,9) as СтарыйЭлемент,
    |        СпрК.ID as НовыйЭлемент,
    |        СпрК.ismark as Пометка
    |    FROM
    |        тзЗамены
    |    INNER JOIN [Справочник.Клиенты] as СпрК
    |        ON СпрК.ИИН = тзЗамены.ИИН
    |) as T
    |WHERE T.Пометка <> '*'
    |";

В этом случае SQLite сначала выполнит соединение по индексу (самая быстрая часть), а затем отфильтрует результат по полю ismark.

Полезные советы по работе с SQLite в 1С 7.7

При оптимизации запросов к DBF через SQLite важно помнить о следующих нюансах:

Фиксированная длина строк: В 1С 7.7 строки в базе данных всегда дополняются пробелами до максимальной длины. Если поле ИИН имеет длину 20 символов, а вы ищете строку "12345", индекс может не сработать. Всегда проверяйте, чтобы длины сравниваемых строк совпадали (дополняйте пробелами в ТЗ, если нужно).
Инструмент EXPLAIN: Если запрос все равно работает медленно, добавьте в начало текста запроса фразу EXPLAIN QUERY PLAN. Это позволит увидеть, как SQLite планирует выполнять запрос: увидите ли вы фразу "SCAN TABLE" (плохо) или "SEARCH TABLE ... USING INDEX" (хорошо).
Чувствительность к регистру: Помните, что индексы в 1С 7.7 создаются в верхнем регистре. Если вы используете строковый поиск, убедитесь, что компонента 1sqlite настроена на использование правильных индексов 1С.
Оператор LIKE: Избегайте использования LIKE '%значение%'. Это гарантированно убивает производительность. Поиск LIKE 'значение%' (с процентом только в конце) может использовать индекс, если включена соответствующая опция в драйвере.

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