Партицированная дисциплина программиста в 1С

20.09.22

База данных - HighLoad оптимизация

Почему при росте объемов базы 1С все становится медленней, даже если все индексы правильно сделаны? В статье на простом примере с регистром сведений показана причина и как этого избежать. Кто виноват больше, 1С или MS SQL решать Вам :)

Скачать файл

ВНИМАНИЕ: Файлы из Базы знаний - это исходный код разработки. Это примеры решения задач, шаблоны, заготовки, "строительные материалы" для учетной системы. Файлы ориентированы на специалистов 1С, которые могут разобраться в коде и оптимизировать программу для запуска в базе данных. Гарантии работоспособности нет. Возврата нет. Технической поддержки нет.

Наименование По подписке [?]
Статистика факт - 1С Join много записей
.txt 10,48Kb
6
6 Скачать бесплатно
Статистика - 1С Join мало записей
.txt 10,48Kb
5
5 Скачать бесплатно
Статистика - 1С Join с фильтром по границе (дата)
.txt 10,79Kb
5
5 Скачать бесплатно
Статистика - условие IN вместо JOIN
.txt 11,89Kb
6
6 Скачать бесплатно

Партицированная дисциплина программиста в 1С

Как известно, возможности партицирования при использовании 1С ограничены по ряду причин:

  1. Этому мешают общие реквизиты \ разделители, которые добавляются как первое поле в каждый индекс (справедливо для типовых конфигураций.)
  2. Ряд метаданных не поддается партицированию (Например, Регистры бухгалтерии, Документы с табличными частями) поскольку они состоят из нескольких таблиц, в которых нельзя выделить одинаковое по смыслу поле для партицирования (partitioned column)

Подробнее все описано тут Методика похудения для 1С - 100%

Но можно жить и без партицирования, если правильно ставить условия в запросах и «попадать в индекс». Однако просто «попадания в индекс» недостаточно.  Причем такие эффекты проявляются именно при росте базы 1С.

При работе с большими объемами в 1С храните версии.

Для примера возьмем сделки и трансферы, которые хранятся в двух регистрах сведений

Трансферы со ссылкой на сделку

 

 

И сделки

 

 

Почему в регистрах сведений, а не документах? Для того чтобы повысить скорость импорта операций, используя запись версий операций, пакетами по 1000 штук. Версия хранится в поле Период как дата\время загрузки. Подробнее можно прочитать тут Язык мой враг мой. В этом случае можно использовать максимальную возможную для платформы 1С скорость импорта, поскольку нет обновлений с фильтром  на равенство для измерений , а идет только запись новых наборов записей. В качестве минуса – в запросах на каком-то этапе приходится вычислять последние версии операций.

Наша задача:

  1. Отобрать трансферы по нужным критериям, сохранить СвязаннаяОпИдИсхСистемы во временную таблицу
  2. Проиндексировать  Врем_ИдОперацийИзТранзакций.
  3. Соединить Врем_ИдОперацийИзТранзакций с СУУ_Агрегированная_СделкаКП по Ид сделки

Типичная задача соединения трансферов со сделками.

Вопрос: Почему приходится соединять через временную таблицу?

Просто при версионной структуре хранения, напрямую трансферы и сделки соединить не получится – ведь у них разные значения версий. Соединять срезы последних неэффективно (это тема отдельной статьи). Но даже простое соединение по Ид не так просто,когда записей сотни миллионов.

Таблицы большие

  • СУУ_АгрегированнаяСделкаКП  - 210 миллионов записей
  • СУУ_АгрегированныеДенежныеТранзакции – 474 миллиона записей
  • В качестве MS SQL используется версия 2019 (15.0.2000.5)

Итак, базовый запрос, ничего сложного

Отбираем ограниченный набор Ид, а потом соединяем с большой таблицей сделок

ВЫБРАТЬ РАЗЛИЧНЫЕ

                СУУ_АгрегированныеДенежныеТранзакции.СвязаннаяОпИдИсхСистемы КАК СвязаннаяОпИдИсхСистемы

ПОМЕСТИТЬ Врем_ИдОперацийИзТранзакций

ИЗ

                РегистрСведений.СУУ_АгрегированныеДенежныеТранзакции КАК СУУ_АгрегированныеДенежныеТранзакции

ГДЕ

                СУУ_АгрегированныеДенежныеТранзакции.Период >= &ДатаНачала



ИНДЕКСИРОВАТЬ ПО

                СвязаннаяОпИдИсхСистемы

;



////////////////////////////////////////////////////////////////////////////////

ВЫБРАТЬ

                СУУ_АгрегированнаяСделкаКП.Период,

                СУУ_АгрегированнаяСделкаКП.ИсходнаяСистема,

                СУУ_АгрегированнаяСделкаКП.ИдИсхСистемы КАК ИдИсхСистемы,

                СУУ_АгрегированнаяСделкаКП.ОсновнойСчет,

                СУУ_АгрегированнаяСделкаКП.НогаСделки

ПОМЕСТИТЬ РезультатВыбранныеВерсииСделок

ИЗ

                РегистрСведений.СУУ_АгрегированнаяСделкаКП КАК СУУ_АгрегированнаяСделкаКП

                               ВНУТРЕННЕЕ СОЕДИНЕНИЕ Врем_ИдОперацийИзТранзакций КАК Врем_ИдОперацийИзТранзакций

                               ПО СУУ_АгрегированнаяСделкаКП.ИдИсхСистемы = Врем_ИдОперацийИзТранзакций.СвязаннаяОпИдИсхСистемы

;

 

И каждый раз сбрасываем в SQL Server все что можно

DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR); 

DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR); 

DBCC FREEPROCCACHE ;

DBCC DROPCLEANBUFFERS;

 

Все индексировано, а счастья нет

Последний  SQL запрос выглядит так

INSERT INTO #tt2 WITH(TABLOCK) (_Q_001_F_000, _Q_001_F_001RRef, _Q_001_F_002, _Q_001_F_003RRef, _Q_001_F_004RRef) SELECT

T1._Period,

T1._Fld18861RRef,

T1._Fld18865,

T1._Fld18863RRef,

T1._Fld19363RRef

FROM dbo._InfoRg18860 T1 WITH(NOLOCK) –Таблица сделок

INNER JOIN #tt1 T2 WITH(NOLOCK) –Временная таблица с Ид сделками

ON (T1._Fld18865 = T2._Q_000_F_000) –Условие соединения по номеру

WHERE (T1._Fld628 = @P1) –Общий реквизит разделитель ОбластьДанныхОсновныеДанные

Если его исполнить на небольшом количестве записей 100-200 тыс, то SQL картина будет несколько искаженная: SQL Server использует Nested loop, и не пытается использовать более правильный Merge Join – видимо, все соединение можно сделать в памяти

 

 

А нужный индекс для Merge join существует _InfoR18860_ByDims18897_STRRRR (_Fld18865 -ИдИсхСистемы)

 

 

и как покажет следующая итерация ниже – используется

Увеличим период выборки по трансферам, получим больше Ид во временную таблицу (20 миллионов – типичный наш объем за месяц )

Врем_ИдОперацийИзТранзакций посмотрим, как поменялось соединение

 

 

Index seek стал выполнятся с выдачей в поток почти всех данных (ранее был цикл обхода), а Nested loop  в Merge join который принимает на вход потоки отсортированных данных по таблицам #tt1 и _InfoR18860.  Вроде все корректно?

Подробнее можно увидеть тут

 

 

Данные отсортированы строго по индексам но проблема в том, что оптимизатор SQL Server выбирает  Index Seek по _InfoR18860_ByDims18897_STRRRR и дает  на вход Merge Join 210 649 142 строк т.е. весь индекс , хотя он соединяется с таблицей #tt1, в которой всего 20 миллионов отсортированных значений  и по которой он делает

|--Merge Join(Inner Join, MANY-TO-MANY MERGE:([T2].[_Q_000_F_000])=([T1].[_Fld18865]), RESIDUAL:(#tt1.[_Q_000_F_000] as [T2].[_Q_000_F_000]=[MIS_PROD2].[dbo].[_InfoRg18860].[_Fld18865] as [T1].[_Fld18865]))



|--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#tt1] AS [T2]), ORDERED FORWARD)

|--Index Seek(OBJECT:([MIS_PROD2].[dbo].[_InfoRg18860].[_InfoR18860_ByDims18897_STRRRR] AS [T1]), SEEK:([T1].[_Fld628]=[@P1]) ORDERED FORWARD) 

Здесь, конечно, есть Seek [T1].[_Fld628]=[@P1] по общему реквизиту разделителю  (ОбластьДанныхОсновныеДанные), но это неизбежность при использовании типовой конфигурации или БСП

Анонс: Влияние общих разделителей на запросы

Обратите внимание как поведет себя MS SQL, если еще увеличить количество Ид в #tt1

Вместо Index Seek по _InfoR18860_ByDims18897_STRRRR может  превратится в Index Scan  в зависимости от количества записей в #tt1

 

Проблема

1) получается печальная картина, чем больше таблица,  тем медленней будет простой запрос на соединение с фиксированным количеством записей (в нашем случае 20 миллионов)

2) идеологически правильный план MS SQL берет на больших данных, а на объемах поменьше может выбрать Nested loop, который по факту может быть не таким эффективным.

 

Правильно сформулированный запрос, это половина ответа

В теории MS SQL , который принимает отсортированные потоки для Merge join мог бы  посмотреть на значение первого и последнего элемента в #tt1 и  сделать более умный Index seek который возвратил бы меньшее количество записей. Но если он это он это делать не умеет – придется помочь. В нашем примере два варианта

  1. Наложить условие на поле ИдИсхСистемы, ограничив его минимальным из #tt1 значением. Оно _Fld18865 тут строковое,  поэтому могут быть нюансы производительности в зависимости от типа данных
  2. Наложить условие на поле Period (Период) – поскольку там содержатся даты\время загрузки версий (примечание в поле Дата – дата операции ). Можно наложить условие на заведомо закрытый период

Попробуем условие на Period тем более, что оно следующее за _Fld18865 в индексе _InfoR18860_ByDims18897_STRRRR

В последний запрос будет добавлено условие

ГДЕ

СУУ_АгрегированнаяСделкаКП.Период >= ДОБАВИТЬКДАТЕ(&ДатаНачала, МЕСЯЦ, -3)

 

Смотрим новый план

 

 

Картина становится гораздо приятнее теперь Index Seek  дает всего  77 миллионов записей записей

Условие Index Seek тоже поменялось, раньше оно выдавало все а теперь только то, что мы ограничили снизу

|--Index Seek(OBJECT:([MIS_PROD2].[dbo].[_InfoRg18860].[_InfoR18860_ByDims18897_STRRRR] AS [T1]), SEEK:([T1].[_Fld628]=[@P1]),  WHERE:([MIS_PROD2].[dbo].[_InfoRg18860].[_Period] as [T1].[_Period]>=[@P2]) ORDERED FORWARD)

Если сравним фактическую стоимость запроса, увидим что Estimate IO и CPU уменьшились именно на merge join. На минуты\секунды можно не смотреть, поскольку тест был на виртуальной  среде с совместным использованием ресурсов сервера

 

 

Общая стоимость в «попугаях» снизилась с 11424 до 7801

Вывод -  

нам всегда  в MS SQL нужно указывать явно границу для Index Seek.

Партицируем непартицируемое

На что это похоже? Кто работал с партицированием, увидит partitioning column

Partitioning column

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

подробно этот эффект описан тут Работа с партициями в MS SQL

Как замечено выше, для типовых конфигураций партицирование применять затруднительно,но для того, чтобы работать с большими объемами, для каждого запроса придется выбирать свой аналог «partitioning column», чтобы ограничить стремление MS SQL сделать неэффективный Index seek. Получается, на больших базах нельзя жить без партиций,  даже если их нет. 1С по сути всего лишь очень удобный генератор запросов к СУБД и если нужно делать это эффективно, без знания работы СУБД не обойтись. Все что изложено – применимо и к другим метаданным, поскольку соединения MS SQL делает по одинаковому принципу.

 

Лучший план запроса враг хорошего?

Можно ли еще улучшить план? Это не так просто, поскольку тут уже нужно смотреть глубже на работу оптимизатора SQL и подбирать нестандартные индексы,а это уже отдельная статья. Один простой вариант – отказаться от Join и применить условие IN.

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

ВЫБРАТЬ

                СУУ_АгрегированнаяСделкаКП.Период,

                СУУ_АгрегированнаяСделкаКП.ИсходнаяСистема,

                СУУ_АгрегированнаяСделкаКП.ИдИсхСистемы КАК ИдИсхСистемы,

                СУУ_АгрегированнаяСделкаКП.ОсновнойСчет,

                СУУ_АгрегированнаяСделкаКП.НогаСделки

ПОМЕСТИТЬ РезультатВыбранныеВерсииСделок



ИЗ

                РегистрСведений.СУУ_АгрегированнаяСделкаКП КАК СУУ_АгрегированнаяСделкаКП

               

ГДЕ

                СУУ_АгрегированнаяСделкаКП.Период >= ДОБАВИТЬКДАТЕ(&ДатаНачала, МЕСЯЦ, -3) И СУУ_АгрегированнаяСделкаКП.ИдИсхСистемы В (ВЫБРАТЬ СвязаннаяОпИдИсхСистемы ИЗ Врем_ИдОперацийИзТранзакций)

 

Как ни странно, в MS SQL оно работает лучше , хотя план почти похож (исчез cost на IO на уровне Merge). Кстати, стоимость стала 4666 

 

 

Но цена ниже

 

 

Т.е. с ростом объемов, программировать на 1С без углубления знаний в СУБД невозможно – ведь 1С это всего лишь генератор запросов (ORM Object-Relational Mapping подобный) со всеми его минусами. Буду рад видеть Вас на нашем канале  😊

SQL Index seek Оптимизация запросов

См. также

Инструментарий разработчика Роли и права Запросы СКД Программист Платформа 1С v8.3 Управляемые формы Запросы Система компоновки данных Конфигурации 1cv8 Платные (руб)

Набор инструментов программиста и специалиста 1С для всех конфигураций на управляемых формах. В состав входят инструменты: Консоль запросов, Консоль СКД, Консоль кода, Редактор объекта, Анализ прав доступа, Метаданные, Поиск ссылок, Сравнение объектов, Все функции, Подписки на события и др. Редактор запросов и кода с раскраской и контекстной подсказкой. Доработанный конструктор запросов тонкого клиента. Продукт хорошо оптимизирован и обладает самым широким функционалом среди всех инструментов, представленных на рынке.

10000 руб.

02.09.2020    152493    836    397    

846

Механизмы платформы 1С Программист Стажер Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

Эта небольшая статья - некоторого рода шпаргалка по файловым потокам: как и зачем с ними работать, какие преимущества это дает.

23.06.2024    6114    bayselonarrend    19    

153

WEB-интеграция Универсальные функции Механизмы платформы 1С Программист Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

При работе с интеграциями рано или поздно придется столкнуться с получением JSON файлов. И, конечно же, жизнь заставит проверять файлы перед тем, как записывать данные в БД.

28.08.2023    13575    YA_418728146    7    

165

Запросы Инструментарий разработчика Программист Бесплатно (free)

Список всех популярных обработок.

17.03.2023    54271    kuzyara    88    

190

Механизмы платформы 1С Программист Платформа 1С v8.3 Бесплатно (free)

Давайте разберемся в механизме «История данных» и поэкспериментируем для наглядности. Сравним «Версионирование объектов» и «Историю данных».

06.03.2023    28737    dsdred    69    

204

Механизмы платформы 1С Программист Платформа 1С v8.3 Россия Бесплатно (free)

При знакомстве с новой механикой работы с асинхронностью (обещание, ждать и асинх) делал пометки, которыми и хочу поделиться. Ничего сверхъестественного в них нет, просто небольшие примеры и некоторые всплывшие нюансы использования.

29.07.2022    60725    zeltyr    25    

208

Механизмы платформы 1С Системный администратор Программист Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

Копнем глубже в тему "Что же такое динамическое обновление" и почему оно может привести к проблемам. И может ли?

09.05.2022    32648    Infostart    84    

245

HighLoad оптимизация Технологический журнал Системный администратор Программист Платформа 1С v8.3 Бесплатно (free)

Рассмотрим по шагам процесс обнаружения, анализа и решения проблемы производительности на примере базы ERP, сравним отличия в работе Postgres и MS SQL.

28.02.2022    18643    ivanov660    18    

157
Комментарии
Подписаться на ответы Инфостарт бот Сортировка: Древо развёрнутое
Свернуть все
1. SerVer1C 794 20.09.22 17:00 Сейчас в теме
Ждём-с от 1С механизма гибкого управления индексами из конфигуратора (в т.ч. индексами с включенными столбцами)...
2. 1CUnlimited 320 20.09.22 17:06 Сейчас в теме
(1) А как это поможет в этом примере? Какого нестандартного индекса не хватает? В целом достаточно средств СУБД чтобы создавать гибкие индексы со спецификой конкретной субд (а их сейчас как минимум 4). Чего 1С не хватает - возможности hint устанавливать для плана исполнения, сейчас планом исполнения вообще невозможно управлять напрямую
3. paulwist 10.11.23 16:06 Сейчас в теме
Если его исполнить на небольшом количестве записей 100-200 тыс, то SQL картина будет несколько искаженная: SQL Server использует Nested loop, все соединение можно сделать в памяти


Хм, правильное "наблюдение" привело к неправильному выводу

и не пытается использовать более правильный Merge Join


Оптимизатор оценивает "стоимость" запроса, как вы правильно сказали в "попугаях".

Одной из самых важных величин (главным попугаем) влияющих на стоимость является число строк. Начиная с определенного кол-ва строк, становится более выгодным использовать Nested Loops (NL) вместо Merge Join (MJ).

Когда вы ему вместо 100-200 тыс. подали в "верхнем" наборе 20млн, то стоимость просто увеличилась.

Для запроса
СУУ_АгрегированнаяСделкаКП.Период >= ДОБАВИТЬКДАТЕ(&ДатаНачала, МЕСЯЦ, -3) И СУУ_АгрегированнаяСделкаКП.ИдИсхСистемы В (ВЫБРАТЬ СвязаннаяОпИдИсхСистемы ИЗ Врем_ИдОперацийИзТранзакций)


Верните во временную табличку 100 тыс. записей, скорее всего опять получите NL.

PS статья из разряда "что вижу, то пою" с неверными выводами.
4. 1CUnlimited 320 14.11.23 17:38 Сейчас в теме
(3)
Оптимизатор оценивает "стоимость" запроса, как вы правильно сказали в "попугаях".

Одной из самых важных величин (главным попугаем) влияющих на стоимость является число строк. Начиная с определенного кол-ва строк, становится более выгодным использовать Nested Loops (NL) вместо Merge Join (MJ).

Вы специально убрали при цитировании "Видимо все соединение можно сделать в памяти" т.е. это было предположение а не утверждение, поскольку открытый код оптимизатора MS SQL никто из нас не смотрел.
По поводу расчета стоимости оптимизатором неясно окуда уверенность что MS SQL ее считает верно, тем более что есть Estimated и Fact cost. A nested loop на более чем 100 тыс записей так себе алгоритм,который сядет на одно ядро тем более что параллелизм по рекомендациям 1С выключен
Тут скорее не выводы а констатация факта
5. paulwist 15.11.23 10:29 Сейчас в теме
(4)
Вы специально убрали при цитировании "Видимо все соединение можно сделать в памяти" т.е. это было предположение а не утверждение


Эээ, действительно слово "видимо" потерялось "по дороге", согласен, специально не убирал.

(4)
По поводу расчета стоимости оптимизатором неясно окуда уверенность что MS SQL ее считает верно


Ну так, посмотрите внимательно на планы, на картинках приведены Actual Excecution Plan, то есть запросы физически выполнились и представленные планы - это фактические затраты "попугаев".

Более того, приведенные примеры картинок говорят, что запросы выполнялись на актуальной статистике.

(4)
тем более что есть Estimated и Fact cost.


Ммм, поясните, что такое Fact cost, желательно ссылкой на сайт производителя. :)

(4)
A nested loop на более чем 100 тыс записей так себе алгоритм,который сядет на одно ядро тем более что параллелизм по рекомендациям 1С выключен


Вполне нормальный алгоритм, пожалуйста на 100 тыс NL

-- похоже, разделением ключевых слов (Create/drop/sel ect) движок сайта борется с sql-injection :) 
cre ate     table #test (id int not null primary key, f1 varchar(200));
cre ate     index  idx1 on #test (f1);
go
 
ins ert into #test (id, f1)
sel ect top 100000 ROW_NUMBER() over(order by 1/0) , 
cast(ROW_NUMBER() over(order by 1/0)  as varchar(200))
fr om master.dbo.spt_values a
cross join master.dbo.spt_values b
go 
 
set statistics xml on
go
select * fr om #test t
left join #test t1 on t1.f1 = t.f1 
and t1.f1 = '1'
go
set statistics xml off
go

dr op       table #test;
Показать


(4)
Тут скорее не выводы а констатация факта


Ну если фраза

и не пытается использовать более правильный Merge Join


является констатацией факта :), то пусть будет так :), на мой взгляд - это полное непонимание "стоимостного" выбора плана.
Оставьте свое сообщение