Курс по Оптимизации 1С – Занятие №6
В рамках этого занятия необходимо изучить следующие темы Главы 8.
Модуль 3. Анализ причин медленной работы и оптимизация системы
- Глава 8. Причины медленной работы
- Обзор основных причин неоптимальной работы запроса
- Невыполнение регламентных операций СУБД
- Неоптимальные соединения таблиц
- Основные сведения о индексах. Когда индекс не может быть использован
Практическое задание
Скачивайте практические задания в формате PDF:
К сожалению, у Вас недостаточно прав для дальнейшего просмотра.
Если Вы приобрели курс, но еще не активировали токен — пожалуйста, активируйте доступ по инструкциям, высланным на Ваш email после покупки.
Если Вы не залогинены на сайте — залогиньтесь, вернитесь на эту страницу и обновите ее.
Если Вы залогинены, у Вас активирован токен доступа, но Вы все равно видите эту запись — напишите нам на e-mail поддержки.
Комментарии / обсуждение (229):
Комментарии закрыты
Андрей, для временной таблицы, как я понимаю, можно создать только один индекс. А если эта таблица используется потом несколько раз? Первый раз условия накладываются на один состав полей, второй раз на другой. Нужно создавать еще одну временную таблицу специально для создания нового индекса?
…
(текст комментария доступен только участникам Мастер-группы)
Андрей, скажи, пожалуйста, когда ставится “ИНДЕКСИРОВАТЬ” для временной таблицы
по нескольким полям, то создается один составной индекс или отдельные ?
…
(текст комментария доступен только участникам Мастер-группы)
Добрый день!
Андрей, можете пояснить: что такое PrimaryKey и в какой момент он образуется?
…
(текст комментария доступен только участникам Мастер-группы)
Здравствуйте. Вопрос по индексам.
Некластеризованные индексы в листьях содержат ссылки на данные. Какие данные имеются ввиду? Данные индекса или данные основной таблицы? Что представляет собой эта самая ссылка на данные, которая в листьях?
Раньше я думал, что ссылка ведет к данным основной таблицы, т.е. нашли индекс – перешли к данным и они все доступны. Но смутила тема про покрывающие индексы, где говорится, что если поля нет в индексе, то получаем lookup, т.е. поиск в других индексах. Где я заблуждаюсь?
…
(текст комментария доступен только участникам Мастер-группы)
Ссылка на данные это обычно адрес данных в примерно следующем формате: номер файла, номер секции, номер страницы, номер строки. Источник: ©Курсы-по-1С.рф
А что именно хранится по этому адресу? Данные индекса? Т.е. индекс – это тоже некая таблица с данными? Или данные основной таблицы?
…
(текст комментария доступен только участникам Мастер-группы)
Добрый день!
Хотел бы уточнить есть ли противопоказания к использованию примитивных типов в субконто
1)Если назначить субконто примитивный тип (не составной, например дата).
2)Если назначить примитивный тип составным с другим примитивным типом. (Чисто гипотетически допустим дата+ число)
…
(текст комментария доступен только участникам Мастер-группы)
Спасибо!
После того, как средствами MS SQL был создан составной индекс, как можно посмотреть все поля, которые в него входят (не только корневое поле, но и те, что были включены с помощью INCLUDE)?
…
(текст комментария доступен только участникам Мастер-группы)
Здравствуйте. После просмотра урока возникло несколько вопросов.
Допустим есть регистр сведений с измерениями Из1, Из2, Из3, Из4.
1) Правильно ли я понял, что составной индекс по нескольким полям (например Из2 + Из3) средствами платформы сделать невозможно?
2) Есть запрос, выбирающий данные по этому регистру. В секции ГДЕ стоят условия по Из2 и Из4. Кластерный индекс здесь невозможно использовать(Нет 1 измерения + зазор). Добавить условия по Из1 и Из3 не позволяет логика запроса. Менять измерения в конфигураторе приведёт к падению производительности в других запросах. Правильно ли я понимаю, что наиболее правильным вариантом будет отдельно проиндексировать Из2 и Из4, тогда будет использоваться не clustered index scan а index seek по Из2 и index seek по Из4.
…
(текст комментария доступен только участникам Мастер-группы)
Index Scan и Clustered Index Scan – какое из сканирований эффективнее? На мой взгляд – одинаково, что сканировать отсортированный индекс, что неотсортированный, или я ошибаюсь?
…
(текст комментария доступен только участникам Мастер-группы)
>Индекс в любом случае хранится отсортированным
Андрей, я как-то видел статью на хабре http://habrahabr.ru/post/247373/, где есть фраза “Некластеризованные индексы не могут быть отсортированы в отличие от кластеризованных”, и данная фраза немного ввела в заблуждение. Я больше верю Вам, чем автору статьи (хотя статья в целом, на мой взгляд, познавательная и неплохая). Вопрос: может порекомендуете статью или литературу, где в полной мере раскрывается информация об индексах (если есть данная информация в проекции на 1С, было бы вообще замечательно) и которую Вы считаете наиболее понятной и правдоподобной, дабы не мучить Вас всякими мелкими вопросами на эту тему?
…
(текст комментария доступен только участникам Мастер-группы)
Спасибо за рекомендации
Вопрос по уроку “В индексе не должно быть зазоров” (№33)
Не удается повторить Запрос и план запроса, показанные в уроке.
Используется платформа версии 8.3.5.1517 и тестовая база из приложения к курсу.
СУБД – MS SQL Server 2008r2
Имеем запрос 1С:
ВЫБРАТЬ
ОстаткиТоваровОстатки.Товар,
ОстаткиТоваровОстатки.КоличествоОстаток
ИЗ
РегистрНакопления.ОстаткиТоваров.Остатки(
,
Склад = &Склад
И Товар = &Товар) КАК ОстаткиТоваровОстатки
При выполнении запроса, в трассировке видим запрос SQL:
exec sp_executesql N’SELECT
T1.Fld20RRef,
T1.Fld21Balance_
FROM (SELECT
T2._Fld20RRef AS Fld20RRef,
CAST(SUM(T2._Fld21) AS NUMERIC(30, 8)) AS Fld21Balance_
FROM dbo._AccumRgT22 T2 WITH(NOLOCK)
WHERE T2._Period = @P1 AND (((T2._Fld19RRef = @P2) AND (T2._Fld20RRef = @P3))) AND (T2._Fld21 @P4) AND (T2._Fld21 @P5)
GROUP BY T2._Fld20RRef
HAVING (CAST(SUM(T2._Fld21) AS NUMERIC(30, 8))) @P6) T1′,N’@P1 datetime2(3),@P2 varbinary(16),@P3 varbinary(16),@P4 numeric(10),@P5 numeric(10),@P6 numeric(10)’,’5999-11-01 00:00:00′,0xA8AC080027C5281D11E2F953C9C69A70,0xA779080027C5281D11E2DF19AD3B74C0,0,0,0
Видно, что в подзапросе добавляются условия на неравенство поля _Fld21(Количество) параметрам P4 и P5, значения которых равны нулю. То же происходит и в условии группировки HAVING: вместо неравенства нулю непосредственно в тексте запроса, устанавливается неравенство параметру P6, который тоже устанавливается равным нулю.
Как итог, получается план запроса с SEEK… WHERE….
Clustered Index Seek(
OBJECT:([optimize_base].[dbo].[_AccumRgT22].[_AccumRgT22_ByDims_TRRR] AS [T2]),
SEEK:([T2].[_Period]=[@P1] AND [T2].[_Fld20RRef]=[@P3] AND [T2].[_Fld19RRef]=[@P2]),
WHERE:(
(
[optimize_base].[dbo].[_AccumRgT22].[_Fld21] as [T2].[_Fld21]CONVERT_IMPLICIT(numeric(16,0),[@P4],0)
) AND (
[optimize_base].[dbo].[_AccumRgT22].[_Fld21] as [T2].[_Fld21]CONVERT_IMPLICIT(numeric(16,0),[@P5],0)
)
) ORDERED FORWARD
)
Подскажите пожалуйста, где я делаю ошибку или в чем причина подобного поведения платформы.
Мое предположение, что это изменение в платформе для отсева нулевых записей из таблиц итогов регистра накопления.
…
(текст комментария доступен только участникам Мастер-группы)
Добрый день.
Андрей, можете пояснить, в каком случае при создании индекса ему присваивается свойство “уникальный”, а в каком – нет? (речь о некластерных индексах, с кластерным конечно всё понятно).
…
(текст комментария доступен только участникам Мастер-группы)
В остаточном регистре накопления установлен признак “разрешить разделение итогов”, до пересчется итогов в sql таблице итогов 0 записи хранились на 5999/11/01 (Файл. До пересчета) и их было 495 074, после пересчета итогов появились 4 записи 0, которые не удалились с периода 2015/03/01.. 1 две записи судя по полям Ref по одинаковым измерениям, то у одной ресурс = -50 и splitter =0, у другой ресурс =0 и splitter =1, и вторая запись похожая… В итоге после пересчета 0 записей стало 1318354, на каждый месяц после 2015/03/01, стало по 2 записи до 5999 года..Почему так?
…
(текст комментария доступен только участникам Мастер-группы)
почему образовались две нулевые записи с 2015/03/01, которые по идее должны были удалится при пересчете итогов?
…
(текст комментария доступен только участникам Мастер-группы)
пока к сожалению автоматический…
а как решить данную проблему?
…
(текст комментария доступен только участникам Мастер-группы)
…
(текст комментария доступен только участникам Мастер-группы)
Как тогда уменьшить количество строк в таблице итогов sql? После пересчета размер таблицы увеличислся в несколько раз, как и количество записей…
…
(текст комментария доступен только участникам Мастер-группы)
Получается даже самый безобидный запрос:
SELECT TOP 10
ПРЕДСТАВЛЕНИЕ(Фирма)
FROM
РегистрНакопления.ОстаткиТоваров.Остатки()
превращается в:
SELECT TOP 10
T1.Fld374RRef,
T3._Description
FROM
(SELECT
T2._Fld374RRef AS Fld374RRef,
CAST(SUM(T2._Fld378) AS NUMERIC(32, 8)) AS Fld378Balance_,
CAST(SUM(T2._Fld379) AS NUMERIC(30, 8)) AS Fld379Balance_
FROM dbo._AccumRgT382 T2
WHERE T2._Period = @P1 AND (T2._Fld378 @P2 OR T2._Fld379 @P3) AND (T2._Fld378 @P4 OR T2._Fld379 @P5)
GROUP BY T2._Fld374RRef
HAVING (CAST(SUM(T2._Fld378) AS NUMERIC(32, 8))) @P6 OR (CAST(SUM(T2._Fld379) AS NUMERIC(30, 8))) @P7) T1
LEFT OUTER JOIN dbo._Reference9 T3
ON T1.Fld374RRef = T3._IDRRef
Где есть объединение с подзапросом
+ OR в WHERE
+ в WHERE отбор по ресурсам , которые не входят в индекс :)
Как я понял, написать оптимально (с учетом всех рекомендаций) сложный запрос – очень объемно, это приводит к увеличению времени разработки(соотв. увеличению стоимости для клиента).
Можно ли выделить так скажем “основные” правила и сначала разработать запрос по “основным” правилам, а оптимизацией заниматься только если запрос станет работать медлено? Т.е. как бы вынести оптимизацию на потом, но сохраняя баланс между оптимизацией и скоростью разработки запроса.
…
(текст комментария доступен только участникам Мастер-группы)
Ок, я Вас услышал
план запроса к моему вопросу
…
(текст комментария доступен только участникам Мастер-группы)
Имеется запрос:
ВЫБРАТЬ
ЦеныНоменклатурыСрезПоследних.Номенклатура,
ЦеныНоменклатурыСрезПоследних.ТипЦен
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних(
,
Номенклатура В ИЕРАРХИИ (&Номенклатура)
И ТипЦен В ИЕРАРХИИ (&ТипыЦены)) КАК ЦеныНоменклатурыСрезПоследних
Измерения регистра: ТипЦен, Номенклатура, ХарактеристикаНоменклатуры.
план получается сложный с KEY LOOKUP в ведомой таблице с соединением с помощью оператора NESTED LOOPS. Ведущая таблица – индекс по полю номенклатура, ведомой таблицей является индекс того же регистра по периоду. В итоге имеется большое число выполнений, что сильно увеличивает стоимость запроса. Причем соединение используется, чтобы получить поля номенклатуры и характеристик. Почему бы не использовать для этого индекс по измерениям?
…
(текст комментария доступен только участникам Мастер-группы)
Доброго дня !
Андрей, возник вопрос по свойству “Индексировать с доп упорядочиванием”.
Имеет ли смысл его использовать когда в запросе (например из справочника) по этому полю выбирается не только ссылка, но и наименование. Резонно ли это, чтобы избежать KEY LOOKUP? Или лучше добавлять индекс с INCLUDE [Наименование] через sql?
Я это использую когда требуется выводить данные в печатную форму, стараюсь представление выбирать явно через наименование.
…
(текст комментария доступен только участникам Мастер-группы)
Вопрос по кластерному индексам периодического регистра сведений в 8.3. в документации написано что индекс состоит как [Хэш функция разделитель|независимый разделитель] + [Измерение1…] + Период.
Создал такой регистр сведений и смотрю состав индекса в SQL. Вижу все кроме [Хэш функция разделитель|независимый разделитель]. как его “увидеть” ?
…
(текст комментария доступен только участникам Мастер-группы)
Если поставить режим совместимости с 8.3.2, то появится вычисляемое поле DataSeparationHash и оно же будет в индексах. В V8Update.htm написано: “При отключении режима совместимости с версией 8.3.2 выполняется оптимизация структуры хранения информации о разделителях для таблиц, связанных с объектами, входящими в состав разделителей. При установке режима совместимости с версией 8.3.2 выполняется обратная реструктуризация таблиц базы данных.”
Доброй ночи,
немного непонятно, кластерный индекс, т.е. содержит сами данные в листьях, т.е. фактически саму таблицу, он существует как отдельная сущность физически храня копию таблицы или он как-то интегрируется в саму таблицу, упорядочивая ее?
У вас в уроках говорится, что лучше не использовать конструкцию или, т.к. будет выполнено сканирование индекса, хотя мне и не удалось воспроизвести это, но интересно, а как обстоят дела с операцией “В”, “В Иерархии”? Массивы для отбора тоже лучше через ВТ передавать?
…
(текст комментария доступен только участникам Мастер-группы)
благодарю
Добрый день!
Подскажите, по каким причинам предполагаемое количество строк в плане выполнения запроса может не совпадать с фактическим даже при полностью актуальной статистике?
Например, воспроизводя на своей базе пример по оператору seek … where (урок 50 (признаки неоптимального плана)) при обновленной статистике получаю предп. кол. строк 7361, фактическое 3779. Скорее всего это происходит из-за того, что нету статистики по колонкам Период + Характеристика + Количество. Я прав? Какие еще есть причины неверного предположения оптимизатора по количеству строк при актуальной статистике?
…
(текст комментария доступен только участникам Мастер-группы)
Нет, не внутри. Текст и план запроса прикрепляю.
…
(текст комментария доступен только участникам Мастер-группы)
А какие рекомендации в данном случае? Индексы создавать? или что еще можно сделать? Вот в один из подходов NL по плану должен был соединить по одной строке, а по факту получили NL с 4000 записями в каждой таблице. План не могу приложить. Не могу его воспроизвести после экспериментов с добавлением индексов в базу SQL и последующим их удалением. И тут вопрос 2: После удаления индекса и обновления статистики удаленный индекс уже никак не может влиять на планы запроса?
…
(текст комментария доступен только участникам Мастер-группы)
Соединения с виртуальными таблицами. Как так?
Мир перевернулся ))
Всю жизнь активно использую соединения с виртуальными таблицами.
Да и в типовых конфигурациях, особенно старых (УТ 10.3, УПП 1.2) таких запросов много.
А теперь оказывается, это не оптимально с точки зрения производительности.
Вопрос 1.
Всегда считал хорошим тоном взять справочник или табличную часть документа, прикрутить
к ней левым внешним соединение таблицу остатков или оборотов. Естественно наложив на виртуальную
таблицу все необходимые условия в параметрах да и на справочник тоже. А теперь значит, это были “по науке” не очень хорошие запросы?
Я правильно понимаю, что если делать “по науке”, то таких соединений быть не должно и все их заменяем на временные таблицы с индексацией? А с ними уже делаем нужные соединения?
Естественно эти запросы работали в основном не под высокой нагрузкой… И отрабатывало всегда все достаточно быстро.
Вопрос 2.
В примере урока вы показали достаточно сложный запрос с вашего проекта по оптимизации, где использовалось много соединений с виртуальными таблицами. Но в этом запросе и так использовалось несколько временных таблиц.
Если оптимизировать его “по науке”, то временных таблиц в запросе будет десятка два не меньше ))
Это нормально? С учетом накладных затрат на постоянное создание таблиц? Не будет здесь перебора?
…
(текст комментария доступен только участникам Мастер-группы)
Добрый день.
При просмотре уроков этой главы обратил внимание, что иногда время выполнения не оптимального запроса, показываемого в консоли меньше, чем более оптимального. К примеру вот в этом “3.8-2.40 Условия, не позволяющие использовать индекс. МЕСЯЦ” при использовании функции Месяц выполняется 0,019 сек (34 логических чтения), а при использовании условий 0,032 (2 логических чтения). Чем это объяснить и стоит ли вообще обращать внимание?
…
(текст комментария доступен только участникам Мастер-группы)
Здравствуйте.
Занятие № 5 уже закрыли, а у меня есть встречный вопрос на последний Ваш ответ, где Вы писали про Ordered Prefetch.
Почему когда выполняется сканирование таблицы без условий, предикатов нет (в нашем случае индекса) – возвращается только часть? например:
1. в моем случае в таблице “_Document556” всего строк 1845 – возвращается 217
2. в Вашем случае в таблице “#t2” всего строк 1000 – возвращается 41
…
(текст комментария доступен только участникам Мастер-группы)
Понятно, спасибо.
Я правильно понимаю следующее:
1. Ordered Prefetch это указатель на оптимизацию выполнения оператора Nested Loops
2. Когда выполняется оператор сканирования, даже в том случае, когда для этого оператора нет никакого предиката, сканирование может быть выполнено оптимально, т.е. считаны не все строки таблицы, а только требуемые. Так как оптимизатор при выполнении сканирования таблицы учитывает также и другие операторы и условия текущего плана запроса.
Например, План запроса, состоящий из двух операторов: Сканирование таблицы (1000 записей) -> Топ (5). По этому плану будет прочитано из таблицы 5 записей, хотя в таблице 1000 записей и выполнялось ее сканирование без предиката.
3. Хотя при реализации в конфигурациях 1С механизмов RLS на выходе в СУБД получается очень кривой запрос с коррелированными подзапросами оптимизатор СУБД выполняет такие запросы довольно оптимально – лишние записи практически не читаются.
…
(текст комментария доступен только участникам Мастер-группы)
настроил Скрипт для дефрагментации и реорганизации индекса.txt как регламентное задание. перед выполнением посмотрел состояние индексов. выполнил. контрольный просмотр – и ничего не изменилось.
чядн?
…
(текст комментария доступен только участникам Мастер-группы)
В “видео 32. В индексе не должно быть зазоров. Схема решения”. Вы говорите, что можно установить свойство индексировать для поля склад, что избавиться от seek where. Но как это нам поможет? Ведь появится составной индекс период+склад. А выходные поля Товар и остаток. Т.е. индекс не покрывающий, а значит будет опять использован оптимальный кластерный индекс в котором есть разрыв, т.е. опять seek where. И по плану запроса у меня так и получается при этом индексировании…
…
(текст комментария доступен только участникам Мастер-группы)
Добрый день!
На одной из рабочих баз столкнулся с ростом объема индексов и как результат рост в десятки раз объема базы (в файловом варианте размер файла базы данных на прежнем уровне), по регистрам с нулевым количеством строк даже перестройка индекса не приводит к уменьшению размера индексов.
Вот пример:
До очистки Данные Индекс Число строк
_InfoRg3292 110944 7450688 689002
_InfoRg5140 75384 2923776 1130601
После очистки регистров и перестройки индексов
Данные Индекс Число строк
_InfoRg3292 7408 7450720 3274
_InfoRg5140 49984 2923768 0
После сжатия базы
_InfoRg3292 680 7450720 3274
_InfoRg5140 0 2923768 0
Пример для регистра адресного классификатора и регистра Объекты доступа документов. Подобная ситуация и на регистрах расчета и накопления.
Что еще следует проверить?
Как долго ждать проверки?
…
(текст комментария доступен только участникам Мастер-группы)
…
(текст комментария доступен только участникам Мастер-группы)
Числа это объем в килобайтах занятый данными, занятый индексами и количество строк в таблице.Такой результат как раз получился после загрузки из dt. Копии на данном сервере восстанавливали в разных режимах неоднократно, результат один и тот-же. Принудительное перестроение индексов ни к чему не приводит.
Самое непонятное, что на другом сервере с аналогичным ПО и настройками база восстанавливается в нормальном размере из того же файла.
…
(текст комментария доступен только участникам Мастер-группы)
Андрей, иногда встречается рекомендация включать событие SQL:BatchStarted. Что это дает?
…
(текст комментария доступен только участникам Мастер-группы)
В купсе говорится, что мы никак не можем повлиять на выбор оптимизатором плана запроса. А если все же как-то использовать “use plan” для предварительно созданного плана?
…
(текст комментария доступен только участникам Мастер-группы)
Что означает логический оператор “Статическое выражение потока”?
…
(текст комментария доступен только участникам Мастер-группы)
В динамических списках есть Lookup. Насколько это критично и как можно обойти?
…
(текст комментария доступен только участникам Мастер-группы)
Индекс нужен для поля, участвующего в выборке или для поля, по которому идет условие?
…
(текст комментария доступен только участникам Мастер-группы)
В списке выходных столбцов к каждому полю добавлено окончание либо TRef, либо RRef. Например, Товар – _Fld20RRef. Что это означает и почему такое появилось?
…
(текст комментария доступен только участникам Мастер-группы)
Можно ли как-то проверить производительность СКД (системы компоновки данных) в профайлере? Притом, именно не вытаскивая запрос в отдельную обработку?
…
(текст комментария доступен только участникам Мастер-группы)
Бывают ли индексы по ресурсам?
…
(текст комментария доступен только участникам Мастер-группы)