В практике разработки на платформе 1С программисты иногда сталкиваются с «мистическим» поведением запросов: один и тот же код при одинаковых данных выдает разные результаты. Особенно часто это проявляется при использовании конструкции ВЫБРАТЬ ПЕРВЫЕ 1 в сочетании с виртуальными таблицами СрезПоследних. Рассмотрим подробно, почему так происходит, как работает SQL-оптимизатор и как правильно писать такие запросы, чтобы результат всегда был предсказуемым.
Проанализируем ситуацию, описанную автором. Имеется запрос к виртуальной таблице регистра сведений СрезПоследних. Программист ожидает получить одну конкретную «последнюю» запись, используя ВЫБРАТЬ ПЕРВЫЕ 1. Однако при последовательных вызовах система возвращает то одну запись, то другую. При этом данные в базе не меняются.
Основная причина кроется в фундаментальном правиле работы реляционных СУБД: порядок строк в результирующем наборе не гарантирован, если он явно не указан в предложении УПОРЯДОЧИТЬ ПО. Рассмотрим по шагам, какие факторы заставляют SQL Server «перемешивать» результаты.
Разберем внутреннее устройство MS SQL Server. Когда СУБД читает данные из таблицы, она не обязательно начинает с первой физической страницы. В SQL Server реализован механизм «Advanced Scanning» (продвинутое сканирование), который также называют «карусельным сканированием». Если один запрос уже читает большую таблицу и находится на середине пути, а в этот момент поступает второй запрос к этой же таблице, оптимизатор может «подсадить» второй запрос к текущему сканированию первого.
В результате второй запрос сначала получит данные из середины таблицы, а дойдя до конца, «дочитает» начало. Без явного ORDER BY (в 1С это УПОРЯДОЧИТЬ ПО) строки будут выданы клиенту в том порядке, в котором они были прочитаны из буферного кэша. Это делает выборку ПЕРВЫЕ 1 абсолютно случайной с точки зрения бизнес-логики.
Проанализируем влияние многопоточности. Если сервер БД обладает несколькими ядрами и запрос признан достаточно «тяжелым», SQL Server строит параллельный план выполнения. Данные разбиваются на порции, и каждое ядро обрабатывает свою часть. Тот поток, который быстрее закончит чтение и обработку своей части данных, первым вернет результат в общий поток. Поскольку нагрузка на процессор меняется ежесекундно, порядок «финиша» этих потоков будет разным при каждом запуске. Без сортировки записи, попавшие в ВЫБРАТЬ ПЕРВЫЕ 1, будут зависеть от производительности ядер в конкретный микромомент времени.
Выясним причину, связанную со спецификой 1С. Автор темы указал, что накладывает условия через предложение ГДЕ, а не в параметрах виртуальной таблицы. Рассмотрим пример кода автора:
ВЫБРАТЬ ПЕРВЫЕ 1
ОтветственныеЛицаСрезПоследних.Период,
ОтветственныеЛицаСрезПоследних.Организация,
ОтветственныеЛицаСрезПоследних.Подписант КАК ОсновнаяПодпись
ИЗ
РегистрСведений.ОтветственныеЛица.СрезПоследних(&Период, ) КАК ОтветственныеЛицаСрезПоследних
ГДЕ
ОтветственныеЛицаСрезПоследних.Организация = &Организация
И ОтветственныеЛицаСрезПоследних.Документ = &СчетФактура
Такой подход критически опасен. Платформа 1С работает следующим образом:
ГДЕ.Подписант), а сортировки нет, то ПЕРВЫЕ 1 вернет случайную из них.Для получения стабильного и корректного результата необходимо выполнить три шага.
Это самое важное правило. Если вы используете ВЫБРАТЬ ПЕРВЫЕ, у вас обязательно должно быть предложение УПОРЯДОЧИТЬ ПО. СУБД должна понимать, какая запись считается первой. Обычно в регистрах сведений сортируют по Период УБЫВ. Если возможны записи в одну секунду, добавьте в сортировку дополнительные поля (например, Регистратор или НомерСтроки).
Рассмотрим, как оптимизировать запрос. Фильтры по измерениям должны находиться внутри параметров СрезПоследних(). Это позволит системе получить срез уже отфильтрованных данных, что работает в разы быстрее и стабильнее.
Проанализируем структуру регистра. Если в регистре несколько записей на один и тот же момент времени с одинаковыми измерениями (в случае, если какое-то поле ошибочно сделано ресурсом или реквизитом вместо измерения), то для СУБД эти записи равнозначны. В таком случае даже СрезПоследних вернет «неопределенный» результат. Убедитесь, что состав измерений обеспечивает уникальность записи в контексте бизнес-логики.
Перепишем запрос автора, внеся необходимые исправления:
ВЫБРАТЬ ПЕРВЫЕ 1
ОтветственныеЛицаСрезПоследних.Период КАК Период,
ОтветственныеЛицаСрезПоследних.Подписант КАК ОсновнаяПодпись
ИЗ
РегистрСведений.ОтветственныеЛица.СрезПоследних(&Период, Организация = &Организация И Документ = &СчетФактура) КАК ОтветственныеЛицаСрезПоследних
УПОРЯДОЧИТЬ ПО
Период УБЫВ,
ОтветственныеЛицаСрезПоследних.Подписант // Дополнительное поле для стабильности, если периоды совпадут
Важный момент: использование УПОРЯДОЧИТЬ ПО гарантирует, что даже при изменении планов обслуживания SQL Server, обновлении статистики или очистке кэша планов, ваш запрос всегда будет возвращать одну и ту же запись.
Часто программисты возражают: «Но ведь раньше всё работало правильно!». Разберем, почему ошибка проявилась не сразу. Пока база данных небольшая, SQL Server обычно использует Index Scan (полное сканирование индекса) по кластерному индексу. Физический порядок записей в кластерном индексе совпадает с порядком их добавления. Пока данных мало, запрос выполняется в один поток и всегда читает страницы по порядку.
Как только база вырастает, данные начинают распределяться по страницам иначе, обновляется статистика распределения, и оптимизатор принимает решение использовать другой индекс или параллельный план. Именно в этот момент «скрытая» ошибка в коде (отсутствие сортировки) превращается в реальную проблему с неверными данными в отчетах или документах.
ВЫБРАТЬ ПЕРВЫЕ без УПОРЯДОЧИТЬ ПО.СрезПоследних или СрезПервых.