Как эффективно найти последний документ по дате для каждого договора из большого массива в 1С и что делать, если МАКСИМУМ работает некорректно?

Программист 1С v8.3 (Управляемые формы)
← На главную

При работе с информационными системами 1С часто возникает задача получить последний по дате документ для каждого элемента из большого списка, например, для каждого договора. Когда массив элементов насчитывает десятки тысяч записей, такие операции становятся критически важными с точки зрения производительности. Циклический перебор массива с выполнением запроса для каждого элемента, как показал опыт автора, может занимать часы. Кроме того, возникают ситуации, когда стандартная функция агрегации МАКСИМУМ(Дата), которая, казалось бы, должна решить проблему, возвращает неверные данные, указывая на старый документ при наличии более новых.

В этой статье мы подробно разберем, как эффективно решить задачу поиска последнего документа, используя оптимизированные запросы в 1С, а также проанализируем возможные причины некорректной работы функции МАКСИМУМ и предложим методы диагностики и альтернативные подходы для получения гарантированно одного последнего документа.

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

Первоначальная проблема, с которой сталкиваются многие разработчики, заключается в низкой производительности при обработке большого объема данных. Если мы попытаемся перебирать массив из более чем 20 тысяч договоров в цикле, выполняя отдельный запрос для каждого договора, это неизбежно приведет к очень долгому выполнению. Каждый запрос — это отдельное обращение к базе данных, что при большом количестве операций создает колоссальную нагрузку.

Оптимальным решением является использование пакетных запросов. Пакетный запрос позволяет выполнить несколько запросов к базе данных за одно обращение, используя временные таблицы для промежуточных результатов. Такой подход значительно снижает накладные расходы на взаимодействие с СУБД и позволяет СУБД лучше оптимизировать выполнение всей операции.

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

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

  2. Шаг 2: Затем мы выполняем второй запрос, который соединяет исходную таблицу документов с созданной временной таблицей. Условия соединения будут включать как ссылку на договор, так и полученную максимальную дату. Это позволит нам отобрать только те документы, которые соответствуют максимальной дате для каждого договора.

Давайте посмотрим на пример такого запроса, основанного на коде автора, но с учетом рекомендаций:


// Шаг 1: Получаем максимальную дату для каждого договора из переданного массива
ВЫБРАТЬ
    Выгрузка.Договор КАК Договор,
    МАКСИМУМ(Выгрузка.Ссылка.Дата) КАК ДатаПоследнегоДокумента
ПОМЕСТИТЬ ВТ_ПоследниеДатыДоговоров
ИЗ
    Документ.ВыгрузкаНБКИ_3_0_R.Контрагенты КАК Выгрузка
ГДЕ
    Выгрузка.Договор В(&МассивДоговоровСсылок) // Важно: передавать ССЫЛКИ на договоры, а не их наименования
СГРУППИРОВАТЬ ПО
    Выгрузка.Договор
;

////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Соединяем с временной таблицей для получения самих документов по максимальной дате
ВЫБРАТЬ
    ВТ_ПоследниеДатыДоговоров.Договор КАК Договор,
    ВТ_ПоследниеДатыДоговоров.Договор.Дата КАК ДатаДоговора,
    ВТ_ПоследниеДатыДоговоров.Договор.мфоДатаЗакрытия КАК ДатаЗакрытияДоговора,
    ВТ_ПоследниеДатыДоговоров.ДатаПоследнегоДокумента КАК ДатаПоследнейВыгрузкиНБКИ,
    Выгрузка.Ссылка.Номер КАК ВыгрузкаНБКИНомер,
    Выгрузка.Ссылка КАК ВыгрузкаНБКИСсылка // Ссылка на документ для расшифровки
ИЗ
    ВТ_ПоследниеДатыДоговоров КАК ВТ_ПоследниеДатыДоговоров
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.ВыгрузкаНБКИ_3_0_R.Контрагенты КАК Выгрузка
    ПО ВТ_ПоследниеДатыДоговоров.Договор = Выгрузка.Договор
    И ВТ_ПоследниеДатыДоговоров.ДатаПоследнегоДокумента = Выгрузка.Ссылка.Дата
;

Рассмотрим ключевые моменты этого запроса:

  1. Параметр &МассивДоговоровСсылок: Крайне важно передавать в качестве параметра не строковые наименования договоров, а их ссылки на объекты. Использование Договор.Наименование В(&Договоры) заставляет СУБД сравнивать строковые значения, что медленнее и может быть неточным (например, из-за невидимых пробелов или различий в регистре). Передача ссылок позволяет использовать индексы по полю Договор, что значительно ускоряет выполнение запроса.

  2. МАКСИМУМ(Выгрузка.Ссылка.Дата): Эта функция корректно выбирает документ с самой поздней датой и временем. В 1С дата документа всегда включает в себя время с точностью до секунды. Поэтому, если у нас есть несколько документов с одним и тем же календарным днём, но разным временем, МАКСИМУМ выберет тот, у которого время позднее.

  3. ПОМЕСТИТЬ ВТ_ПоследниеДатыДоговоров: Создание временной таблицы – это основа пакетного запроса. Она позволяет эффективно хранить промежуточные агрегированные данные.

  4. ВНУТРЕННЕЕ СОЕДИНЕНИЕ: Соединение по двум условиям (Договор И Дата) гарантирует, что мы выбираем только те строки исходной табличной части, которые соответствуют найденной максимальной дате для каждого договора.

Этот подход является стандартным и, как правило, высокопроизводительным. Однако, если в один и тот же момент времени (с точностью до секунды) по одному договору было проведено несколько документов, данный запрос вернет их все. В большинстве случаев это допустимо, но если нам нужен строго один документ, потребуется дополнительная логика, которую мы рассмотрим ниже.

Диагностика проблем, если функция МАКСИМУМ возвращает неверные данные

Автор в своем сообщении 5 указал, что, несмотря на кажущуюся корректность запроса, функция МАКСИМУМ(Выгрузка.Ссылка.Дата) для некоторых договоров возвращает дату от 21.11.2022, хотя в базе существуют документы за 08.12.2022, 07.01.2023 и более поздние. Это критическая ситуация, поскольку она указывает на то, что запрос не видит или некорректно обрабатывает существующие данные. «Чудес не бывает», как верно подмечено в сообщении 11, и такое поведение почти всегда вызвано проблемами с данными или неправильной интерпретацией условий.

Мы предлагаем выполнить следующие шаги для диагностики:

  1. Проверка передаваемого параметра &МассивДоговоровСсылок:

    • Ссылки вместо наименований: Убедитесь, что вы передаете в параметр запроса коллекцию ссылок на объекты-договоры, а не их наименования. Использование Договор.Наименование В(&Договоры) в условии ГДЕ может привести к тому, что по каким-то причинам наименование договора в массиве не совпадает с наименованием в базе данных (например, из-за невидимых символов, разного регистра, пробелов в начале/конце). Ссылки всегда уникальны и гарантируют точное совпадение.

    • Полнота массива: Удостоверьтесь, что проблемные договоры (по которым функция МАКСИМУМ возвращает неверную дату) действительно присутствуют в массиве, который вы передаете в запрос.

  2. Проверка полноты данных в источнике (табличной части):

    • В консоли запросов или непосредственно в конфигурации откройте проблемный документ, в табличной части которого находится договор, и вручную убедитесь, что этот договор и все более поздние документы (которые, по вашим сведениям, существуют) действительно присутствуют в табличной части Документ.ВыгрузкаНБКИ_3_0_R.Контрагенты.

    • Выполните отдельный запрос без агрегации для конкретного проблемного договора, чтобы увидеть все записи, которые принадлежат ему в этой табличной части:

      
ВЫБРАТЬ
    Выгрузка.Ссылка.Дата КАК ДатаДокумента,
    Выгрузка.Ссылка КАК СсылкаДокумента,
    Выгрузка.Договор КАК Договор
ИЗ
    Документ.ВыгрузкаНБКИ_3_0_R.Контрагенты КАК Выгрузка
ГДЕ
    Выгрузка.Договор = &КонкретныйПроблемныйДоговор
УПОРЯДОЧИТЬ ПО
    Выгрузка.Ссылка.Дата УБЫВ

      Проанализируйте полученные результаты. Действительно ли там есть все ожидаемые даты? Если этот запрос показывает более поздние даты, чем ваш агрегатный запрос, это указывает на проблему с агрегацией или соединением.

  3. Анализ полей для сравнения и их точности:

    • Убедитесь, что вы сравниваете именно дату документа (Выгрузка.Ссылка.Дата), а не дату самого договора или какую-либо другую дату. Иногда путаница в полях может привести к некорректным результатам.

    • 1С хранит даты с точностью до секунды. Если у вас есть два документа, один за 08.12.2022 07:01:45 и другой за 08.12.2022 07:01:46, функция МАКСИМУМ корректно выберет второй. Проверьте, что в исходных данных нет ошибки в представлении времени.

  4. Проверка данных на "скрытые" различия в договорах:

    • Иногда встречаются ситуации, когда на вид одинаковые договоры на самом деле являются разными объектами в базе данных (например, созданы два раза, имеют разные GUID). Функция МАКСИМУМ будет работать для каждого из этих "разных" объектов. В консоли запросов внимательно посмотрите на поле Договор (именно ссылку) для документов, которые, по вашему мнению, относятся к одному и тому же договору, но дают разные максимальные даты. Если ссылки на договор разные, то это разные договоры для системы.

  5. Использование консоли запросов для пошагового анализа:

    • Используйте консоль запросов для выполнения каждой части пакетного запроса отдельно. Сначала выполните только первый запрос (который помещает данные во временную таблицу ВТ_ПоследниеДатыДоговоров) и посмотрите его результат. Убедитесь, что для проблемного договора там указана правильная максимальная дата.

    • Если первая часть запроса уже возвращает неверную дату, это указывает на то, что проблема кроется в самом агрегировании или в условии ГДЕ. Если первая часть возвращает верную дату, но итоговый запрос нет, значит, проблема в условиях СОЕДИНЕНИЯ во втором запросе.

Имитация оконных функций (ROW_NUMBER) для сложных случаев

Как мы уже упоминали, стандартный подход с МАКСИМУМ(Дата) может вернуть несколько документов, если по одному договору в одну и ту же секунду было проведено несколько записей. Если нам требуется гарантированно получить только один "самый последний" документ, даже при совпадении даты и времени, мы можем имитировать поведение оконных функций SQL, таких как ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...). В языке запросов 1С нет прямого аналога этой функции, но мы можем достичь схожего результата, добавив в агрегацию дополнительные критерии, которые гарантируют уникальность.

Чаще всего, если даты (включая секунды) совпадают, мы выбираем "последний" документ по какому-либо другому уникальному или упорядочиваемому полю, например, по его внутренней ссылке (Ссылка) или номеру. Поскольку ссылки документов генерируются последовательно, МАКСИМУМ(Ссылка) или МИНИМУМ(Ссылка) для одной и той же даты и времени помогут нам выбрать один уникальный документ.

Вот как можно модифицировать запрос для гарантированного получения одного последнего документа:


// Шаг 1: Получаем максимальную дату И максимальную (или минимальную) ссылку для каждого договора
// Это позволяет однозначно идентифицировать "последний" документ, даже если несколько имеют одинаковую дату+время
ВЫБРАТЬ
    Выгрузка.Договор КАК Договор,
    МАКСИМУМ(Выгрузка.Ссылка.Дата) КАК ДатаПоследнегоДокумента,
    МАКСИМУМ(Выгрузка.Ссылка) КАК СсылкаПоследнегоДокумента // Используем Ссылку как второй критерий сортировки
ПОМЕСТИТЬ ВТ_ПоследниеДатыИСсылкиДоговоров
ИЗ
    Документ.ВыгрузкаНБКИ_3_0_R.Контрагенты КАК Выгрузка
ГДЕ
    Выгрузка.Договор В(&МассивДоговоровСсылок)
СГРУППИРОВАТЬ ПО
    Выгрузка.Договор
;

////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Соединяем с временной таблицей для получения ОДНОГО документа по максимальной дате и ссылке
ВЫБРАТЬ
    ВТ_ПоследниеДатыИСсылкиДоговоров.Договор КАК Договор,
    ВТ_ПоследниеДатыИСсылкиДоговоров.Договор.Дата КАК ДатаДоговора,
    ВТ_ПоследниеДатыИСсылкиДоговоров.Договор.мфоДатаЗакрытия КАК ДатаЗакрытияДоговора,
    ВТ_ПоследниеДатыИСсылкиДоговоров.ДатаПоследнегоДокумента КАК ДатаПоследнейВыгрузкиНБКИ,
    Выгрузка.Ссылка.Номер КАК ВыгрузкаНБКИНомер,
    Выгрузка.Ссылка КАК ВыгрузкаНБКИСсылка
ИЗ
    ВТ_ПоследниеДатыИСсылкиДоговоров КАК ВТ_ПоследниеДатыИСсылкиДоговоров
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.ВыгрузкаНБКИ_3_0_R.Контрагенты КАК Выгрузка
    ПО ВТ_ПоследниеДатыИСсылкиДоговоров.Договор = Выгрузка.Договор
    И ВТ_ПоследниеДатыИСсылкиДоговоров.ДатаПоследнегоДокумента = Выгрузка.Ссылка.Дата
    И ВТ_ПоследниеДатыИСсылкиДоговоров.СсылкаПоследнегоДокумента = Выгрузка.Ссылка // Дополнительное условие для уникальности
;

В этом модифицированном подходе мы не только находим максимальную дату, но и максимальную ссылку (МАКСИМУМ(Выгрузка.Ссылка)) для этой даты. Затем при соединении мы используем оба этих условия, что гарантирует выбор только одного уникального документа, даже если по одной дате/времени их было несколько.

Это метод более надежен для получения строго одного "последнего" документа, но он не решает проблему, если МАКСИМУМ(Выгрузка.Ссылка.Дата) изначально возвращает неверную дату (как в случае автора), поскольку проблема в этом случае лежит глубже – в данных или условиях выборки, а не в неоднозначности "последнего" при одинаковых датах.

Дополнительные рекомендации по оптимизации запросов

Для обеспечения максимальной производительности при работе с большими объемами данных в 1С всегда следует придерживаться следующих рекомендаций:

  1. Использование индексов: Убедитесь, что поля, используемые в условиях соединения, группировки и фильтрации (например, Выгрузка.Договор, Выгрузка.Ссылка.Дата), проиндексированы. В 1С платформа автоматически создает индексы для многих стандартных полей (таких как ссылка, дата документа), но для полей табличных частей или специфических реквизитов может потребоваться анализ плана запроса или даже создание дополнительных индексов средствами конфигурации или СУБД. Хорошие индексы – это основа быстрой работы с большими таблицами.

  2. Разделение сложных запросов на пакетные: Если ваш запрос становится слишком громоздким, содержит много соединений или сложные вычисления, разбейте его на несколько простых запросов с использованием временных таблиц. Это не только улучшает читаемость кода, но и позволяет СУБД эффективнее оптимизировать каждый этап выполнения, обрабатывая промежуточные результаты.

  3. Избегание "получения данных через точку" для составных типов в условиях ГДЕ: Хотя в запросах 1С можно обращаться к реквизитам через точку (например, Договор.Наименование), использование таких выражений в условиях ГДЕ или СОЕДИНЕНИЯ, особенно для составных типов, может быть неоптимальным. Желательно сначала получить ссылки на объекты (как Договор), а затем использовать их напрямую в фильтрации. Это позволяет СУБД напрямую использовать индексы по ссылочным полям.

  4. Анализ плана запроса: Если вы столкнулись с медленным запросом, который, по вашему мнению, должен работать быстрее, обязательно используйте инструменты анализа плана запроса вашей СУБД (например, SQL Server Management Studio для MS SQL Server, pgAdmin для PostgreSQL). План запроса покажет, как СУБД выполняет запрос, какие индексы использует (или не использует), какие операции являются наиболее ресурсоемкими (сканирование таблиц вместо поиска по индексу). Это поможет выявить "узкие" места и точнее определить, что именно нужно оптимизировать.

В заключение, эффективный поиск последнего документа по дате в 1С для большого массива данных требует применения пакетных запросов с временными таблицами. Если функция МАКСИМУМ ведет себя не так, как ожидалось, это практически всегда указывает на проблемы с данными, некорректно сформированный параметр или ошибочные условия соединения. Тщательная диагностика с использованием консоли запросов и проверка целостности данных помогут выявить и устранить истинную причину проблемы, а применение дополнительных критериев при агрегации гарантирует получение строго одного "последнего" документа при любых условиях.

← На главную