Лучшее соединение враг хорошего?

Краткое содержание предыдущих серий о индексах 1С на примере регистра сведений, было показано

1. Для сохранения производительности больших баз, нужно использовать ограничения как в партицированных таблицах, даже если вы не используете партицированные таблицы Партицированная дисциплина программиста 1С. Join будет выполнятся лучше, если Вы примените такую практику

2. Селективность индекса для современного оптимизатора запросов, не самый важный критерий в выборе эффективного плана  Селективный индекс от 1С — что выберет MS SQL? . Подложить MS SQL правильный индекс становится сложнее.

3. Отсутствие поля общий реквизит- разделитель во временных таблицах  (как архитектурный pattern 1С) , лишает возможности сделать «классический» Merge join Общие реквизиты разделители против временных таблиц . Строго говоря Merge join делается, с автоподставляемым условием на равенство _Fld628=Х , но как будет показано ниже этот хороший с виду план запроса будет не самым оптимальным.

Проектирование решений в стиле ORM (Object-Relational Mapping ) позволяет дать стройное решение разработчику,  для взаимодействия с несколькими СУБД, но не позволяет сразу на бумаге разглядеть последствия для производительности. Насколько они существенные будет видно ниже.

Запрос ниже использует опыт предыдущих статей

Реальность в 1С

Исходный запрос по регистру сведений приведен тут

ВЫБРАТЬ РАЗЛИЧНЫЕ              СУУ_АгрегированныеДенежныеТранзакции.СвязаннаяОпИдИсхСистемы КАК СвязаннаяОпИдИсхСистемы  ПОМЕСТИТЬ Врем_ИдОперацийИзТранзакций  ИЗ              РегистрСведений.СУУ_АгрегированныеДенежныеТранзакции КАК СУУ_АгрегированныеДенежныеТранзакции  ГДЕ              СУУ_АгрегированныеДенежныеТранзакции.Период >= &ДатаНачала     ИНДЕКСИРОВАТЬ ПО              СвязаннаяОпИдИсхСистемы  ;     ////////////////////////////////////////////////////////////////////////////////  ВЫБРАТЬ              СУУ_АгрегированнаяСделкаКП.Период,              СУУ_АгрегированнаяСделкаКП.ИсходнаяСистема,              СУУ_АгрегированнаяСделкаКП.ИдИсхСистемы КАК ИдИсхСистемы,              СУУ_АгрегированнаяСделкаКП.ОсновнойСчет,              СУУ_АгрегированнаяСделкаКП.НогаСделки  ПОМЕСТИТЬ РезультатВыбранныеВерсииСделок  ИЗ              РегистрСведений.СУУ_АгрегированнаяСделкаКП КАК СУУ_АгрегированнаяСделкаКП                          ВНУТРЕННЕЕ СОЕДИНЕНИЕ Врем_ИдОперацийИзТранзакций КАК Врем_ИдОперацийИзТранзакций                          ПО СУУ_АгрегированнаяСделкаКП.ИдИсхСистемы = Врем_ИдОперацийИзТранзакций.СвязаннаяОпИдИсхСистемы  ГДЕ              СУУ_АгрегированнаяСделкаКП.Период >= ДОБАВИТЬКДАТЕ(&ДатаНачала, МЕСЯЦ, -3)     Стандартный план запроса который создает 1С  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)) AND ((T1._Period >= @P2))

Выглядит так

(фактическая статистика в StandardJoin.txt)

Фактический ввод вывод тут. Для индекса сделан Rebuild чтобы фрагментация не искажала картину

Ожидания от 1С

Если бы во временной таблице было поле разделителя _Fld628 запрос можно было бы переделать в такой вид (Это симуляция по мотивам генератора запросов 1С ) 

DECLARE @BeginPer datetime ;  SET @BeginPer = '4022-02-01 00:00:00';     DROP TABLE #tt_alternative;  DROP TABLE #tt_RESULT  --Альтернатива как можно было бы. Создаем  временную таблицу с разделителем  CREATE TABLE #tt_alternative (tt_Fld628 numeric(7,0), _Q_000_F_000 nvarchar(100));        --Делаем вставку во временную таблицу с разделителем  INSERT INTO #tt_alternative WITH(TABLOCK) (tt_Fld628[SS1] ,_Q_000_F_000) SELECT DISTINCT T1._Fld628,  T1._Fld18936     FROM dbo._InfoRg18800 T1 WITH(NOLOCK)  WHERE ((T1._Fld628 = 0)) AND ((T1._Period >= @BeginPer));     --Создаем УНИКАЛЬНЫЙ индекс по временной таблице включая разделитель   CREATE UNIQUE [SS2] INDEX tt_alternative_ind  ON  #tt_alternative (tt_Fld628 ASC,_Q_000_F_000 ASC);        --делаем Join   SELECT   T1._Period,  T1._Fld18861RRef,  T1._Fld18865,  T1._Fld18863RRef,  T1._Fld19363RRef   INTO #tt_RESULT  FROM dbo._InfoRg18860 T1 WITH(NOLOCK)  INNER JOIN #tt_alternative T2 WITH(NOLOCK)  ON  (T1._Fld628 =T2.tt_Fld628 ) [SS3] AND (T1._Fld18865 = T2._Q_000_F_000)    WHERE T1._Period>=@BeginPer;

 Теперь план содержит Index scan, по условию , но он быстрее Seek

|—Index Scan(OBJECT:([MIS_PROD2].[dbo].[_InfoRg18860].[_InfoR18860_ByDims18897_STRRRR] AS [T1]),  WHERE:([MIS_PROD2].[dbo].[_InfoRg18860].[_Period] as [T1].[_Period]>=[@BeginPer]) ORDERED FORWARD)

Merge тоже идет быстрее это видно из 2_2_SimulatedJoinFld628andNumber.txt

Фактическая статистика показывает что за счет CPU

Кроме того создание уникального индекса по временной таблице «CREATE UNIQUE INDEX  tt_alternative_ind  ON  #tt_alternative» позволяет вместо соединения Many-to-Many сделать более эффективное.

Внимательный читатель спросит – А если к соединению по двум полям добавить еще условие _Fld628=0? Хуже не будет

Архитектор ORM   как враг лучшего решения?

Какой вывод можно сделать из этой истории?  Очень простой – проектирование решений с верхнего уровня в стиле ORM (Object-Relational Mapping ) , инкапсулируют и переводят проблемы производительности на нижний уровень. Конечно это как правило  поправимо фирмой 1С, но не исключено, что под капотом придется создавать ветки для разных СУБД.

Например в текущей платформе, невозможно создать уникальный индекс на временную таблицу. Это конечно плюс для стабильности – не будет ошибок при создании индекса если результат запроса дает неуникальные записи для индекса. Но это минус для производительности при соединениях

В любом случае при работе с большими объемами, глубокое знание своей СУБД жизненно необходимо, а продуктов искуcственного интеллекта в СУБД пока не видно. Подписывайтесь на новые серии на нашем телеграмм канале.