При разработке механизмов проведения документов в платформе 1С:Предприятие разработчики часто сталкиваются с неочевидным поведением СУБД: подготовленная и строго отсортированная в коде таблица движений после записи в базу данных отображается или считывается в хаотичном порядке. Например, вместо ожидаемой последовательности строк по порядку 1, 2, 3, 4, 5 мы можем получить в результате выборки порядок вида 1, 2, 4, 5, 3.
В этой статье мы подробно разберем, почему так происходит, как на физический порядок хранения данных влияют кластерные индексы и особенности различных систем управления базами данных (MS SQL Server и PostgreSQL), а также как гарантировать правильный порядок строк в прикладных задачах.
Рассмотрим типичный пример кода при проведении документа. Разработчик выбирает данные из табличной части документа, упорядочивает их по номеру строки, загружает в коллекцию движений и записывает:
Запрос = Новый Запрос;
Запрос.Текст = "ВЫБРАТЬ
| ПрочиеРасходыРасходы.НомерСтроки КАК НомерСтроки,
| ПрочиеРасходыРасходы.Период КАК Период,
| ПрочиеРасходыРасходы.Контрагент КАК Контрагент,
| ПрочиеРасходыРасходы.СтатьяРасходов КАК СтатьяРасходов,
| ПрочиеРасходыРасходы.Сумма КАК Сумма
|ИЗ
| Документ.ПрочиеРасходы.Расходы КАК ПрочиеРасходыРасходы
|ГДЕ
| ПрочиеРасходыРасходы.Ссылка = &Ссылка
|
|УПОРЯДОЧИТЬ ПО
| НомерСтроки";
Запрос.УстановитьПараметр("Ссылка", Ссылка);
Таблица = Запрос.Выполнить().Выгрузить();
ДвиженияРегистра = Движения.ПрочиеРасходы;
ДвиженияРегистра.Загрузить(Таблица);
ДвиженияРегистра.Записать();
Если мы проверим таблицу значений Таблица в отладчике перед загрузкой, то увидим идеальный порядок по возрастанию колонки НомерСтроки. Однако после выполнения метода Записать() при просмотре движений в регистре или при их простом считывании порядок нарушается. Выясним, какие подводные камни скрывает в себе этот процесс.
В СУБД физический порядок хранения строк на диске определяется так называемым кластерным индексом. Для большинства регистров (накопления и сведений) в 1С:Предприятии кластерный индекс строится по следующему набору полей и строго в указанной последовательности:
Проанализируем, к чему это приводит на практике. Если в документе «Прочие расходы» разные строки имеют разный Период (например, расходы распределяются по разным датам или даже разным секундам внутри одного дня), то СУБД физически сгруппирует записи сначала по дате (полю Период), а уже затем внутри каждой даты отсортирует их по НомерСтроки.
Таким образом, если строка №3 имеет более поздний период, чем строка №4, то при физической записи на диск строка №4 окажется выше строки №3. Попытка записать их подряд по номерам строк физически невозможна из-за архитектуры хранения данных в СУБД.
Если в качестве СУБД используется PostgreSQL, ситуация становится еще более интересной. Архитектура PostgreSQL основана на концепции MVCC (многоверсионности), где данные по умолчанию хранятся в неупорядоченном виде в так называемой «куче» (heap).
Когда мы выполняем простой запрос к таблице без явной секции сортировки, PostgreSQL возвращает записи в том порядке, в котором они физически лежат в файле данных на диске. Этот порядок может меняться произвольным образом в результате следующих событий:
VACUUM.Важный вывод: СУБД PostgreSQL в принципе не гарантирует никакого порядка выдачи строк при обычном запросе, если в нем отсутствует инструкция ORDER BY. Даже если записи лежат на диске последовательно, планировщик СУБД может отдать их хаотично ради оптимизации скорости чтения.
При вызове метода Записать() для набора записей движений платформа выполняет транзакционные действия на уровне СУБД:
DELETE.INSERT. Начиная с версии платформы 8.3, для больших пакетов данных может использоваться механизм массовой вставки (Bulk Insert). В этом режиме СУБД сама решает, в каком порядке и в какие физические блоки данных поместить новые записи, чтобы минимизировать фрагментацию индексов.Если при загрузке таблицы значений в набор записей колонка НомерСтроки не заполнена или заполнена с дублями, платформа 1С автоматически пересчитает эти номера при записи, что также может сбить первоначальный порядок строк.
В профессиональной разработке на платформе 1С:Предприятие действует золотое правило: никогда не закладываться на физический порядок хранения строк в таблицах СУБД. Логика вашей программы не должна зависеть от того, «как легла запись».
Если для печатной формы, отчета или алгоритма проведения критически важно получить движения строго в определенном порядке (например, в порядке заполнения строк в документе), необходимо всегда использовать явную сортировку в запросе чтения.
Посмотрим на пример правильного запроса для получения движений регистра:
ЗапросЧтения = Новый Запрос;
ЗапросЧтения.Текст = "ВЫБРАТЬ
| ПрочиеРасходы.НомерСтроки КАК НомерСтроки,
| ПрочиеРасходы.Период КАК Период,
| ПрочиеРасходы.Контрагент КАК Контрагент,
| ПрочиеРасходы.СтатьяРасходов КАК СтатьяРасходов,
| ПрочиеРасходы.Сумма КАК Сумма
|ИЗ
| РегистрНакопления.ПрочиеРасходы КАК ПрочиеРасходы
|ГДЕ
| ПрочиеРасходы.Регистратор = &Регистратор
|
|УПОРЯДОЧИТЬ ПО
| НомерСтроки";
ЗапросЧтения.УстановитьПараметр("Регистратор", СсылкаНаДокумент);
Выборка = ЗапросЧтения.Выполнить().Выбрать();
Пока Выборка.Следующий() Цикл
// Обрабатываем движения в гарантированном порядке 1, 2, 3...
КонецЦикла;
Даже если в базе данных на диске строки лежат хаотично из-за разницы в периодах или особенностей оптимизатора PostgreSQL, оператор УПОРЯДОЧИТЬ ПО НомерСтроки заставит СУБД выполнить сортировку перед выдачей результата в 1С. Благодаря наличию индекса по полю НомерСтроки (в составе кластерного индекса), эта операция сортировки выполнится мгновенно и без лишней нагрузки на сервер.