Краткое содержание предыдущих серий о индексах 1С на примере регистра сведений, было показано
Для сохранения производительности больших баз, нужно использовать ограничения как в партицированных таблицах, даже если вы не используете партицированные таблицы Партицированная дисциплина программиста 1С. Join будет выполнятся лучше, если Вы примените такую практику
Селективность индекса для современного оптимизатора запросов, не самый важный критерий в выборе эффективного плана Селективный индекс от 1С — что выберет MS SQL? . Подложить MS SQL правильный индекс становится сложнее.
Отсутствие поля общий реквизит- разделитель во временных таблицах (как архитектурный 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ственного интеллекта в СУБД пока не видно. Подписывайтесь на новые серии на нашем телеграмм канале. t.me/Chat1CUnlimited
Комментарии (13)
hbn3
22.10.2022 06:14Поддерживается ли в 1С создание memory_optimized временных таблиц?
И интересно, а старый добрый способ, ввалить побольше ресурсов в железо насколько эффективно решает проблемы.
Для средних размеров базы, апгрейд памяти и дисковой подсистемы в размере месячной зарплаты средненького программиста может сотворить чудеса.
Добавить несколько SSD райдов и на каждый сделать отдельную файловую группу.
Разбить тяжёлые таблицы и индексы по разным группам.
Попробовать патицирование сделать.
Добить памяти, чтобы хорошая часть данных была закэшированна.
Понятно что правильная оптимизация запросов может на порядки ускорить производительность. А железо в лучшем случае в разы.
Но всё равно было бы интересно посмотреть на сравнение производительности при улучшении характеристик и оптимизации системы IO.Это конечно плюс для стабильности – не будет ошибок при создании индекса если результат запроса дает неуникальные записи для индекса.
не очень понятно а в чём проблема создать неуникальный индекс? Он же точно такой же прирост по производительности даст.
1CUnlimited Автор
22.10.2022 11:29memory_optimized в sql имеют кучу ограничений, решают только проблемы чтения а не записи и еще коцепции разные в разных субд. 1с поддерживает 4 субд и им это важно.
Железом описанную в статье проблему не решишь. Там видно что поток для merge при соединении с двумя полями содержит меньше записей, чем с номером+ fld628=0. Там именно процессорное время уменьшается . В этом случае все будет ограничено одним ядром процессора
Железо помогает если конфигурация сделана с учетом горизонтального маштабирования как делали мы https://habr.com/ru/post/674282/
edo1h
24.10.2022 16:32Добавить несколько SSD райдов и на каждый сделать отдельную файловую группу.
а вы часто утыкаетесь в производительность отдельного ssd?
hbn3
24.10.2022 16:47При обычной нагрузе никогда.
Если база тяжёлая и много одновременно работающих пользователей, то вполне возможно.
Плюс достаточно обычная ситуация когда база хостится на SAN и для временных таблиц используется диски на тех же SAN, что может быть вполне затратно.
Поставь локальный RAID1 на SSD 4TB, назначь его для фаловой группы для временных таблиц. Может сильно веселее стать. Цена вопроса в районе $2K если не брать супер варианты.
С оперативной памятью конечно интереснее, если вы гоняете ms sql standard edition, то там лимиты довольно смешные на память и процессоры и было бы глупо не использовать их по максимуму.
Ну и так в принципе, добить до максимума который ваш сервер поддерживает обычно не сильно большие деньги.
1CUnlimited Автор
22.10.2022 11:35"не очень понятно а в чём проблема создать неуникальный индекс?" Many to many более сложно делать чем однозначное по уникальному индексу
katamoto
22.10.2022 18:15+1У вас не совсем корректное сравнение запросов. В первом случае (реальность) выполняется параметризированный запрос, во втором (ожидание) - с использованием локальных переменных, что ведёт к другому способу расчёта ожидаемого количества строк и, потенциально, может в итоге привести к другому плану. Прогоните второй вариант с OPTION (RECOMPILE), возможно вы увидите другие цифры в итоге.
1CUnlimited Автор
22.10.2022 18:16Я перед каждым запросом делаю сброс всего instance sql. Так что каждый запрос идет с чистого листа
DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);
DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);
DBCC FREEPROCCACHE ;
DBCC DROPCLEANBUFFERS
katamoto
22.10.2022 18:36В данном случае это ни на что не влияет, т.к. тут дело не кэшировании. В первом случае компилятор видит передаваемый в запрос параметр (при включенном по умолчанию parameter sniffing) и будет в расчётах использовать гистограмму из статистики, во втором случае - нет и будет использоваться общая плотность, что на неравномерно распределённых данных может дать сильно другое количество ожидаемых строк
1CUnlimited Автор
24.10.2022 15:52Можете какой то пруф линк привести? Я вот впервые слышу что при разном способе передачи параметров будет разный способ использования статистики. При повторной передаче параметров как тут Почему Parameter Sniffing не всегда плохо (хотя обычно так и есть) | SQL-Ex blog да такое есть.
В любом случае запустил запрос еще раз с recompile разницы нет
Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions ---- -------- -------- ------ ------ ------ ---------- --------- -------- ------------- ------------ ---------- ----------- ---------- ---------------- ---------- -------- ---- -------- ------------------ 18376071 1 Table Insert(OBJECT:([#tt_RESULT]), SET:([#tt_RESULT].[_Period] = [MIS_PROD2].[dbo].[_InfoRg18860].[_Period] as [T1].[_Period],[#tt_RESULT].[_Fld18861RRef] = [MIS_PROD2].[dbo].[_InfoRg18860].[_Fld18861RRef] as [T1].[_Fld18861RRef],[#tt_RESULT].[_Fld18865] = [MIS_PROD2].[dbo].[_InfoRg18860].[_Fld18865] as [T1].[_Fld18865],[#tt_RESULT].[_Fld18863RRef] = [MIS_PROD2].[dbo].[_InfoRg18860].[_Fld18863RRef] as [T1].[_Fld18863RRef],[#tt_RESULT].[_Fld19363RRef] = [MIS_PROD2].[dbo].[_InfoRg18860].[_Fld19363RRef] as [T1].[_Fld19363RRef])) 0 0 Table Insert Insert OBJECT:([#tt_RESULT]), SET:([#tt_RESULT].[_Period] = [MIS_PROD2].[dbo].[_InfoRg18860].[_Period] as [T1].[_Period],[#tt_RESULT].[_Fld18861RRef] = [MIS_PROD2].[dbo].[_InfoRg18860].[_Fld18861RRef] as [T1].[_Fld18861RRef],[#tt_RESULT].[_Fld18865] = [MIS_PROD2].[dbo].[_InfoRg18860].[_Fld18865] as [T1].[_Fld18865],[#tt_RESULT].[_Fld18863RRef] = [MIS_PROD2].[dbo].[_InfoRg18860].[_Fld18863RRef] as [T1].[_Fld18863RRef],[#tt_RESULT].[_Fld19363RRef] = [MIS_PROD2].[dbo].[_InfoRg18860].[_Fld19363RRef] as [T1].[_Fld19363RRef]) 2.87826E+006 201.792 2.87826 9 2752.21 PLAN_ROW 0 1 18376071 1 |--Top(ROWCOUNT est 0) 0 1 0 Top Top TOP EXPRESSION:((0)) 2.87826E+006 0 0.287826 85 2547.54 [T1].[_Period], [T1].[_Fld18861RRef], [T1].[_Fld18865], [T1].[_Fld18863RRef], [T1].[_Fld19363RRef] PLAN_ROW 0 1 18376071 1 |--Merge Join(Inner Join, MERGE:([T2].[tt_Fld628], [T2].[_Q_000_F_000])=([T1].[_Fld628], [T1].[_Fld18865]), RESIDUAL:([MIS_PROD2].[dbo].[_InfoRg18860].[_Fld628] as [T1].[_Fld628]=#tt_alternative.[tt_Fld628] as [T2].[tt_Fld628] AND [MIS_PROD2].[dbo].[_InfoRg18860].[_Fld18865] as [T1].[_Fld18865]=#tt_alternative.[_Q_000_F_000] as [T2].[_Q_000_F_000])) 0 2 1 Merge Join Inner Join MERGE:([T2].[tt_Fld628], [T2].[_Q_000_F_000])=([T1].[_Fld628], [T1].[_Fld18865]), RESIDUAL:([MIS_PROD2].[dbo].[_InfoRg18860].[_Fld628] as [T1].[_Fld628]=#tt_alternative.[tt_Fld628] as [T2].[tt_Fld628] AND [MIS_PROD2].[dbo].[_InfoRg18860].[_Fld18865] as [T1].[_Fld18865]=#tt_alternative.[_Q_000_F_000] as [T2].[_Q_000_F_000]) 2.87826E+006 0 90.3212 85 2547.25 [T1].[_Period], [T1].[_Fld18861RRef], [T1].[_Fld18865], [T1].[_Fld18863RRef], [T1].[_Fld19363RRef] PLAN_ROW 0 1 20079020 1 |--Index Scan(OBJECT:([tempdb].[dbo].[#tt_alternative] AS [T2]), ORDERED FORWARD) 0 3 2 Index Scan Index Scan OBJECT:([tempdb].[dbo].[#tt_alternative] AS [T2]), ORDERED FORWARD [T2].[tt_Fld628], [T2].[_Q_000_F_000] 2.0079E+007 75.5653 22.0871 34 97.6524 [T2].[tt_Fld628], [T2].[_Q_000_F_000] PLAN_ROW 0 1 22611008 1 |--Index Scan(OBJECT:([MIS_PROD2].[dbo].[_InfoRg18860].[_InfoR18860_ByDims18897_STRRRR] AS [T1]), WHERE:([MIS_PROD2].[dbo].[_InfoRg18860].[_Period] as [T1].[_Period]>='4022-02-01 00:00:00.000') ORDERED FORWARD) 0 4 2 Index Scan Index Scan OBJECT:([MIS_PROD2].[dbo].[_InfoRg18860].[_InfoR18860_ByDims18897_STRRRR] AS [T1]), WHERE:([MIS_PROD2].[dbo].[_InfoRg18860].[_Period] as [T1].[_Period]>='4022-02-01 00:00:00.000') ORDERED FORWARD [T1].[_Period], [T1].[_Fld18861RRef], [T1].[_Fld18865], [T1].[_Fld18863RRef], [T1].[_Fld19363RRef], [T1].[_Fld628] 2.27914E+007 2026.45 231.714 90 2258.16 [T1].[_Period], [T1].[_Fld18861RRef], [T1].[_Fld18865], [T1].[_Fld18863RRef], [T1].[_Fld19363RRef], [T1].[_Fld628] PLAN_ROW 0 1katamoto
24.10.2022 16:18Если посмотреть на estimated rows при выборке из _InfoRg18860, то видно, что разница всё же есть - 63194800 без recompile и 22791400 с recompile. В данном случае на план это не повлияло, но, потенциально, при других параметрах, вполне могло бы.
Точную ссылку где я про это читал сейчас по быстрому нагуглить не удалось, но например вот тут про это упоминается тоже - https://www.sqlservercentral.com/blogs/inside-the-statistics-histogram-density-vector
HADGEHOGs
24.10.2022 19:48Начну более менее адекватно воспринимать автора, когда он ответит в чем сакральный смысл лепить индекс на временную таблицу вместе ее сортировки по полям связи.
boopiz
ИМХО. Самое важное это не сиюминутный план запроса с самой низкой стоимостью, а стабильный и прогнозируемый. Конечно, приемлимый по цене. Но, для этого надо профайлить все. И иметь инструментарий корректировки итоговых запросов. Например, для указания использования nested loop вместо hash функций, которые на больших объёмах дают, порой, неконтролируемую просадку. и т. д.
1CUnlimited Автор
Я специально брал большие обьемы данных чтобы оптимизатор не пытался перейти на nested loop в соединениях или другие пути если они ему покажутся эффективными. Хинты 1с не поддерживает и в трассировках я их не видел, но там и без хинтов можно перестройкой запросов под капотом платформы добится хороших результатов. С поддержкой по теме данной статьи общаюсь - думаю возьмут в работу